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.







---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to