From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why performance improvement on converting subselect
Date: Thu, 31 Jul 2003 01:08:31 +0530
Message-ID: <3F281EB7.7010308@trade-india.com> (raw)
In-Reply-To: <9198.1059573083@sss.pgh.pa.us>
References: <200307291114.29567.mallah@trade-india.com>
<3F26A503.3050104@trade-india.com>
<4578.1059514334@sss.pgh.pa.us>
<200307301254.49159.mallah@trade-india.com>
<9198.1059573083@sss.pgh.pa.us>
Tom Lane wrote:
>Rajesh Kumar Mallah <mallah@trade-india.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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mallah@trade-india.com, tgl@sss.pgh.pa.us
Subject: Re: Why performance improvement on converting subselect
In-Reply-To: <3F281EB7.7010308@trade-india.com>
References: <200307291114.29567.mallah@trade-india.com> <3F26A503.3050104@trade-india.com> <4578.1059514334@sss.pgh.pa.us> <200307301254.49159.mallah@trade-india.com> <9198.1059573083@sss.pgh.pa.us> <3F281EB7.7010308@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox