RE: Very long query planning times for database with lots ofpartitions

Поиск
Список
Период
Сортировка
От Steven Winfield
Тема RE: Very long query planning times for database with lots ofpartitions
Дата
Msg-id E9FA92C2921F31408041863B74EE4C2001BD30DF1A@CCPMAILDAG03.cantab.local
обсуждение исходный текст
Ответ на Very long query planning times for database with lots of partitions  (Mickael van der Beek <mickael@woorank.com>)
Ответы Re: Very long query planning times for database with lots of partitions  (Mickael van der Beek <mickael@woorank.com>)
Список pgsql-performance

Do you have constraint_exclusion set correctly (i.e. ‘on’ or ‘partition’)?

If so, does the EXPLAIN output mention all of your parent partitions, or are some being successfully pruned?

Planning times can be sped up significantly if the planner can exclude parent partitions, without ever having to examine the constraints of the child (and grandchild) partitions. If this is not the case, take another look at your query and try to figure out why the planner might believe a parent partition cannot be outright disregarded from the query – does the query contain a filter on the parent partitions’ partition key, for example?

 

I believe Timescaledb has its own query planner optimisations for discarding partitions early at planning time.

 

Good luck,

Steve.





This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice

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

Предыдущее
От: Justin Pryzby
Дата:
Сообщение: Re: Very long query planning times for database with lots ofpartitions
Следующее
От: Mickael van der Beek
Дата:
Сообщение: Re: Very long query planning times for database with lots of partitions