pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: pgsql-performance@postgresql.org
Subject: Why performance improvement on converting subselect to a function ?
Date: Tue, 29 Jul 2003 11:14:29 +0530
Message-ID: <200307291114.29567.mallah@trade-india.com> (raw)


Hi,

For each company_id in certain table i have to search the same table
get certain rows sort them and pick up the top one , i tried using this
subselect:

explain analyze SELECT company_id , (SELECT edition FROM ONLY 
public.branding_master b WHERE old_company_id = a.company_id OR company_id = 
a.company_id  ORDER BY b.company_id DESC LIMIT 1) from public.branding_master 
a   limit 50;


                                                                                
QUERY PLAN

Limit  (cost=0.00..3.52 rows=50 width=4) (actual time=463.97..19429.54 rows=50 
loops=1)
  ->  Seq Scan on branding_master a  (cost=0.00..6530.79 rows=92679 width=4) 
(actual time=463.97..19429.28 rows=51 loops=1)
        SubPlan
          ->  Limit  (cost=0.00..168.36 rows=1 width=6) (actual 
time=66.96..380.94 rows=1 loops=51)
                ->  Index Scan Backward using branding_master_pkey on 
branding_master b  (cost=0.00..23990.26 rows=142 width=6) (actual 
time=66.95..380.93 rows=1 loops=51)
                      Filter: ((old_company_id = $0) OR (company_id = $0))
Total runtime: 19429.76 msec
(7 rows)

Very Slow 20 secs.


CREATE FUNCTION most_recent_edition (integer) returns integer AS 'SELECT 
edition::integer FROM ONLY public.branding_master b WHERE old_company_id = $1 
OR company_id = $1  ORDER BY b.company_id DESC LIMIT 1 ' language 'sql';

tradein_clients=# explain analyze SELECT company_id , 
most_recent_edition(company_id) from public.branding_master limit 50;

QUERY PLAN

Limit  (cost=0.00..3.52 rows=50 width=4) (actual time=208.23..3969.39 rows=50 
loops=1)
  ->  Seq Scan on branding_master  (cost=0.00..6530.79 rows=92679 width=4) 
(actual time=208.22..3969.15 rows=51 loops=1)
Total runtime: 3969.52 msec
(3 rows)

Time: 4568.33 ms

 4 times faster.


But i feel it can be lot more faster , can anyone suggest me something
to try.

Indexes exists on company_id(pkey) and old_company_id Most of the chores 
are already done [ vacuum full analyze , reindex ]


Regds
mallah.









view thread (9+ messages)  latest in thread

Message-ID: <200307291114.29567.mallah@trade-india.com>
Permalink:  ../200307291114.29567.mallah@trade-india.com/
Also on:    postgresql.org/message-id/200307291114.29567.mallah@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
  Subject: Re: Why performance improvement on converting subselect to a function ?
  In-Reply-To: <200307291114.29567.mallah@trade-india.com>
  References: <200307291114.29567.mallah@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