From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gabriele Bartolini <angusgb@tin.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Normal case or bad query plan?
Date: Tue, 12 Oct 2004 01:45:15 -0400
Message-ID: <28411.1097559915@sss.pgh.pa.us> (raw)
In-Reply-To: <6.1.2.0.2.20041012072112.01ed8810@box.tin.it>
References: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it>
<23515.1097530437@sss.pgh.pa.us>
<6.1.2.0.2.20041012072112.01ed8810@box.tin.it>
Gabriele Bartolini <angusgb@tin.it> writes:
> Seq Scan on ip2location (cost=0.00..30490.65 rows=124781 width=8)
> (actual time=5338.120..40237.283 rows=1 loops=1)
> Filter: ((1040878301::bigint >= ip_address_from) AND
> (1040878301::bigint <= ip_address_to))
> Total runtime: 40237.424 ms
>>
>> I suspect that you haven't ANALYZEd this table in a long time, if ever.
>> You really need reasonably up-to-date ANALYZE stats if you want the
>> planner to do an adequate job of planning range queries.
> That's the thing ... I had just peformed a VACUUM ANALYSE :-(
In that case I think Kris Jurka had it right: the problem is the planner
doesn't know enough about the relationship of the ip_address_from and
ip_address_to columns to realize that this is a very selective query.
But actually, even *had* it realized that, it would have had little
choice but to use a seqscan, because neither of the independent
conditions is really very useful as an index condition by itself.
Assuming that this problem is representative of your query load, you
really need to recast the data representation to make it more readily
searchable. I think you might be able to get somewhere by combining
ip_address_from and ip_address_to into a single CIDR column and then
using the network-overlap operator to probe for matches to your query
address. (This assumes that the from/to pairs are actually meant to
represent CIDR subnets; if not you need some other idea.) Another
possibility is to convert to a geometric type and use an rtree index
with an "overlaps" operator. I'm too tired to work out the details,
but try searching for "decorrelation" in the list archives to see some
related problems.
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, angusgb@tin.it
Subject: Re: Normal case or bad query plan?
In-Reply-To: <28411.1097559915@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