Very slow query in PostgreSQL 9.3.3

From: <fburgess(at)radiantblue(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Very slow query in PostgreSQL 9.3.3
Date: 2014-03-13 19:26:54
Message-ID: 20140313122654.5a830134ae84016b0174832fdc1a3173.52b2345c76.wbe@email11.secureserver.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general pgsql-performance

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

Attachment Content-Type Size
unknown_filename text/html 8.8 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2014-03-13 19:36:15 Re: [BUGS] Very slow query in PostgreSQL 9.3.3
Previous Message Tom Lane 2014-03-13 19:19:11 Re: BUG #9551: Hang in State "authentication" Prevents Vacuum from Freeing Dead Rows

Browse pgsql-general by date

  From Date Subject
Next Message john gale 2014-03-13 19:32:37 Re: puzzling perl DBI vs psql problem
Previous Message Tom Lane 2014-03-13 19:25:39 Re: puzzling perl DBI vs psql problem

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2014-03-13 19:36:15 Re: [BUGS] Very slow query in PostgreSQL 9.3.3
Previous Message Evgeny Shishkin 2014-03-11 23:12:13 Re: Query taking long time