postgres jsonb query performance is decreased by order by

Question

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

• Laurenz Albe: Is the WHERE condition always the same or do the constants vary?
• Joishi Bodio: 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..
• Nikita: @JoishiBodio you're right. perfrormance is the same as in the query with order by.

Answer 1

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

Then try

SELECT *
FROM (SELECT *
 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.

• Nikita: Hi, thanks for your response. I've tried your solution. Query executes 2 times worse than previous solution.
• Laurenz Albe: That's a step forward. What is the execution plan?
• Laurenz Albe: Also, you did not answer my question in the comment to the question: are the constants in the WHERE clause always the same?
• Nikita: Sorry, constants could vary. I've added plan in question
• Laurenz Albe: I tries to come up with a better answer.