Re: partitioning query planner almost always scans all tables
От
Spiros Ioannou
Тема
Re: partitioning query planner almost always scans all tables
Дата
Msg-id
CACKh8C_tMuvjQ9ZepRJ31Ke-fTLBdxqBsSLKbsoO2LvXUq-d_g@mail.gmail.com
Ответ на
Re: partitioning query planner almost always scans all
tables (Kyotaro HORIGUCHI)
Список
Дерево обсуждения
partitioning query planner almost always scans all tables Spiros Ioannou <sivann@inaccess.com>
Re: partitioning query planner almost always scans all tables Rob Sargent <robjsargent@gmail.com>
Re: partitioning query planner almost always scans all
tables Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Re: partitioning query planner almost always scans all tables Spiros Ioannou <sivann@inaccess.com>
Re: partitioning query planner almost always scans all
tables Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Re: partitioning query planner almost always scans all tables Spiros Ioannou <sivann@inaccess.com>
Re: partitioning query planner almost always scans all tables Tom Lane <tgl@sss.pgh.pa.us>
Re: partitioning query planner almost always scans all tables Spiros Ioannou <sivann@inaccess.com>
> EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE
> measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY
> measurement_time DESC LIMIT 1;
>
> This seems to fail, scanning all tables. Do you think this can be improved
> at all ? The query plan of the above query is as follows:
The combination of sorting by measurement_source_id and limit
hinders constraint exclusion because the order of the column
across whole the inheritance is not known to planner. And the
below plan also dosn't show whether constraint exclusion worked
or not, by the same reason. But I suppose it worked.
It is ORDER BY measurement_time, not measurement_id, and measurement_time is used to create the partition. So the planner should know the correct order, but instead it seems to query tables in the wrong order.
В списке pgsql-general по дате отправления
От: Kyotaro HORIGUCHI
Дата:
