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