Slow query execution in large partition

Question

I have a DB schema that is partionned on time stamp field, each partition includes 155 time stamps unique vlaues and it is 1.5 GB in size. the schema is very simple and includes time stamp, object id and additional fields (no foreign keys no joins). the primary key is the time stamp and object id field.

now the following query takes ~50 seconds to execute

SELECT c_aggregated_data_10_minutes */ 
    from_time,
    object_id,
    object_type,
    latencies_ttlbsec_sum,
    usage_hits_total
FROM
    metric_store.lc_aggregated_data_master_10_minutes
WHERE
    object_id in ( list of ~100 ids) AND 
    from_time >= 1351602600 AND 
    from_time <  1351688400

the time span in the condition covers 144 time points

the execution plan is as follows:

"Result  (cost=0.00..279041.19 rows=68274 width=24)"
"  ->  Append  (cost=0.00..279041.19 rows=68274 width=24)"
"        ->  Seq Scan on lc_aggregated_data_master_10_minutes  (cost=0.00..0.00 rows=1 width=24)"
"              Filter: ((from_time >= 1351602600) AND (from_time < 1351688400) AND (object_id = ANY ('{258453,260435,259490,262254,261341,445607,263218,447674,446803,448540,9532,2071,5232,2429532,246502,3939,244000,241179,236971,254544,252928,250982,248878,257377,5893,256092,5707,2986,733,7765,3836,7850,2885,100,9744,4435,10492,2441779,573255,8105,993,6004,5052,7581,15,10171,7363,10381,822,4340,5616,2673,2174,10696,7028,10066,8845,10595,2499,3184,6325,2280,10278,519,8020,1504,3081,7935,3741,4235,3535,5428,6218,7472,567771,568316,568862,569411,8954,570517,569972,571619,571062,572710,572165,9862,1710,1875,6541,2397,205,4756,2435059,4859,562859,563404,426,562308,6434,8738,4038,567226,566681,7260,566130,565584,8628,565039,564494,2492165,563949,1286,8307,5141,9308,1080,6824,6640,9961,518277,519721,556424,178509,555067,160902,559587,558254,522427,520857,524956,523659,229743,3379,222533,215285,208058,200756,193533,186251,5327,630,505950,7680,3632,2491614,517196,509766,510971,507374,508381,1593,4965,514786,9425,515944,512018,513537,1974,1377,9128,4129,5529,503659,504806,471537,495721,1201,496761,497870,499285,500262,3284,501341,502624,309,6733,4639,6915,470231,467992,469134,465660,466675,463127,8196,464183,6107,461061,462081,2790,459792,9043,455646,456791,457747,458721,451617,452556,453738,454718,9213,9643,8414,449680,450608}'::integer[])))"
"        ->  Bitmap Heap Scan on lc_aggregated_data_10_minutes_from_1351510800 lc_aggregated_data_master_10_minutes  (cost=1444.26..174220.14 rows=42626 width=24)"
"              Recheck Cond: ((from_time >= 1351602600) AND (from_time < 1351688400))"
"              Filter: (object_id = ANY ('{258453,260435,259490,262254,261341,445607,263218,447674,446803,448540,9532,2071,5232,2429532,246502,3939,244000,241179,236971,254544,252928,250982,248878,257377,5893,256092,5707,2986,733,7765,3836,7850,2885,100,9744,4435,10492,2441779,573255,8105,993,6004,5052,7581,15,10171,7363,10381,822,4340,5616,2673,2174,10696,7028,10066,8845,10595,2499,3184,6325,2280,10278,519,8020,1504,3081,7935,3741,4235,3535,5428,6218,7472,567771,568316,568862,569411,8954,570517,569972,571619,571062,572710,572165,9862,1710,1875,6541,2397,205,4756,2435059,4859,562859,563404,426,562308,6434,8738,4038,567226,566681,7260,566130,565584,8628,565039,564494,2492165,563949,1286,8307,5141,9308,1080,6824,6640,9961,518277,519721,556424,178509,555067,160902,559587,558254,522427,520857,524956,523659,229743,3379,222533,215285,208058,200756,193533,186251,5327,630,505950,7680,3632,2491614,517196,509766,510971,507374,508381,1593,4965,514786,9425,515944,512018,513537,1974,1377,9128,4129,5529,503659,504806,471537,495721,1201,496761,497870,499285,500262,3284,501341,502624,309,6733,4639,6915,470231,467992,469134,465660,466675,463127,8196,464183,6107,461061,462081,2790,459792,9043,455646,456791,457747,458721,451617,452556,453738,454718,9213,9643,8414,449680,450608}'::integer[]))"
"              ->  Bitmap Index Scan on lc_aggregated_data_10_minutes_from_1351510800_pkey  (cost=0.00..1433.60 rows=66382 width=0)"
"                    Index Cond: ((from_time >= 1351602600) AND (from_time < 1351688400))"
"        ->  Bitmap Heap Scan on lc_aggregated_data_10_minutes_from_1351630800 lc_aggregated_data_master_10_minutes  (cost=866.98..104821.05 rows=25647 width=24)"
"              Recheck Cond: ((from_time >= 1351602600) AND (from_time < 1351688400))"
"              Filter: (object_id = ANY ('{258453,260435,259490,262254,261341,445607,263218,447674,446803,448540,9532,2071,5232,2429532,246502,3939,244000,241179,236971,254544,252928,250982,248878,257377,5893,256092,5707,2986,733,7765,3836,7850,2885,100,9744,4435,10492,2441779,573255,8105,993,6004,5052,7581,15,10171,7363,10381,822,4340,5616,2673,2174,10696,7028,10066,8845,10595,2499,3184,6325,2280,10278,519,8020,1504,3081,7935,3741,4235,3535,5428,6218,7472,567771,568316,568862,569411,8954,570517,569972,571619,571062,572710,572165,9862,1710,1875,6541,2397,205,4756,2435059,4859,562859,563404,426,562308,6434,8738,4038,567226,566681,7260,566130,565584,8628,565039,564494,2492165,563949,1286,8307,5141,9308,1080,6824,6640,9961,518277,519721,556424,178509,555067,160902,559587,558254,522427,520857,524956,523659,229743,3379,222533,215285,208058,200756,193533,186251,5327,630,505950,7680,3632,2491614,517196,509766,510971,507374,508381,1593,4965,514786,9425,515944,512018,513537,1974,1377,9128,4129,5529,503659,504806,471537,495721,1201,496761,497870,499285,500262,3284,501341,502624,309,6733,4639,6915,470231,467992,469134,465660,466675,463127,8196,464183,6107,461061,462081,2790,459792,9043,455646,456791,457747,458721,451617,452556,453738,454718,9213,9643,8414,449680,450608}'::integer[]))"
"              ->  Bitmap Index Scan on lc_aggregated_data_10_minutes_from_1351630800_pkey  (cost=0.00..860.56 rows=39940 width=0)"
"                    Index Cond: ((from_time >= 1351602600) AND (from_time < 1351688400))"

how can I speed the execution of this query (get it executed in less than 10 seconds)

Answer 1

Create an index on the id or make the primary key with the id first (id, ts) in instead of (ts, id). BTW the time stamp field is a unix timestamp not to be confounded with postgresql's timestamp data type.