Postgres Pro
Downloads
Home   >   mailing lists

Thread: Composite GiST indexes?

Composite GiST indexes?

From
Christopher Kings-Lynne
Date:
Is it possible to make a composite GiST index?  I want to create an 
index on a txtidx and a timestamp column - is that at all possible?

Chris

Re: Composite GiST indexes?

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Is it possible to make a composite GiST index?  I want to create an 
> index on a txtidx and a timestamp column - is that at all possible?

Yeah.  If you have a problem with that, it's grounds for a bug report.
Note though that GiST can't handle entries with NULL in the first
column, so it's best to put a NOT NULL column first if possible.
        regards, tom lane

Re: Composite GiST indexes?

From
Christopher Kings-Lynne
Date:
>>Is it possible to make a composite GiST index?  I want to create an 
>>index on a txtidx and a timestamp column - is that at all possible?

OK, this is what I'm trying (7.3.4):

create index blah on forums_posts using gist(ftiidx, datetime);

ERROR:  data type timestamp with time zone has no default operator class 
for access method "gist"        You must specify an operator class for the index or define a        default operator
classfor the data type
 

I guess I can put an ops indicator in there, but I have no idea what to 
try using...

Chris

Re: Composite GiST indexes?

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> OK, this is what I'm trying (7.3.4):
> create index blah on forums_posts using gist(ftiidx, datetime);
> ERROR:  data type timestamp with time zone has no default operator class 
> for access method "gist"

That's got nothing to do with it being a two-column index --- it's that
GiST doesn't know what to do with a timestamptz, in any column.

I see that there is support for timestamp without time zone in
contrib/btree_gist --- probably it would be a simple matter to extend
that to timestamp with time zone.  But it's not there today (much less
in 7.3 ;-))
        regards, tom lane

Re: Composite GiST indexes?

From
Oleg Bartunov
Date:
On Mon, 5 Jan 2004, Christopher Kings-Lynne wrote:

> Is it possible to make a composite GiST index?  I want to create an
> index on a txtidx and a timestamp column - is that at all possible?

you need to install contrib/btree_gist

btw, tsearch2 is much better than tsearch.
Check http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2


>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: Composite GiST indexes?

From
Oleg Bartunov
Date:
On Mon, 5 Jan 2004, Christopher Kings-Lynne wrote:

> >>Is it possible to make a composite GiST index?  I want to create an
> >>index on a txtidx and a timestamp column - is that at all possible?
>
> OK, this is what I'm trying (7.3.4):
>
> create index blah on forums_posts using gist(ftiidx, datetime);
>
> ERROR:  data type timestamp with time zone has no default operator class
> for access method "gist"
>          You must specify an operator class for the index or define a
>          default operator class for the data type
>
> I guess I can put an ops indicator in there, but I have no idea what to
> try using...

install contrib/btree_gist


>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: Composite GiST indexes?

From
George Essig
Date:
>>Is it possible to make a composite GiST index?  I want to create an 
>>index on a txtidx and a timestamp column - is that at all possible?
> 
> OK, this is what I'm trying (7.3.4):
> 
> create index blah on forums_posts using gist(ftiidx, datetime);
> 
> ERROR:  data type timestamp with time zone has no default operator 
> class 
> for access method "gist"
>          You must specify an operator class for the index or define a
>          default operator class for the data type
> 
> I guess I can put an ops indicator in there, but I have no idea what to 
> try using...
> 
> Chris

Install contrib/btree_gist so you can include the timestamp field in a gist index.  Teodor Sigaev
suggested this on the openfts mailing list at:
http://sourceforge.net/mailarchive/message.php?msg_id=5349399

The btree_gist readme file says it supports the int2, int8, float4, float8, and timestamp data
types.  

George Essig