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
In-reply-to: <18042-27d35e97efb1583f@postgresql.org>
References: <18042-27d35e97efb1583f@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 28 Jul 2023 18:40:43 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2373031.1690571715.1@sss.pgh.pa.us>
Date: Fri, 28 Jul 2023 15:15:15 -0400
Message-ID: <2373032.1690571715@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2373032.1690571715%40sss.pgh.pa.us>
Precedence: bulk

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