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 A2AD05396C for ; Wed, 6 Apr 2005 21:51:32 +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 00797-08 for ; Wed, 6 Apr 2005 20:51:29 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E3E2B538E5 for ; Wed, 6 Apr 2005 21:51:28 +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 j36KpUWB006000; Wed, 6 Apr 2005 16:51:30 -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: <425423A9.9000504@vulcanus.its.tudelft.nl> References: <425413D3.5030304@vulcanus.its.tudelft.nl> <28552.1112809334@sss.pgh.pa.us> <425423A9.9000504@vulcanus.its.tudelft.nl> Comments: In-reply-to Arjen van der Meijden message dated "Wed, 06 Apr 2005 20:00:09 +0200" Date: Wed, 06 Apr 2005 16:51:30 -0400 Message-ID: <5999.1112820690@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/124 X-Sequence-Number: 11521 Arjen van der Meijden writes: > On 6-4-2005 19:42, Tom Lane wrote: >> 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: >> 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. Planner error ... because it doesn't have any good way to estimate the number of matching rows, it thinks that way is a bit more expensive than data_main as the outside, but in reality it seems a good deal cheaper: arjen=# set enable_seqscan TO 1; SET arjen=# explain analyze arjen-# SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=332586.85..332586.85 rows=1 width=0) (actual time=143999.678..143999.683 rows=1 loops=1) -> Nested Loop (cost=3.76..328945.96 rows=1456356 width=0) (actual time=0.211..143549.461 rows=82688 loops=1) Join Filter: (("outer".range >= "inner".range_from) AND ("outer".range <= "inner".range_till)) -> Seq Scan on data_main dm (cost=0.00..1262.20 rows=81920 width=2) (actual time=0.059..663.065 rows=81920 loops=1) -> Materialize (cost=3.76..5.36 rows=160 width=4) (actual time=0.004..0.695 rows=160 loops=81920) -> Seq Scan on postcodes p (cost=0.00..3.60 rows=160 width=4) (actual time=0.028..1.589 rows=160 loops=1) Total runtime: 144000.415 ms (7 rows) arjen=# set enable_seqscan TO 0; SET arjen=# explain analyze arjen-# SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=100336307.18..100336307.18 rows=1 width=0) (actual time=2367.097..2367.102 rows=1 loops=1) -> Nested Loop (cost=100000000.00..100332666.28 rows=1456356 width=0) (actual time=0.279..1918.890 rows=82688 loops=1) -> Seq Scan on postcodes p (cost=100000000.00..100000003.60 rows=160 width=4) (actual time=0.060..1.381 rows=160 loops=1) -> Index Scan using dm_range on data_main dm (cost=0.00..1942.60 rows=9103 width=2) (actual time=0.034..7.511 rows=517 loops=160) Index Cond: ((dm.range >= "outer".range_from) AND (dm.range <= "outer".range_till)) Total runtime: 2368.056 ms (6 rows) (this machine is slower than yours, plus I have profiling enabled still...) regards, tom lane