X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C9D4D53A3C for ; Wed, 6 Apr 2005 18:42:16 +0100 (BST) 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 36899-08 for ; Wed, 6 Apr 2005 17:42:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 681E953570 for ; Wed, 6 Apr 2005 18:42:13 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j36HgEpD028553; Wed, 6 Apr 2005 13:42:14 -0400 (EDT) To: Arjen van der Meijden Cc: performance pgsql Subject: Re: Plan for relatively simple query seems to be very inefficient In-reply-to: <425413D3.5030304@vulcanus.its.tudelft.nl> References: <425413D3.5030304@vulcanus.its.tudelft.nl> Comments: In-reply-to Arjen van der Meijden message dated "Wed, 06 Apr 2005 18:52:35 +0200" Date: Wed, 06 Apr 2005 13:42:14 -0400 Message-ID: <28552.1112809334@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/109 X-Sequence-Number: 11506 Arjen van der Meijden writes: > I noticed on a forum a query taking a surprisingly large amount of time > in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much > better. To my surprise PostgreSQL was ten times worse on the same > machine! And I don't understand why. Wrong index ... what you probably could use here is an index on data_main.range, so that the query could run with postcodes as the outer side. I get such a plan by default with empty tables: Aggregate (cost=99177.80..99177.80 rows=1 width=0) -> Nested Loop (cost=0.00..98021.80 rows=462400 width=0) -> Seq Scan on postcodes p (cost=0.00..30.40 rows=2040 width=4) -> Index Scan using rangei on data_main dm (cost=0.00..44.63 rows=227 width=2) Index Cond: ((dm.range >= "outer".range_from) AND (dm.range <= "outer".range_till)) but I'm not sure if the planner would prefer it with the tables loaded up. (It might not be the right thing anyway ... but seems worth trying.) Given the relatively small size of the postcodes table, and the fact that each data_main row seems to join to about one postcodes row, it's possible that what the planner did for you was actually the optimal thing anyhow. I'm not sure that any range-capable index would be faster than just scanning through 160 entries in memory ... regards, tom lane