Re: please help with the explain analyze plan - Mailing list pgsql-performance

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.rajesh@gmail.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)
>