pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query on partitioned table.
3+ messages / 2 participants
[nested] [flat]
* Slow query on partitioned table.
@ 2018-03-27 14:14 Glenn Pierce <glennpierce@gmail.com>
2018-03-27 14:43 ` Re: Slow query on partitioned table. Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 3+ messages in thread
From: Glenn Pierce @ 2018-03-27 14:14 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi I am having terrible trouble with a simple partitioned table.
Select queries are very slow.
Ie
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
Takes over five minutes.
Postgres version is PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
shared_buffers = 3000MB
work_mem = 50MB
maintenance_work_mem = 64MB
wal_writer_delay = 10000ms
#effective_cache_size = 4GB I guess this is the default
My amount of memory is 15G
The table gets constant inserts (thousands a minute)
The table has something like 700000000 rows.
So the table is defined as
\d+ sensor_values;
Table "public.sensor_values"
Column | Type | Modifiers
| Storage | Stats target | Description
-----------+--------------------------+--------------------------------------------+---------+--------------+-------------
ts | timestamp with time zone | not null
| plain | |
value | double precision | not null default 'NaN'::real
| plain | |
sensor_id | integer | not null
| plain | |
status | tridium_status | not null default
'unknown'::tridium_status | plain | |
Indexes:
"sensor_values_sensor_id_timestamp_index" UNIQUE, btree (sensor_id, ts)
Foreign-key constraints:
"sensor_values_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES
sensors(id)
Triggers:
a_statistics_trigger BEFORE INSERT OR DELETE ON sensor_values FOR
EACH ROW EXECUTE PROCEDURE stat_info()
sensor_values_trigger_timestamp_sensor_insert_sensor_values BEFORE
INSERT ON sensor_values FOR EACH ROW EXECUTE PROCEDURE
sensor_values_timestamp_sensor_func_insert_trigger()
Child tables: sensor_values_2007q1,
sensor_values_2007q2,
sensor_values_2007q3,
sensor_values_2007q4,
sensor_values_2008q1,
sensor_values_2008q2,
sensor_values_2008q3,
sensor_values_2008q4,
sensor_values_2009q1,
sensor_values_2009q2,
sensor_values_2009q3,
sensor_values_2009q4,
sensor_values_2010q1,
sensor_values_2010q2,
sensor_values_2010q3,
sensor_values_2010q4,
sensor_values_2011q1,
sensor_values_2011q2,
sensor_values_2011q3,
sensor_values_2011q4,
sensor_values_2012q1,
sensor_values_2012q2,
sensor_values_2012q3,
sensor_values_2012q4,
sensor_values_2013q1,
sensor_values_2013q2,
sensor_values_2013q3,
sensor_values_2013q4,
sensor_values_2014q1,
sensor_values_2014q2,
sensor_values_2014q3,
sensor_values_2014q4,
sensor_values_2015q1,
sensor_values_2015q2,
sensor_values_2015q3,
sensor_values_2015q4,
sensor_values_2016q1,
sensor_values_2016q2,
sensor_values_2016q3,
sensor_values_2016q4,
sensor_values_2017q1,
sensor_values_2017q2,
sensor_values_2017q3,
sensor_values_2017q4,
sensor_values_2018q1,
sensor_values_2018q2,
sensor_values_2018q3,
sensor_values_2018q4,
sensor_values_2019q1,
sensor_values_2019q2,
sensor_values_2019q3,
sensor_values_2019q4,
sensor_values_2020q1,
sensor_values_2020q2,
sensor_values_2020q3,
sensor_values_2020q4
The child tables are all like
Column | Type | Modifiers
| Storage | Stats target | Description
-----------+--------------------------+--------------------------------------------+---------+--------------+-------------
ts | timestamp with time zone | not null
| plain | |
value | double precision | not null default 'NaN'::real
| plain | |
sensor_id | integer | not null
| plain | |
status | tridium_status | not null default
'unknown'::tridium_status | plain | |
Indexes:
"sensor_values_2018q1_sensor_id_timestamp_index" UNIQUE, btree
(sensor_id, ts)
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)
Inherits: sensor_values
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
[2018-03-27 14:45:39] 260 rows retrieved starting from 1 in 13m 13s
221ms (execution: 13m 13s 141ms, fetching: 80ms)
Shows the following output
https://explain.depesz.com/s/c8HU
Any idea why this query takes so long ?
Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow query on partitioned table.
2018-03-27 14:14 Slow query on partitioned table. Glenn Pierce <glennpierce@gmail.com>
@ 2018-03-27 14:43 ` Justin Pryzby <pryzby@telsasoft.com>
2018-03-27 21:00 ` Re: Slow query on partitioned table. Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 3+ messages in thread
From: Justin Pryzby @ 2018-03-27 14:43 UTC (permalink / raw)
To: Glenn Pierce <glennpierce@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow query on partitioned table.
2018-03-27 14:14 Slow query on partitioned table. Glenn Pierce <glennpierce@gmail.com>
2018-03-27 14:43 ` Re: Slow query on partitioned table. Justin Pryzby <pryzby@telsasoft.com>
@ 2018-03-27 21:00 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 3+ messages in thread
From: Justin Pryzby @ 2018-03-27 21:00 UTC (permalink / raw)
To: Glenn Pierce <glennpierce@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Re-added -performance.
On Tue, Mar 27, 2018 at 05:13:25PM +0100, Glenn Pierce wrote:
> Damn as I was playing with the indexes I must have deleted the constraints :(
> Question if I have a constraint like
>
> ALTER TABLE sensor_values_2007q1
> ADD CONSTRAINT sensor_values_2007q1_sensor_id_timestamp_constraint
> UNIQUE (sensor_id, ts);
>
> will that be used like an index or do I need to add a separate index ?
Yes:
https://www.postgresql.org/docs/current/static/ddl-constraints.html
|Adding a unique constraint will automatically create a unique B-tree index on
the column or group of columns listed in the constraint
https://www.postgresql.org/docs/current/static/indexes-unique.html
|PostgreSQL automatically creates a unique index when a unique constraint or
|primary key is defined for a table. The index ... is the mechanism that
|enforces the constraint.
Justin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-03-27 21:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-27 14:14 Slow query on partitioned table. Glenn Pierce <glennpierce@gmail.com>
2018-03-27 14:43 ` Justin Pryzby <pryzby@telsasoft.com>
2018-03-27 21:00 ` Justin Pryzby <pryzby@telsasoft.com>
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