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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
Дата
Msg-id CAMkU=1wBW81FF_egUEJLFpO0KhdAEGSq6eTicqibXXp8GM=4KQ@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where  (PG Bug reporting form <noreply@postgresql.org>)
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
Следующее
От: Kong Man
Дата:
Сообщение: pg_restore 14 skips ACL COLUMN when --schema is used