X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A5F42D1C90E for ; Tue, 29 Jul 2003 05:44:14 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 48845-04 for ; Tue, 29 Jul 2003 02:43:17 -0300 (ADT) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by svr1.postgresql.org (Postfix) with SMTP id 5BC94D1C510 for ; Tue, 29 Jul 2003 02:44:11 -0300 (ADT) Received: (qmail 8688 invoked from network); 29 Jul 2003 05:53:12 -0000 Received: from unknown (HELO system67.trade-india-local.com) (unknown) by unknown with SMTP; 29 Jul 2003 05:53:12 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: pgsql-performance@postgresql.org Subject: Why performance improvement on converting subselect to a function ? Date: Tue, 29 Jul 2003 11:14:29 +0530 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307291114.29567.mallah@trade-india.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/455 X-Sequence-Number: 2748 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.