Date: Tue, 27 Mar 2018 09:43:15 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Glenn Pierce <glennpierce@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow query on partitioned table.
Message-ID: <20180327144315.GO28454@telsasoft.com>
References: 
 <CAM5ipV_iX11SW4EveqauQ=OXcyY5x9_WpM28hWZ-xjyyV=yJbA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAM5ipV_iX11SW4EveqauQ=OXcyY5x9_WpM28hWZ-xjyyV=yJbA@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Tue, Mar 27, 2018 at 03:14:30PM +0100, Glenn Pierce wrote:
> Hi I am having terrible trouble with a simple partitioned table.
> Select queries are very slow.
....
> The child tables are all like
> Check constraints:
>     "sensor_values_2018q1_timestamp_check" CHECK (ts >= '2018-01-01
> 00:00:00+00'::timestamp with time zone AND ts < '2018-04-01
> 01:00:00+01'::timestamp with time zone)

> EXPLAIN (ANALYZE, BUFFERS)  SELECT ts::timestamptz, s1.sensor_id, s1.value
>                                   FROM sensor_values s1
>                                    WHERE s1.sensor_id =
> ANY(ARRAY[596304,597992,610978,597998])
>                                          AND s1.ts >= '2000-01-01
> 00:01:01'::timestamptz AND
>                                          s1.ts < '2018-03-20
> 00:01:01'::timestamptz

> Shows the following output
> https://explain.depesz.com/s/c8HU

It's scanning all partitions, so apparently constraint_exclusion isn't working.

Is it because the CHECK has ts with different timezones +00 and +01 ??

Also, it looks funny to use 00:01:01 as the beginning of the day (although I
think it's true that an HR department would understand that better..).

Justin