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.
