From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marcin Krupowicz <ma(at)rcin(dot)me> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Adding non-selective key to jsonb query @> reduces performance? |
Date: | 2022-06-08 14:32:03 |
Message-ID: | 1623002.1654698723@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Marcin Krupowicz <ma(at)rcin(dot)me> writes:
> However this one, is slow:
> Q2
> select count(*) from tbl where row @> '{"SELECTIVE_COL":
> "SearchValue", "DATE": "20220606", "NON_SELECTIVE_COL": "Abc"}'::jsonb
> It takes 17ms
> Note that the only difference is adding one more - not very unique -
> key. If in Q2 I replaced NON_SELECTIVE_COL with another selective
> column, it's becoming fast again.
This doesn't surprise me a whole lot based on what I know of GIN.
It's going to store sets of TIDs associated with each key or value
mentioned in the data, and then a query will have to AND the sets
of TIDs for keys/values mentioned in the query. That will take
longer when some of those sets are big.
It might be worth experimenting with an index built using the
non-default jsonb_path_ops opclass [1]. I'm not sure if that'd be
faster for this scenario, but it seems worth trying.
regards, tom lane
[1] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2022-06-08 14:32:47 | Re: Strange behavior of limit clause in complex query |
Previous Message | Paulo Silva | 2022-06-08 14:07:11 | Re: Strange behavior of limit clause in complex query |