From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marcin Krupowicz <ma@rcin.me>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Adding non-selective key to jsonb query @> reduces performance?
Date: Wed, 08 Jun 2022 10:32:03 -0400
Message-ID: <1623002.1654698723@sss.pgh.pa.us> (raw)
In-Reply-To: <CAK1Niaz+bHjM6yQ2HTwcTtFrrAJKZkB28nar7C-spygjEyOv3w@mail.gmail.com>
References: <CAK1Niaz+bHjM6yQ2HTwcTtFrrAJKZkB28nar7C-spygjEyOv3w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ma@rcin.me, pgsql-performance@lists.postgresql.org
Subject: Re: Adding non-selective key to jsonb query @> reduces performance?
In-Reply-To: <1623002.1654698723@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox