-1

The table in question has 3.8M records. The data column is indexed on a different field: "idx2_table_on_data_id" btree ((data ->> 'id'::text)). I assumed the sequential scan would be very slow but it is completing in just over 1 second. data->'array' does not exist in many of the records, fyi. Why is this running so quickly? Postgres v10

db=> explain analyze select * from table where jsonb_array_length(data->'array') != 0;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Seq Scan on table (cost=0.00..264605.21 rows=3797785 width=681) (actual time=0.090..1189.997 rows=1762 loops=1)
   Filter: (jsonb_array_length((data -> 'array'::text)) <> 0)
   Rows Removed by Filter: 3818154
 Planning time: 0.561 ms
 Execution time: 1190.492 ms
(5 rows)
verdude
  • 57
  • 5
  • 1
    Not the run-of-the-mill performance question. – Gordon Linoff Dec 03 '20 at 19:26
  • the problem of every single [programmer](https://onsizzle.com/i/it-doesnt-work-why-it-works-why-1644646) – eshirvana Dec 03 '20 at 19:29
  • 1
    [JSON](https://www.postgresql.org/docs/10/datatype-json.html) "...while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed." – Adrian Klaver Dec 03 '20 at 19:34
  • I think if you had proper index it would be a matter of milliseconds and It's relevantly fast because most of the records (99% based on query plan) are being filtered out – eshirvana Dec 03 '20 at 19:34

1 Answers1

0

We could tell for sure if you had run EXPLAIN (ANALYZE, BUFFERS), but odds are that most of the data were cached in RAM.

Also jsonb_array_length(data->'array') is not terribly expensive if the JSON is short.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263