From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
To: PostgreSQL mailing lists <pgsql-performance@postgresql.org>
Subject: Re: postgresql 10.1 wrong plan in when using partitions bug
Date: Sun, 4 Feb 2018 13:03:27 +0100
Message-ID: <f042e9cb-be4b-4215-4165-8a14313cbdf7@2ndquadrant.com> (raw)
In-Reply-To: <CA+t6e1m54jrEj7spf9ArH5BJiO-Qznn=_YCRGWWMtohvXg7mEg@mail.gmail.com>
References: <CA+t6e1m54jrEj7spf9ArH5BJiO-Qznn=_YCRGWWMtohvXg7mEg@mail.gmail.com>
On 02/04/2018 11:14 AM, Mariel Cherkassky wrote:
>
> 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_x2
> log_full _02_11_2017_x3
> log_full _02_11_2017_x4
>
> 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 ?
>
You haven't shown us how the partitions are defined, nor the query plan.
So it's rather hard to say. You mentioned text format, but then you use
to_date() to query the partitioned table. Which I guess might be the
cause, but it's hard to say for sure.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, mariel.cherkassky@gmail.com
Subject: Re: postgresql 10.1 wrong plan in when using partitions bug
In-Reply-To: <f042e9cb-be4b-4215-4165-8a14313cbdf7@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox