Postgresql very slow query on indexed columns

Question

I have very simple query which uses json data for joining on primary table:

WITH timecode_range AS ( SELECT (t->>'table_id')::integer AS table_id, (t->>'timecode_from')::bigint AS timecode_from, (t->>'timecode_to')::bigint AS timecode_to FROM (SELECT '{"table_id":1,"timecode_from":19890328,"timecode_to":119899328}'::jsonb t) rowset ) SELECT n.* FROM partition.json_notification n INNER JOIN timecode_range r ON n.table_id = r.table_id AND n.timecode > r.timecode_from AND n.timecode <= r.timecode_to

It works perfectly when "timecode_range" returns only 1 record:

Nested Loop (cost=0.43..4668.80 rows=1416 width=97) (actual time=0.352..0.352 rows=0 loops=1) CTE timecode_range -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1) -> CTE Scan on timecode_range r (cost=0.00..0.02 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=1) -> Index Scan using json_notification_pkey on json_notification n (cost=0.42..4654.61 rows=1416 width=97) (actual time=0.322..0.322 rows=0 loops=1) Index Cond: ((timecode > r.timecode_from) AND (timecode <= r.timecode_to)) Filter: (r.table_id = table_id) Planning time: 2.292 ms Execution time: 0.665 ms

But when I need to return several records:

WITH timecode_range AS ( SELECT (t->>'table_id')::integer AS table_id, (t->>'timecode_from')::bigint AS timecode_from, (t->>'timecode_to')::bigint AS timecode_to FROM (SELECT json_array_elements('[{"table_id":1,"timecode_from":19890328,"timecode_to":119899328}]') t) rowset ) SELECT n.* FROM partition.json_notification n INNER JOIN timecode_range r ON n.table_id = r.table_id AND n.timecode > r.timecode_from AND n.timecode <= r.timecode_to

It starts using sequential scan and execution time dramatically grows :(

Hash Join (cost=7.01..37289.68 rows=92068 width=97) (actual time=418.563..418.563 rows=0 loops=1) Hash Cond: (n.table_id = r.table_id) Join Filter: ((n.timecode > r.timecode_from) AND (n.timecode <= r.timecode_to)) Rows Removed by Join Filter: 14444 CTE timecode_range -> Subquery Scan on rowset (cost=0.00..3.76 rows=100 width=32) (actual time=0.233..0.234 rows=1 loops=1) -> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.218..0.218 rows=1 loops=1) -> Seq Scan on json_notification n (cost=0.00..21703.36 rows=840036 width=97) (actual time=0.205..312.991 rows=840036 loops=1) -> Hash (cost=2.00..2.00 rows=100 width=20) (actual time=0.239..0.239 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> CTE Scan on timecode_range r (cost=0.00..2.00 rows=100 width=20) (actual time=0.235..0.236 rows=1 loops=1) Planning time: 4.729 ms Execution time: 418.937 ms

What am I doing wrong?

Laurenz Albe · Accepted Answer · 2017-05-17 14:53:01Z

PostgreSQL has no possibility to estimate the number of rows returned from a table function, so it uses the ROWS value specified in CREATE FUNCTION (default 1000).

For json_array_elements this value is set to 100:

SELECT prorows FROM pg_proc WHERE proname = 'json_array_elements'; ┌─────────┐ │ prorows │ ├─────────┤ │ 100 │ └─────────┘ (1 row)

But in your case the function returns only 1 row.

This misestimate makes PostgreSQL choose another join strategy (hash join instead of nested loop), which causes the longer execution time.

If you can choose some other construct than such a table function (e.g. a VALUES statement) that PostgreSQL can estimate, you'll get a better plan.

An alternative is to use a LIMIT clause on the CTE definition if you can safely specify an upper limit.

If you think that PostgreSQL is wrong when it switches to a hash join beyond a certain row count, you can test as follows:

Run the query (using a sequential scan and a hash join) and measure the duration (psql's \timing command will help).

Force a nested loop join:

SET enable_hashjoin=off; SET enable_mergejoin=off;

Run the query again (with a nested loop join) and measure the duration.

If PostgreSQL is indeed wrong, you could adjust the optimizer parameters by lowering random_page_cost to a value closer to seq_page_cost.

Thanks for your answer! LIMIT 1 works well but unfortunately LIMIT 24 doesn't work :/ I have no idea how to convert json string to VALUES statement...
LIMIT should do fine. I have expanded the answer to show how you can test if PostgreSQL is right and what you can try if it isn't.

Collectives™ on Stack Overflow

Postgresql very slow query on indexed columns

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related