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 0977932A35C for ; Mon, 11 Oct 2004 22:06:05 +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 71639-01 for ; Mon, 11 Oct 2004 21:05:57 +0000 (GMT) Received: from vsmtp3.tin.it (vsmtp3alice.tin.it [212.216.176.143]) by svr1.postgresql.org (Postfix) with ESMTP id 16FD332A1C4 for ; Mon, 11 Oct 2004 22:05:57 +0100 (BST) Received: from angus.tin.it (82.51.56.159) by vsmtp3.tin.it (7.0.027) id 414B175C00A42ED0 for pgsql-performance@postgresql.org; Mon, 11 Oct 2004 23:05:57 +0200 Message-Id: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it> X-Sender: angusgb@box.tin.it (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.1.2.0 Date: Mon, 11 Oct 2004 23:05:59 +0200 To: pgsql-performance@postgresql.org From: Gabriele Bartolini Subject: Normal case or bad query plan? Mime-Version: 1.0 Content-Type: multipart/mixed; x-avg-checked=avg-ok-55D43834; boundary="=======6D9D418A=======" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_RFCI X-Spam-Level: X-Archive-Number: 200410/142 X-Sequence-Number: 8618 --=======6D9D418A======= Content-Type: text/plain; x-avg-checked=avg-ok-55D43834; charset=us-ascii; format=flowed Content-Transfer-Encoding: 8bit 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 --=======6D9D418A======= Content-Type: text/plain; charset=us-ascii; x-avg=cert; x-avg-checked=avg-ok-55D43834 Content-Disposition: inline --- 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 --=======6D9D418A=======--