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 263CD53441 for ; Thu, 24 Mar 2005 08:19:51 +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 18227-10 for ; Thu, 24 Mar 2005 08:19:43 +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 52A195343E for ; Thu, 24 Mar 2005 08:19:42 +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 1DENah-00077q-Eb; Thu, 24 Mar 2005 08:21:20 +0000 Reply-To: From: "Thilina Gunasekara" To: "'Tom Lane'" Cc: "'PGSQL Admin'" Subject: Re: Why this Query Plan is different Date: Thu, 24 Mar 2005 14:19:17 +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 In-Reply-To: <23424.1111643969@sss.pgh.pa.us> Thread-Index: AcUwOLowdVniY9YYQe2pB3k9wJWb0AADc0MA 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: <20050324081942.52A195343E@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.278 tagged_above=0 required=5 tests=AWL, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200503/385 X-Sequence-Number: 16883 Hi Tom, Begging your pardon for mislabeling the query plan output, please find the correctly labeled output. **** This is Box A which runs on 7.4.5 and delivers very quick result. production=# SELECT version(); version ---------------------------------------------------------------------------- ----------------------------------------------------- PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6) (1 row) production=# EXPLAIN ANALYSE SELECT count(airport_code) FROM properties WHERE airport_code::character varying IN (SELECT airport_code::character varying FROM airport_codes); QUERY PLAN ---------------------------------------------------------------------------- ------------------------------------------------------ Aggregate (cost=7494.95..7494.95 rows=1 width=4) (actual time=285.821..285.823 rows=1 loops=1) -> Hash IN Join (cost=228.67..7393.46 rows=40593 width=4) (actual time=24.994..279.818 rows=5833 loops=1) Hash Cond: (("outer".airport_code)::text = (("inner".airport_code)::character varying)::text) -> Seq Scan on properties (cost=0.00..2156.93 rows=40593 width=4) (actual time=0.035..83.152 rows=40593 loops=1) -> Hash (cost=179.54..179.54 rows=7654 width=7) (actual time=24.491..24.491 rows=0 loops=1) -> Seq Scan on airport_codes (cost=0.00..179.54 rows=7654 width=7) (actual time=0.046..12.481 rows=7654 loops=1) Total runtime: 286.234 ms (7 rows) ****** And this is the Box B which runs on 7.3.9 and takes hours to deliver the result. Actually I didn't execute EXPLAIN ANALYSE on Box B, because when I tried, it went for more than 20-30 Minutes. production=> SELECT version(); version ---------------------------------------------------------------------------- ----------------------------------------------------------------- PostgreSQL 7.3.9 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1) (1 row) 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=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) (5 rows) Regards, Thilina -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane Sent: 24 March 2005 11:59 To: tgunasekara@openworld.org Cc: PGSQL Admin Subject: Re: [ADMIN] Why this Query Plan is different "Thilina Gunasekara" writes: > 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. 7.4 knows a great deal more about optimizing IN queries than 7.3 does. I think you have misidentified the two query plans, because "Hash IN Join" is not a plan type that 7.3 has at all. What is not clear to me is which version is really slower. Could we see EXPLAIN ANALYZE output (correctly labeled ;-)) not just EXPLAIN? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings