pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>  
>





view thread (9+ messages)  latest in thread

Message-ID: <3F281EB7.7010308@trade-india.com>
Permalink:  ../3F281EB7.7010308@trade-india.com/
Also on:    postgresql.org/message-id/3F281EB7.7010308@trade-india.com

 · 

reply

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