4

I have an issue where the execution is not using a GIN index when going over 18 OR statements.

This is my index:

CREATE INDEX emailjobs_externalreferences_gin
ON vsko_mailer_api_prod.emailjobs USING gin
("externalReferences" jsonb_path_ops)
TABLESPACE pg_default;

this is the query:

explain analyze
select * from "emailjobs"
where "emailjobs"."$$meta.deleted" = false
  and ("externalReferences" @> '[{"href":"/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)
order by "$$meta.created" asc,"key" asc
limit '500'

And the result:

"Limit  (cost=0.08..8857.81 rows=500 width=1380) (actual time=766.232..1182.058 rows=49 loops=1)"
"  ->  Index Scan using emailjobs_ordered_created_and_keys on emailjobs  (cost=0.08..70064.73 rows=3955 width=1380) (actual time=766.230..1182.049 rows=49 loops=1)"
"        Filter: ((NOT "$$meta.deleted") AND (("externalReferences" @> '[{"href": "/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)))"
"        Rows Removed by Filter: 216447"
"Planning time: 0.237 ms"
"Execution time: 1182.107 ms"

So it's not using the GIN index.

however, I've looked for the turning point. If i have 18 or less OR statements, it seems to use the GIN just fine.

explain analyze
select * from "emailjobs"
where "emailjobs"."$$meta.deleted" = false
  and ("externalReferences" @> '[{"href":"/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)
order by "$$meta.created" asc,"key" asc
limit '500'

result:

"Limit  (cost=8938.34..8938.59 rows=500 width=1380) (actual time=23.443..23.455 rows=49 loops=1)"
"  ->  Sort  (cost=8938.34..8940.21 rows=3749 width=1380) (actual time=23.442..23.448 rows=49 loops=1)"
"        Sort Key: "$$meta.created", key"
"        Sort Method: quicksort  Memory: 123kB"
"        ->  Bitmap Heap Scan on emailjobs  (cost=2673.05..8900.97 rows=3749 width=1380) (actual time=23.000..23.326 rows=49 loops=1)"
"              Recheck Cond: (("externalReferences" @> '[{"href": "/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb))"
"              Filter: (NOT "$$meta.deleted")"
"              Heap Blocks: exact=62"
"              ->  BitmapOr  (cost=2673.05..2673.05 rows=3781 width=0) (actual time=22.922..22.922 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.283..1.283 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.281..1.281 rows=2 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.358..1.358 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.196..1.196 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.249..1.249 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.312..1.312 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.291..1.291 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.247..1.247 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.284..1.284 rows=3 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.261..1.261 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.268..1.268 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.296..1.296 rows=3 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.223..1.223 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.273..1.273 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.287..1.287 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.240..1.240 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.255..1.255 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.310..1.310 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)"
"Planning time: 0.249 ms"
"Execution time: 23.623 ms"

Any idea how I can optimize this query, allowing way more OR statements (at least like 50?) and still using the/an index?

here is an example of the externalReferences data:

"[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"},{"href":"/sam/organisationalunits/4fc33e81-6a08-4be8-82b9-8822f77787d5"}]"

edit:

externalReferences could be an empty array too.

  • 1
    Is the query really faster with an index scan? Test with enable_seqscan = off. – Laurenz Albe Sep 2 at 7:05
  • Im not a postgres or json expert so i apologise if im missing something. But that query looks like a maintenance nightmare. Instead of using so many ORs, could you insert the clauses into a table and just do a join or where extref in select distinct yourjsonvalue from jsontable? – Sir Swears-a-lot Sep 3 at 9:46
  • @SirSwears-a-lot not sure if this would be good for my use-case. the list of items is different every time. sometimes only 1 item, sometimes 100 – Stefanvds Sep 3 at 12:40
4

The problem is that PostgreSQL assumes that more rows will be returned when you specify more OR conditions, so at some point it will think that an index scan won't be faster any more.

You could create a function that extracts the interesting parts of the JSON:

CREATE FUNCTION get_array(jsonb) RETURNS text[]
   LANGUAGE sql IMMUTABLE AS
$$SELECT array_agg(x->>'href') FROM jsonb_array_elements($1) AS v(x)$$;

Then you can index

CREATE INDEX ON vsko_mailer_api_prod.emailjobs USING gin (get_array("externalReferences"));

and query like this:

... WHERE get_array("externalReferences")
          && ARRAY['/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852',
                   '/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80',
                   ...
                  ]

The “overlaps” operator should do what you want.

  • thanks. The only issue i'm having now is cannot extract elements from a scalar since some "externalReferences" value is [] – Stefanvds Sep 2 at 8:15
  • this is the function i used in the end that fixed it: $$SELECT array_agg(x->>'href') FROM jsonb_array_elements(case when $1::text = 'null' then '[]' else $1 end ) AS v(x) $$; – Stefanvds Sep 2 at 10:16
  • Ah, I see: the value was null and not [null] as you wrote. – Laurenz Albe Sep 2 at 10:22
  • i'm still not sure what kind of null it was, because is null didn't return those records either – Stefanvds Sep 2 at 11:27
  • That's a JSON null. – Laurenz Albe Sep 2 at 12:20
2

As you add more OR conditions, it thinks it will return more rows. Since there is an ORDER BY and a LIMIT, at some point it thinks that walking a different index, which provides the same ordering as the ORDER BY and stopping early once it hits the LIMIT, will be faster than getting all rows which meet the WHERE clause, and then sorting them into order and applying the LIMIT.

The easiest way to fix this is to skunk the index being used to ORDER BY ("emailjobs_ordered_created_and_keys"). If "key" is numeric, then you can do:

order by "$$meta.created" asc,"key"+0 asc

Or if it textual, then

order by "$$meta.created" asc,"key"||'' asc

This will not change the actual order, but will prevent PostgreSQL from recognizing that it can use the index to supply that order.

You can also use CTE as an optimization fence which forces the full select to be run before ordering and applying the limit:

explain analyze
with t as (
select * from "emailjobs"
where "emailjobs"."$$meta.deleted" = false
  and ("externalReferences" @> '[{"href":"/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb
   OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb
    )
)
select * from t
order by "$$meta.created" asc,"key" asc
limit '500'

Starting in v12, this is no longer an optimization fence unless you write it as with t as materialized

Why is PostgreSQL getting it wrong in the first place? JSON objects have no statistics gathered on them, so PostgreSQL has to use generic estimates about how many rows will match the @> operators. You can see it is over-estimating that by about a factor of 40. This is a double-whammy, as it thinks it will have 40 times more rows to sort if it follow the sorting route, and thinks it will have 40 times less of the already-ordered index to scan (before the LIMIT kicks in) if it takes that route. One way to potentially fix that is by mapping the JSONB into regular PostgreSQL arrays, as those do have statistics gathered on them. You can either store them in the table that way, or you can create a functional index as outlined by Laurenz. However, in my hands this did even worse for row estimates than the JSONB did: the most_common_elems column ended up being NULL, for reasons already explored.

So I would go with the quick and dirty hack first described, at least until some future version of PostgreSQL offers better solutions.

  • thanks for your answer. I have implemented Laurenz's answer and it's also a lot faster than my original solution in case of 18 or less items (by a factor of 10 or more) – Stefanvds Sep 2 at 13:34
  • @Stefanvds did it switch plans to use the index, or was it just faster due to CPU efficiency? I found his overlap query about 3 times faster due to CPU efficiency, but it didn't switch plans to use the functional GIN index. But your real data is surely different than my simulated data, so perhaps you don't have the "most_common_elems is NULL" problem that I do. – jjanes Sep 2 at 13:44
  • 1
    it is indeed using the Bitmap Index Scan on emailjobs_get_array_idx which is about 10x faster than the select in a select. I believe both can be great depending on the data and situation – Stefanvds Sep 2 at 14:00
  • 1
    Awesome, thanks for reporting back. I also get his to use the index as long as one of the elements is common enough so that the stats don't end up NULL. – jjanes Sep 2 at 14:14

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.