PostgreSQL 9.3.3 RHEL 6.4
Total db Server memory 64GB
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
max_connections = 100
shared_buffers = 16GB
work_mem = 32MB
maintenance_work_mem = 1GB
seq_page_cost = 1.0
random_page_cost = 2.0
cpu_tuple_cost = 0.03
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025
effective_cache_size = 48MB
default_statistics_target = 100
constraint_exclusion = partition
Partition table Setup
---------------------
CREATE TABLE measurement (
id bigint not null,
city_id bigint not null,
logdate date not null,
peaktemp bigint,
unitsales bigint,
type bigint,
uuid uuid,
geom geometry
);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);
Partition measurement_y2007m12 contains 38,261,732 rows
Indexes on partition measurement_y2007m12:
"pkey_measurement_y2007m12" PRIMARY KEY, btree (id), tablespace "measurement_y2007"
"idx_measurement_uuid_y2003m12" btree (uuid), tablespace "measurement_y2007"
"idx_measurement_type_y2003m12" btree (type), tablespace "measurement_y2007"
"idx_measurement_city_y2003m12" btree (city_id), tablespace "measurement_y2007"
"idx_measurement_logdate_y2003m12" btree (logdate), tablespace "measurement_y2007"
"sidx_measurement_geom_y2003m12" gist (geom), tablespace "measurement_y2007"
*** Problem Query ***
explain (analyze on, buffers on) Select * from measurement this_
where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp
and this_.city_id=25183 order by this_.logdate asc, this_.peaktemp asc, this_.unitsales asc limit 10000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.803..51714.266 rows=10000 loops=1)
Buffers: shared hit=25614 read=39417
-> Sort (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.799..51712.924 rows=10000 loops=1)
Sort Key: this_.logdate, this_.unitsales
Sort Method: top-N heapsort Memory: 15938kB
Buffers: shared hit=25614 read=39417
-> Append (cost=0.00..33736.09 rows=2068 width=618) (actual time=50.210..50793.589 rows=312046 loops=1)
Buffers: shared hit=25608 read=39417
-> Seq Scan on measurement this_ (cost=0.00..0.00 rows=1 width=840) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((logdate >= '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate <= '2007-12-20 08:01:04.22'::timestamp without time zone) AND (city_id = 25183))
-> Index Scan using idx_measurement_city_y2007m12 on measurement_y2007m12 this__1 (cost=0.56..33736.09 rows=2067 width=618) (actual time=50.206..50731.637 rows=312046 loops=1)
Index Cond: (city_id = 25183)
Filter: ((logdate >= '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate <= '2007-12-20 08:01:04.22'::timestamp without time zone))
Buffers: shared hit=25608 read=39417
Total runtime: 51717.639 ms <--- *** unacceptable ***
(15 rows)
Total Rows meeting query criteria
---------------------------------
Select count(*) from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183;
count
------
312046
Total db Server memory 64GB
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
max_connections = 100
shared_buffers = 16GB
work_mem = 32MB
maintenance_work_mem = 1GB
seq_page_cost = 1.0
random_page_cost = 2.0
cpu_tuple_cost = 0.03
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025
effective_cache_size = 48MB
default_statistics_target = 100
constraint_exclusion = partition
Partition table Setup
---------------------
CREATE TABLE measurement (
id bigint not null,
city_id bigint not null,
logdate date not null,
peaktemp bigint,
unitsales bigint,
type bigint,
uuid uuid,
geom geometry
);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);
Partition measurement_y2007m12 contains 38,261,732 rows
Indexes on partition measurement_y2007m12:
"pkey_measurement_y2007m12" PRIMARY KEY, btree (id), tablespace "measurement_y2007"
"idx_measurement_uuid_y2003m12" btree (uuid), tablespace "measurement_y2007"
"idx_measurement_type_y2003m12" btree (type), tablespace "measurement_y2007"
"idx_measurement_city_y2003m12" btree (city_id), tablespace "measurement_y2007"
"idx_measurement_logdate_y2003m12" btree (logdate), tablespace "measurement_y2007"
"sidx_measurement_geom_y2003m12" gist (geom), tablespace "measurement_y2007"
*** Problem Query ***
explain (analyze on, buffers on) Select * from measurement this_
where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp
and this_.city_id=25183 order by this_.logdate asc, this_.peaktemp asc, this_.unitsales asc limit 10000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.803..51714.266 rows=10000 loops=1)
Buffers: shared hit=25614 read=39417
-> Sort (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.799..51712.924 rows=10000 loops=1)
Sort Key: this_.logdate, this_.unitsales
Sort Method: top-N heapsort Memory: 15938kB
Buffers: shared hit=25614 read=39417
-> Append (cost=0.00..33736.09 rows=2068 width=618) (actual time=50.210..50793.589 rows=312046 loops=1)
Buffers: shared hit=25608 read=39417
-> Seq Scan on measurement this_ (cost=0.00..0.00 rows=1 width=840) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((logdate >= '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate <= '2007-12-20 08:01:04.22'::timestamp without time zone) AND (city_id = 25183))
-> Index Scan using idx_measurement_city_y2007m12 on measurement_y2007m12 this__1 (cost=0.56..33736.09 rows=2067 width=618) (actual time=50.206..50731.637 rows=312046 loops=1)
Index Cond: (city_id = 25183)
Filter: ((logdate >= '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate <= '2007-12-20 08:01:04.22'::timestamp without time zone))
Buffers: shared hit=25608 read=39417
Total runtime: 51717.639 ms <--- *** unacceptable ***
(15 rows)
Total Rows meeting query criteria
---------------------------------
Select count(*) from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183;
count
------
312046
Total Rows in the partition table referenced
------------------------------------------
Select count(*) from measurement_y2007m12;
count
---------
38261732
Does anyone know how to speed up this query? I removed the order by clause and that significantly reduced the run time to approx. 2000-3000 ms. This query is being recorded repeatedly
in our logs and executes very slowly for our UI users from 12000 ms thru 68000 ms
Any suggestions would be appreciated.
thanks
in our logs and executes very slowly for our UI users from 12000 ms thru 68000 ms
Any suggestions would be appreciated.
thanks
