Re: postgresql 10.1 scanning all partitions instead of 1

Поиск
Список
Период
Сортировка
От Mariel Cherkassky
Тема Re: postgresql 10.1 scanning all partitions instead of 1
Дата
Msg-id CA+t6e1nJJABa1emeNHhk85GymSOM5hqV3GO720wJWUSZgDg9dw@mail.gmail.com
обсуждение исходный текст
Ответ на postgresql 10.1 scanning all partitions instead of 1  (Mariel Cherkassky <mariel.cherkassky@gmail.com>)
Список pgsql-performance
Дерево обсуждения
Sorry it didnt send the whole mail : 
Hi,
I configured range partitions on a date column of my main table(log_full). Each partition represents a day in the month. Every day partition has a list parition of 4 tables on a text column.

log_full
          log_full_01_11_2017  -->
                                          log_full _01_11_2017_x1
                                           log_full _01_11_2017_x2
                                          log_full _01_11_2017_x3
                                          log_full _01_11_2017_x4
            log_full_02_11_2017
                                          log_full _02_11_2017_x1
                                          log_full _02_11_2017_x1
                                          log_full _02_11_2017_x1
                                          log_full _02_11_2017_x1

and so on....
      

The date column consist of date in the next format : YYYY-MM-DD HH:24:SS for example : 2017-11-01 00:01:40

I wanted to check the plan that I'm getting for a query that is using the date column and it seems that the planner choose to do seq scans on all tables.

-Each partition consist from 15M rows.
I have about 120 partitions.

The query : 
explain select count(*) from log_full where end_date between to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');

The output is too long but it do full scans on all paritions...
any idea what can be the problem? Is it connected to the date format ?

Thanks , Mariel.



2018-02-04 11:23 GMT+02:00 Mariel Cherkassky <mariel.cherkassky@gmail.com>:
Hi,
I configured range partitions on a date column of my main table(full_table). Each partition represents a day in the month. Every day partition has a list parition of 4 tables on a text column.

full table 
            table_01_11_2017  -->

            table_02_11_2017
               .....

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

Предыдущее
От: Mariel Cherkassky
Дата:
Сообщение: postgresql 10.1 scanning all partitions instead of 1
Следующее
От: Mariel Cherkassky
Дата:
Сообщение: postgresql 10.1 wrong plan in when using partitions bug