[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: Adding non-selective key to jsonb query @> reduces performance?
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2022-06-08 14:32:03
Message-ID: 1623002.1654698723 () sss ! pgh ! pa ! us
[Download RAW message or body]

Marcin Krupowicz <ma@rcin.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


[prev in list] [next in list] [prev in thread] [next in thread]