pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gabriele Bartolini <angusgb@tin.it>
To: pgsql-performance@postgresql.org
Subject: Normal case or bad query plan?
Date: Mon, 11 Oct 2004 23:05:59 +0200
Message-ID: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it> (raw)

Hi guys,

    please consider this scenario. I have this table:

CREATE TABLE ip2location (
     ip_address_from BIGINT NOT NULL,
     ip_address_to BIGINT NOT NULL,
     id_location BIGINT NOT NULL,
     PRIMARY KEY (ip_address_from, ip_address_to)
);

I created a cluster on its primary key, by running:
CLUSTER ip2location_ip_address_from_key ON ip2location;

This allowed me to organise data in a more efficient way: the data that is 
contained are ranges of IP addresses with empty intersections; for every IP 
class there is a related location's ID. The total number of entries is 1392443.

For every IP address I have, an application retrieves the corresponding 
location's id from the above table, by running a query like:

SELECT id_location FROM ip2location WHERE '11020000111' >= ip_address_from 
AND '11020000111' <= ip_address_to;

For instance, by running the 'EXPLAIN ANALYSE' command, I get this "funny" 
result:


                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
  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


With other data, that returns an empty set, I get:

explain SELECT id_location FROM ip2location WHERE '11020000111' >= 
ip_address_from AND '11020000111' <= ip_address_to;

                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------
  Index Scan using ip2location_ip_address_from_key on 
ip2location  (cost=0.00..419.16 rows=140 width=8)
    Index Cond: ((11020000111::bigint >= ip_address_from) AND 
(11020000111::bigint <= ip_address_to))


I guess the planner chooses the best of the available options for the first 
case, the sequential scan. This is not confirmed though by the fact that, 
after I ran "SET enable_scan TO off", I got this:
                                                                          QUERY 
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using ip2location_ip_address_from_key on 
ip2location  (cost=0.00..31505.73 rows=124781 width=8) (actual 
time=2780.172..2780.185 rows=1 loops=1)
    Index Cond: ((1040878301::bigint >= ip_address_from) AND 
(1040878301::bigint <= ip_address_to))
  Total runtime: 2780.359 ms


Is this a normal case or should I worry? What am I missing? Do you have any 
suggestion or comment to do (that would be extremely appreciated)? Is the 
CLUSTER I created worthwhile or not?

Thank you,
-Gabriele

--
Gabriele Bartolini: Web Programmer, ht://Dig & IWA/HWG Member, ht://Check 
maintainer
Current Location: Prato, Toscana, Italia
angusgb@tin.it | http://www.prato.linux.it/~gbartolini | ICQ#129221447
 > "Leave every hope, ye who enter!", Dante Alighieri, Divine Comedy, The 
Inferno

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.773 / Virus Database: 520 - Release Date: 05/10/2004

view thread (9+ messages)  latest in thread

Message-ID: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it>
Permalink:  ../6.1.2.0.2.20041011224414.01fadec0@box.tin.it/
Also on:    postgresql.org/message-id/6.1.2.0.2.20041011224414.01fadec0@box.tin.it

 · 

reply

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: angusgb@tin.it
  Subject: Re: Normal case or bad query plan?
  In-Reply-To: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it>

* 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