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

1

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.

edited May 17, 2017 at 14:53

answered May 17, 2017 at 13:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fr1ar Over a year ago

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...

Laurenz Albe Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related