Skip site navigation (1) Skip section navigation (2)

Re: Scalar in a range (but textual not numeric)

From: Richard Huxton <dev(at)archonet(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Scalar in a range (but textual not numeric)
Date: 2004-02-25 22:08:55
Message-ID: 200402252208.55502.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Wednesday 25 February 2004 21:32, Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)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

In response to

Responses

pgsql-sql by date

Next:From: Richard HuxtonDate: 2004-02-25 22:10:47
Subject: Re: Scalar in a range (but textual not numeric)
Previous:From: Tom LaneDate: 2004-02-25 21:32:08
Subject: Re: Scalar in a range (but textual not numeric)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group