Re: Why performance improvement on converting subselect

Tom Lane wrote:

>Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> 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
>
>