X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 32AA3CCCE7 for ; Fri, 18 Jul 2003 12:51:16 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 03835-03 for ; Fri, 18 Jul 2003 12:51:32 +0000 (GMT) Received: from rtlocal.trade-india.com (unknown [203.196.129.235]) by svr1.postgresql.org (Postfix) with SMTP id 748ACCB576 for ; Fri, 18 Jul 2003 09:50:59 -0300 (ADT) Received: (qmail 7484 invoked from network); 18 Jul 2003 12:59:35 -0000 Received: from unknown (HELO system67.trade-india-local.com) (unknown) by unknown with SMTP; 18 Jul 2003 12:59:35 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: pgsql-performance@postgresql.org Subject: Yet another slow join query.. Date: Fri, 18 Jul 2003 18:21:21 +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: <200307181821.21329.mallah@trade-india.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/289 X-Sequence-Number: 2582 Hi All, data_bank.updated_profiles and public.city_master are small tables with 21790 and 49303 records repectively. both have indexes on the join column. in first one on (city,source) and in second one on (city) The query below does not return for long durations > 10 mins. explain analyze select b.state,a.city from data_bank.updated_profiles a join public.city_master b using(city) where source='BRANDING' and a.state is NULL and b.country='India' ; simple explain returns below. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Nested Loop (cost=0.00..83506.31 rows=14 width=35) Join Filter: ("outer".city = ("inner".city)::text) -> Seq Scan on updated_profiles a (cost=0.00..1376.39 rows=89 width=11) Filter: ((source = 'BRANDING'::character varying) AND (state IS NULL)) -> Index Scan using city_master_temp1 on city_master b (cost=0.00..854.87 rows=5603 width=24) Filter: (country = 'India'::character varying) (6 rows) ----------------------------------------- Any help is appreciated. Regds mallah.