pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (13+ messages)  latest in thread

Message-ID: <008f01c0c68a$b6651c00$2285fd3e@julian>
Permalink:  ../008f01c0c68a$b6651c00$2285fd3e@julian/
Also on:    postgresql.org/message-id/008f01c0c68a$b6651c00$2285fd3e@julian

 · 

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