From: Julian Scarfe <julian@avbrief.com>
To: Jeff Hoffmann <jeff@propertykey.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: RTREE on points
Date: Mon, 16 Apr 2001 16:34:23 +0100
Message-ID: <008f01c0c68a$b6651c00$2285fd3e@julian> (raw)
References: <9bbtqv$688$1@news.tht.net>
<3ADAFF38.E7147C8D@propertykey.com>
Julian Scarfe wrote:
> >
> > explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box;
> > NOTICE: QUERY PLAN:
> > Seq Scan on nodes (cost=0.00..1.10 rows=1 width=28)
From: "Jeff Hoffmann" <jeff@propertykey.com>
> this should work, assuming you have enough points to make a difference
> (in the optimizer's mind, at least). the optimizer still doesn't do a
> great job of knowing when it's best to use an index, although, in your
> sample, there's no way it would ever be cheaper to use an index.
> there's simply not enough data there. you can test to see if an index
> can be used by a query by shutting off the sequential scans (set
> enable_seqscan=off) and retrying the query. essentially, this forces it
> to use an index scan if at all possible.
And indeed it does, thank you, Jeff:
# set enable_seqscan=off;
SET VARIABLE
# explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box;
NOTICE: QUERY PLAN:
Index Scan using test_rtree on nodes (cost=0.00..2.02 rows=1 width=28)
It hadn't occured to me that the index would simply not be used and I'm
grateful for the pointer to the appropriate variable.
Nevertheless, wouldn't...
CREATE INDEX test_rtree ON nodes USING RTREE (node);
(which fails)
...be a lot simpler than...
CREATE INDEX test_rtree ON nodes USING RTREE (box(node,node));
(which succeeds, as above)
?
The latter feels contorted and possibly inefficient. After all, I don't
do...:
CREATE TABLE "nodes" (
"node" point,
"node_name" character varying(30)
);
INSERT INTO nodes VALUES ('(1,1)', 'a');
INSERT INTO nodes VALUES ('(1,2)', 'b');
INSERT INTO nodes VALUES ('(3,2)', 'c');
INSERT INTO nodes VALUES ('(5,4)', 'd');
INSERT INTO nodes VALUES ('(7,8)', 'e');
INSERT INTO nodes VALUES ('(11,10)', 'f');
INSERT INTO nodes VALUES ('(101,11)', 'g');
CREATE INDEX test_btree ON nodes USING BTREE (textcat(node_name,node_name));
...if I want to index by name? (even though in principle it would work)
Thanks for any guidance.
Julian Scarfe
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: julian@avbrief.com, jeff@propertykey.com
Subject: Re: RTREE on points
In-Reply-To: <008f01c0c68a$b6651c00$2285fd3e@julian>
References: <9bbtqv$688$1@news.tht.net> <3ADAFF38.E7147C8D@propertykey.com> <008f01c0c68a$b6651c00$2285fd3e@julian>
* 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