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

Re: Operator class and index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr>
Cc: "pgsql interfaces" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: Operator class and index
Date: 2001-12-04 15:11:56
Message-ID: 28752.1007478716@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
"Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr> writes:
> I tried to write a "begins with"  operator to use it instead of LIKE 'xx%',
> because LIKE 'xx%' can't use index.

Why not?  It should be able to --- unless you're using a non-C locale,
in which case the problem is fundamental and not easily worked around
just by creating another operator.

> 3. Inserted record for begins_with in pg_ampop:

You can't just invent any old operator and then plop it into pg_amop
with a randomly-chosen strategy number.  Btree indexes only know about
strategy numbers 1 to 5, and those numbers have very definite
implications about the semantics of the operator: the operator had
better behave as <, <=, =, >=, or > (not sure which one is which number)
with respect to the standard sort ordering of the indexed datatype.
The other index types also have preconceived notions about the meaning
of the strategy numbers that they understand.

> 5. When I try to get plan for this :
>  EXPLAIN SELECT * from zemlje WHERE naziv<=3D=3D'A';=20
> =20
>  I got :
>  Index Scan using ix_zemlje_naziv on zemlje  (cost=3D0.00..17.07 rows=3D5 w=
> idth=3D405)
> =20
> It seem that everything is ok, but when I execute the query I got empty rec=
> ordset.

I'm surprised you didn't get an Assert failure.  The nbtree routines
have no idea what to do with strategy number 6.

			regards, tom lane

In response to

Responses

pgsql-interfaces by date

Next:From: Jan WieckDate: 2001-12-05 21:44:07
Subject: Re: Cant load pgtclsh library into application
Previous:From: Andreas KretzerDate: 2001-12-04 11:18:45
Subject: Re: TCL with large objects on Win

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