From: Richard Huxton <dev@archonet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Scalar in a range (but textual not numeric)
Date: Wed, 25 Feb 2004 22:08:55 +0000
Message-ID: <200402252208.55502.dev@archonet.com> (raw)
In-Reply-To: <6803.1077744728@sss.pgh.pa.us>
References: <200402251918.20289.dev@archonet.com>
<6803.1077744728@sss.pgh.pa.us>
On Wednesday 25 February 2004 21:32, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Large table representing non-overlapping blocks:
> > blocks(id int4, min varchar, max varchar)
> >
> > SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
> >
> > The estimator gets the wrong plan because it doesn't realise there's
> > (at most) only one block that can match.
>
> Even if it did realize that, it couldn't do much, because this query
> isn't indexable as it stands.
Well, it is in the sense that an index can be used. Here I'd defined pkey as
(min,max,id) and set enable_seqscan=off
-> Index Scan using prnblock_range_pkey on prnblock_range
(cost=0.00..1403.99 rows=892 width=33) (actual time=23.88..24.07 rows=1
loops=1)
Index Cond: (('09050091234'::character varying >= pr_min) AND
('09050091234'::character varying <= pr_max))
Of course, what I really want is a "varchar_range" type with its own
indexing...
> I wonder whether you could adapt the "line segment" datatype
> (see contrib/seg/) into a sort of "text segment" thingy and use the
> GiST indexing support on that. You'd have a query like
> WHERE min_max_object overlaps-operator 'ABCDE'
> and the overlaps operator would be a GiST-indexable one.
Yep, that's the sort of thing I was wanting, just not worth the trouble in
this case. It's not the heart of the system, only a corner case.
Thanks anyway Tom
--
Richard Huxton
Archonet Ltd
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, tgl@sss.pgh.pa.us
Subject: Re: Scalar in a range (but textual not numeric)
In-Reply-To: <200402252208.55502.dev@archonet.com>
* 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