From: Tom Lane <tgl@sss.pgh.pa.us>
To: michael@joincandidhealth.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18834: Query planer is choosing the sub-optimal plan when limit is present
Date: Fri, 07 Mar 2025 18:36:39 -0500
Message-ID: <1144176.1741390599@sss.pgh.pa.us> (raw)
In-Reply-To: <18834-ec88fdda949458d0@postgresql.org>
References: <18834-ec88fdda949458d0@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> We observed **poor query performance** when using an **`ORDER BY`** clause
> combined with **`LIMIT`**, which led us to investigate further.
This is a well-known syndrome. The fundamental problem is the poor
selectivity estimate:
> -> Bitmap Index Scan on ix_fn_ledger_provenance
> (cost=0.00..1228.15 rows=8020 width=0) (actual time=2.923..2.924 rows=1
> loops=1)
> Index Cond: (provenance @> '{"locator":
> "52ca2f03-8184-448f-9b9e-7a0ed99e6922"}'::jsonb)
If the thing thinks there are 8020 matches for the @> condition when
there's only one, it's really pure luck if you get an optimal plan.
All its cost estimates will be off by a factor of more than 1000,
and these decisions are by no means linear. They're particularly
not linear in the presence of LIMIT, but query plans can go far
astray even without that.
So what you need to do about this is get a better selectivity
estimate. You might be able to get somewhere with custom statistics
(see CREATE STATISTICS), or just by cranking up the statistics target
for the "provenance" column to the maximum. But I'm not sure how
much that will help. Fundamentally, putting stuff into unstructured
JSON storage and expecting to get efficient searches of it is an
antipattern. A more reliable answer is to change your query.
You could, for example, make an expression index on
(provenance ->> 'locator') and then write
WHERE (provenance ->> 'locator') = '52ca2f03-8184-448f-9b9e-7a0ed99e6922'
That will provide both fast searches and reasonably trustworthy stats
(once ANALYZE has seen the index).
regards, tom lane
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, michael@joincandidhealth.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18834: Query planer is choosing the sub-optimal plan when limit is present
In-Reply-To: <1144176.1741390599@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