Re: please help with the explain analyze plan

hurray!
ANALYZING changed the plan

I was not expecting the plan to change because
the partition of 2006_02 is supposed to be
dormant. maybe the partition was never analyzed.

But still question remains, why the time taken was
in orders of magnitude higher in loaded condition.

tradein_clients=> explain 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)
-> Index Scan using rfis_part_2006_02_generated_date on
rfis_part_2006_02 (cost=0.00..6.43 rows=1 width=0)
Index Cond: (generated_date >= 2251)
Filter: (receiver_uid = 1320721)
(4 rows)
tradein_clients=> ANALYZE rfi_partitions.rfis_part_2006_02;
ANALYZE
tradein_clients=> explain SELECT count(*) from
rfi_partitions.rfis_part_2006_02 where generated_date >= 2251 and
receiver_uid=1320721 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.78..8.79 rows=1 width=0)
-> Index Scan using rfis_part_2006_02_receiver_uid on
rfis_part_2006_02 (cost=0.00..8.77 rows=1 width=0)
Index Cond: (receiver_uid = 1320721)
Filter: (generated_date >= 2251)
(4 rows)

tradein_clients=> explain analyze SELECT count(*) from
rfi_partitions.rfis_part_2006_02 where generated_date >= 2251 and
receiver_uid=1320721 ;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.78..8.79 rows=1 width=0) (actual time=0.045..0.045
rows=1 loops=1)
-> Index Scan using rfis_part_2006_02_receiver_uid on
rfis_part_2006_02 (cost=0.00..8.77 rows=1 width=0) (actual
time=0.042..0.042 rows=0 loops=1)
Index Cond: (receiver_uid = 1320721)
Filter: (generated_date >= 2251)
Total runtime: 0.082 ms
(5 rows)

tradein_clients=>

On Wed, Feb 11, 2009 at 6:07 PM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
> 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)
>