Re: [PERFORM] Why performance improvement on converting subselect

[Rajesh Kumar Mallah]

Tom Lane wrote:

Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
 

What lead to degradation was the bumping off of
effective_cache_size parameter from 1000 to 64K
   

Check the plan then; AFAIR the only possible effect of changing
effective_cache_size is to influence which plan the planner picks.
Dear Tom,

Below are the plans for two cases. I dont know how to read them accurately
can u please explain them. Also can anyone point to some documentation
oriented towards understanding explain analyze output?
Regds
Mallah.
tradein_clients=# SET effective_cache_size = 1000;
SET
tradein_clients=# explain analyze SELECT  
pri_key,most_recent_edition(pri_key) from profiles where 
source='BRANDING' limit 100;
                                                    QUERY 
PLAN                                                    
--------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..25.67 rows=100 width=4) (actual time=141.11..154.71 
rows=100 loops=1)
  ->  Seq Scan on profiles  (cost=0.00..15754.83 rows=61385 width=4) 
(actual time=141.11..154.51 rows=101 loops=1)
        Filter: (source = 'BRANDING'::character varying)
Total runtime: 154.84 msec
(4 rows)

tradein_clients=# SET effective_cache_size = 64000;
SET
tradein_clients=# explain analyze SELECT  
pri_key,most_recent_edition(pri_key) from profiles where 
source='BRANDING' limit 100;
                                                     QUERY 
PLAN                                                     
----------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..25.67 rows=100 width=4) (actual 
time=587.61..22884.75 rows=100 loops=1)
  ->  Seq Scan on profiles  (cost=0.00..15754.83 rows=61385 width=4) 
(actual time=587.60..22884.25 rows=101 loops=1)
        Filter: (source = 'BRANDING'::character varying)
Total runtime: 22884.97 msec
(4 rows)

tradein_clients=#



			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html
 



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings