Re: [BUGS] Very slow query in PostgreSQL 9.3.3

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 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