Re: PG Query Planner

On Tue, Apr 19, 2022 at 1:57 PM Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com>
wrote:

> Hello,
>
> My query is :
>
> SELECT subs.id AS id1_109,
> scriber_id AS subs_109
> FROM subscription subs
> LEFT OUTER JOIN offer offer1
> ON subs.offer_id = offer1.id
> WHERE offer1.is_external_lifecycle_management = FALSE
> AND subs.job_next_process_time < '2022-04-19 09:25:25.535'
> AND subs.job_in_progress = FALSE
> ORDER BY subs.id ASC LIMIT 1 ;
>

An easy (if you can control the queries) way to force the faster plan is to
prohibit it from using using the index to fulfill the ORDER BY, by changing
it to "ORDER BY subs.id+0 ASC LIMIT 1"

As for getting the planner to get a better plan on its own, I don't think
there is much hope. The difference between 0 rows with
is_external_lifecycle_management=FALSE, and 1 row meeting that, is the
smallest possible difference. Yet still the ratio between them is
infinite. Changing a histogram bin count or adding a multivariate
statistic is not going to change that.

Maybe the executor should be smart enough to cut off the nested loop once
it sees the Materialize will never return a row. But that is not a change
you can make in user-land.

Cheers,

Jeff

>