X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B98F353387 for ; Thu, 24 Mar 2005 04:01:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47519-06 for ; Thu, 24 Mar 2005 04:01:21 +0000 (GMT) Received: from glenis.uk1.openworld.co.uk (glenis.uk1.openworld.co.uk [82.211.89.4]) by svr1.postgresql.org (Postfix) with ESMTP id 87BC753582 for ; Thu, 24 Mar 2005 04:01:17 +0000 (GMT) Received: from [203.115.30.220] (helo=Thilina) by glenis.uk1.openworld.co.uk with asmtp (TLSv1:RC4-MD5:128) (Exim 4.41) id 1DEJYa-0001RU-2y for pgsql-admin@postgresql.org; Thu, 24 Mar 2005 04:02:52 +0000 Reply-To: From: "Thilina Gunasekara" To: "PGSQL Admin" Subject: Why this Query Plan is different Date: Thu, 24 Mar 2005 10:00:48 +0600 Organization: Openworld Lanka (Pvt) Ltd MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcUwJg+jXtFDXjevRbCf3WgfjXIarg== X-SA-Exim-Connect-IP: 203.115.30.220 X-SA-Exim-Mail-From: tgunasekara@openworld.org X-SA-Exim-Scanned: No (on glenis.uk1.openworld.co.uk); SAEximRunCond expanded to false Message-Id: <20050324040117.87BC753582@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.704 tagged_above=0 required=5 tests=MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200503/381 X-Sequence-Number: 16879 Dear All, I have two PostgreSQL Database Servers running on Gentoo which versions are PostgreSQL 7.4.5 (Box A) and PostgreSQL 7.3.9 (Box B) Due to a recent query posted by a developer I was interested to know which factors are affecting these two servers to behave in a different manner to the same query. Please shed light on this. Box A (PostgreSQL 7.4.5) - EXPLAIN SELECT count(airport_code) FROM properties WHERE airport_code::character varying IN (SELECT airport_code::character varying FROM airport_codes); QUERY PLAN ---------------------------------------------------------------------------- Aggregate (cost=3605245.81..3605245.81 rows=1 width=4) -> Seq Scan on properties (cost=0.00..3605195.64 rows=20068 width=4) Filter: (subplan) SubPlan -> Seq Scan on airport_codes (cost=0.00..179.54 rows=7654 width=7) Box B (PostgreSQL 7.3.9) - This take much to process production=# EXPLAIN SELECT count(airport_code) FROM properties WHERE airport_code::character varying IN (SELECT airport_code::character varying FROM airport_codes); QUERY PLAN ---------------------------------------------------------------------------- Aggregate (cost=9421.05..9421.05 rows=1 width=13) -> Hash IN Join (cost=228.67..9320.65 rows=40158 width=13) Hash Cond: ((("outer".airport_code)::character varying)::text = (("inner".airport_code)::character varying)::text) -> Seq Scan on properties (cost=0.00..2137.58 rows=40158 width=13) -> Hash (cost=179.54..179.54 rows=7654 width=7) -> Seq Scan on airport_codes (cost=0.00..179.54 rows=7654 width=7) Thilina Gunasekara Database Administrator