thanks for the hint,
now the peak hour is over and the same scan is taking 71 ms in place of 80000 ms
and the total query time is also acceptable. But it is surprising that
the scan was
taking so long consistently at that point of time. I shall test again
under similar
circumstance tomorrow.
Is it possible to enable block level statistics from the psql prompt
for a particular query
and see the results on the psql prompt ?
explain analyze SELECT count(*) from
rfi_partitions.rfis_part_2006_02 where generated_date >= 2251 and
receiver_uid=1320721 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6.44..6.45 rows=1 width=0) (actual
time=71.513..71.513 rows=1 loops=1)
-> Index Scan using rfis_part_2006_02_generated_date on
rfis_part_2006_02 (cost=0.00..6.43 rows=1 width=0) (actual
time=71.508..71.508 rows=0 loops=1)
Index Cond: (generated_date >= 2251)
Filter: (receiver_uid = 1320721)
Total runtime: 71.553 ms
(5 rows)
