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.
Date: Tue, 27 Mar 2018 09:43:15 -0500
Message-ID: <20180327144315.GO28454@telsasoft.com> (raw)
In-Reply-To: <CAM5ipV_iX11SW4EveqauQ=OXcyY5x9_WpM28hWZ-xjyyV=yJbA@mail.gmail.com>
References: <CAM5ipV_iX11SW4EveqauQ=OXcyY5x9_WpM28hWZ-xjyyV=yJbA@mail.gmail.com>
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
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: pryzby@telsasoft.com, glennpierce@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Slow query on partitioned table.
In-Reply-To: <20180327144315.GO28454@telsasoft.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