From: Tom Lane <tgl@sss.pgh.pa.us>
To: christian.vallieres@evimbec.ca
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
Date: Fri, 28 Jul 2023 15:15:15 -0400
Message-ID: <2373032.1690571715@sss.pgh.pa.us> (raw)
In-Reply-To: <18042-27d35e97efb1583f@postgresql.org>
References: <18042-27d35e97efb1583f@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> Query planner favor starting with the event table and use the 102MB
> event_date_idx corresponding to a order by with a limit,
> instead of the 94MB event_event_type_id_idx corresponding to the where
> condition.
Sadly, this isn't something we can do much about. Estimating the
behavior of a query with ORDER BY and a small LIMIT is simply very
tricky: a plan that depends on an indexscan stopping early might
be very fast, or it might not be, depending on factors such as row
physical locations that the planner doesn't have a lot of info about.
Your best bet might be to prevent event_date_idx from matching the
query's sort order. Seeing that date is NOT NULL, I'd suggest
changing either the index or the query (not both!) to specify NULLS
FIRST. This'll make no actual difference given the lack of nulls, but
the planner isn't cognizant of that and will decide it can't use the
index in this way for this query.
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, christian.vallieres@evimbec.ca, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
In-Reply-To: <2373032.1690571715@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