From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: postgresql 10.1 scanning all partitions instead of 1 |
Date: | 2018-02-04 09:28:47 |
Message-ID: | CA+t6e1nJJABa1emeNHhk85GymSOM5hqV3GO720wJWUSZgDg9dw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)cherkassky(at)gmail(dot)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
> .....
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mariel Cherkassky | 2018-02-04 10:14:04 | postgresql 10.1 wrong plan in when using partitions bug |
Previous Message | Mariel Cherkassky | 2018-02-04 09:23:01 | postgresql 10.1 scanning all partitions instead of 1 |