Re: Query time related to limit clause

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Query time related to limit clause
Дата
Msg-id CAHOFxGomPvBPJwZVouuUrA8EjpMXepDvKjDMnNpXunw0xMuJJA@mail.gmail.com
обсуждение исходный текст
Ответ на Query time related to limit clause  (Shubham Mittal <mittalshubham30@gmail.com>)
Список pgsql-general
What's the definition for abc_serv_nch_q1_2021_expr_idx3? That is a jsonb field I assume? Statistics aren't great on jsonb data, so you may benefit greatly from pulling keys out to be stored as a standard column. I would be curious for more "pure" estimates on each quarterly partition directly for only the condition below (explain analyze, just looking at estimated vs actual row counts) since they seem to be rather severe overestimates but I'm not sure how much the date condition is obscuring that.

(((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text)"

The planner is choosing to use ONLY the index on the created timestamp when limit is 1 and finding they nearly all match (I hope all on the q2 partition) and needs to filter almost all of those out (all from q1 partition I think, and nearly all from the others). I believe that the planner thinks the other criteria in the query are not nearly as selective as they are, and so it thinks it will find 1 match very quickly and be done. That isn't the case.

When you want more rows, the planner decides that using both indexes is less costly and it is correct.

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

Предыдущее
От: Ron
Дата:
Сообщение: Re: type bug?
Следующее
От: ml@ft-c.de
Дата:
Сообщение: Re: type bug?