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 B819553332 for ; Wed, 6 Apr 2005 19:00:46 +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 40856-10 for ; Wed, 6 Apr 2005 18:00:07 +0000 (GMT) Received: from olive.qinip.net (olive.qinip.net [62.100.30.40]) by svr1.postgresql.org (Postfix) with ESMTP id 46FD2534C3 for ; Wed, 6 Apr 2005 19:00:06 +0100 (BST) Received: from [10.0.0.2] (h8441139206.dsl.speedlinq.nl [84.41.139.206]) by olive.qinip.net (Postfix) with ESMTP id 07BA818148; Wed, 6 Apr 2005 20:00:04 +0200 (MEST) Message-ID: <425423A9.9000504@vulcanus.its.tudelft.nl> Date: Wed, 06 Apr 2005 20:00:09 +0200 From: Arjen van der Meijden User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: performance pgsql Subject: Re: Plan for relatively simple query seems to be very inefficient References: <425413D3.5030304@vulcanus.its.tudelft.nl> <28552.1112809334@sss.pgh.pa.us> In-Reply-To: <28552.1112809334@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/112 X-Sequence-Number: 11509 On 6-4-2005 19:42, Tom Lane wrote: > 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.) No it didn't prefer it. > 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 Yep, there is only one or in corner cases two postcode-ranges per postcode. Actually it should be only one, but my generated data is not perfect. But the sequential scan per record is not really what surprises me, especially since the postcode table is only two pages of data, I didn't really expect otherwise. It is the fact that it takes 22 seconds that surprises me. Especially since the two other examples on the same data which consider about the same amount of records per table/record only take 1.4 and 0.18 seconds. Best regards, Arjen