Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where

You report this as a bug against 15.3, did it used to perform better in a
previous version or is this the only version you tried it in?

Your plans are unreadable.  We are people here, not machines; you should
use FORMAT text, not FORMAT json, and post them in a way that preserves
newline and indentations.  Email is bad at that, so maybe share as links to
https://explain.depesz.com/ in addition to or instead of embedded text.
And this doesn't seem to be a bug, just an infamous performance problem, so
maybe use the pgsql-performance list instead.

You could likely get a big improvement by having an index on
(event_type_id, date).  It would still be using the wrong (suboptimal)
index, but it would be a lot less wrong, as it could rule out the
disqualified event_type_id without needing to inspect each one.  It is also
likely to be "good enough" over a wide range of parameters, unlike Tom's
suggestion which would probably be very good for this particular
parameterization, but might not be good for a different one with a much
more popular city_id.

Cheers,

Jeff