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