Re: Slow query on partitioned table.

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