From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
Cc: performance pgsql <pgsql-performance@postgresql.org>
Subject: Re: Plan for relatively simple query seems to be very inefficient
Date: Wed, 06 Apr 2005 16:51:30 -0400
Message-ID: <5999.1112820690@sss.pgh.pa.us> (raw)
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>
Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, acmmailing@vulcanus.its.tudelft.nl
Subject: Re: Plan for relatively simple query seems to be very inefficient
In-Reply-To: <5999.1112820690@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox