I have a table with jsonb column. When I execute query below it evaluates quite fast. But then I need to add order by clause (created column is timestamp column). Execution time decreases by 1000 times.

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' limit 100;
Limit  (cost=0.00..4198.36 rows=100 width=137) (actual time=0.100..2.351 rows=100 loops=1)
  ->  Seq Scan on delivery  (cost=0.00..6843.32 rows=163 width=137) (actual time=0.099..2.338 rows=100 loops=1)
        Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
        Rows Removed by Filter: 1
Planning time: 0.184 ms
Execution time: 2.384 ms
select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' order by created limit 100;
Limit  (cost=6423.11..6434.78 rows=100 width=137) (actual time=2243.874..2270.206 rows=100 loops=1)
  ->  Gather Merge  (cost=6423.11..6438.98 rows=136 width=137) (actual time=2243.872..2270.187 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=5423.09..5423.26 rows=68 width=137) (actual time=2237.522..2237.617 rows=251 loops=3)
              Sort Key: created
              Sort Method: external sort  Disk: 8552kB
              ->  Parallel Seq Scan on delivery  (cost=0.00..5421.02 rows=68 width=137) (actual time=0.160..2152.447 rows=48670 loops=3)
                    Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
                    Rows Removed by Filter: 5518
Planning time: 0.556 ms
Execution time: 2272.830 ms

I tried to create index on created field - nothing happened. How can I improve performance of this query?

UPD I've tried to use indexes (btree on created and gin on links) as @LaurenzAlbe adviced.

Limit  (cost=601.36..601.61 rows=100 width=139) (actual time=4833.531..4833.555 rows=100 loops=1)
  ->  Sort  (cost=601.36..601.76 rows=163 width=139) (actual time=4833.529..4833.544 rows=100 loops=1)
        Sort Key: created
        Sort Method: top-N heapsort  Memory: 74kB
        ->  Bitmap Heap Scan on delivery  (cost=41.26..595.37 rows=163 width=139) (actual time=29.329..4801.109 rows=146010 loops=1)
              Recheck Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
              Heap Blocks: exact=4128
              ->  Bitmap Index Scan on delivery_expr_idx  (cost=0.00..41.22 rows=163 width=0) (actual time=28.531..28.531 rows=146010 loops=1)
                    Index Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
Planning time: 1.144 ms
Execution time: 4833.618 ms
  • Is the WHERE condition always the same or do the constants vary? – Laurenz Albe Oct 23 '19 at 16:19
  • Remove the limit 100 and see if it's still 'quite performant' between the two. Unordered it only takes the time required to generate 100 records, but ordered it has to generate all records regardless, and then give you the first 100.. – Joishi Bodio Oct 23 '19 at 16:20
  • @JoishiBodio you're right. perfrormance is the same as in the query with order by. – Nikita Oct 24 '19 at 7:29

Remove the GIN index delivery_expr_idx you created and make sure that created still has an index.

Then try

      FROM delivery
      ORDER BY created
      OFFSET 0 LIMIT 10000) AS q
WHERE document @>
      '{"links": [{"type": "receipt","externalId": "9990863500764120180212"}]}'
ORDER BY created
LIMIT 100;

I'm cheating with the LIMIT 10000, but since almost all rows satisfy the WHERE condition, I hope to get away with it.

| improve this answer | |
  • Hi, thanks for your response. I've tried your solution. Query executes 2 times worse than previous solution. – Nikita Oct 24 '19 at 7:33
  • That's a step forward. What is the execution plan? – Laurenz Albe Oct 24 '19 at 8:32
  • Also, you did not answer my question in the comment to the question: are the constants in the WHERE clause always the same? – Laurenz Albe Oct 24 '19 at 8:34
  • Sorry, constants could vary. I've added plan in question – Nikita Oct 24 '19 at 9:36
  • I tries to come up with a better answer. – Laurenz Albe Oct 24 '19 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.