Re: RTREE on points

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(at)propertykey(dot)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