pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedint4 in a GiST index
3+ messages / 2 participants
[nested] [flat]
* int4 in a GiST index
@ 2004-11-10 21:51 Mike Rylander <mrylander@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mike Rylander @ 2004-11-10 21:51 UTC (permalink / raw)
To: pgsql-performance
Hello all,
I am using tsearch2 to (imagine this... :) index a text field. There
is also a, for lack of a better name, "classification" field called
'field' that will be used to group certain rows together.
CREATE TABLE biblio.metarecord_field_entry (
record BIGINT REFERENCES biblio.metarecord (id)
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE
INITIALLY DEFERRED,
field INT NOT NULL
REFERENCES biblio.metarecord_field_map (id)
ON UPDATE CASCADE
ON DELETE CASCADE
DEFERRABLE
INITIALLY DEFERRED,
value TEXT,
value_fti tsvector,
source BIGINT NOT NULL
REFERENCES biblio.record (id)
ON UPDATE CASCADE
ON DELETE CASCADE
DEFERRABLE
INITIALLY DEFERRED
) WITHOUT OIDS;
Because there will be "or" queries against the 'value_fti' I want to
create a multi-column index across the tsvector and classification
columns as that should help with selectivity. But because there is no
GiST opclass for INT4 the index creation complains thusly:
oils=# CREATE INDEX metarecord_field_entry_value_and_field_idx ON
biblio.metarecord_field_entry USING GIST (field, value_fti);
ERROR: data type integer has no default operator class for access
method "gist"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
I attempted to give it the 'int4_ops' class, but that also complains:
oils=# CREATE INDEX metarecord_field_entry_value_and_field_idx ON
biblio.metarecord_field_entry USING GIST (value_fti, field int4_ops);
ERROR: operator class "int4_ops" does not exist for access method "gist"
I couldn't find any info in the docs (7.4 and 8.0.0b4) for getting
GiST to index standard integers. I'm sure this has been done before,
but I've note found the magic spell. Of course, I may just be barking
up the wrong tree altogether...
Thanks in advance!
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: int4 in a GiST index
@ 2004-11-11 02:50 George Essig <george_essig@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: George Essig @ 2004-11-11 02:50 UTC (permalink / raw)
To: Mike Rylander <mrylander@gmail.com>; +Cc: pgsql-performance
Mike Rylander wrote:
> I want to create a multi-column index across the tsvector and classification
> columns as that should help with selectivity. But because there is no
> GiST opclass for INT4 the index creation complains thusly:
Install contrib/btree_gist along with contrib/tsearch2 to create a multicolumn index on the in4
and the tsvector columns. See the following for an example:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/multi_column_index.html
George Essig
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: int4 in a GiST index
@ 2004-11-11 05:53 Mike Rylander <mrylander@gmail.com>
parent: George Essig <george_essig@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mike Rylander @ 2004-11-11 05:53 UTC (permalink / raw)
To: George Essig <george_essig@yahoo.com>; +Cc: pgsql-performance
On Wed, 10 Nov 2004 18:50:28 -0800 (PST), George Essig
<george_essig@yahoo.com> wrote:
> Mike Rylander wrote:
>
> > I want to create a multi-column index across the tsvector and classification
> > columns as that should help with selectivity. But because there is no
> > GiST opclass for INT4 the index creation complains thusly:
>
> Install contrib/btree_gist along with contrib/tsearch2 to create a multicolumn index on the in4
> and the tsvector columns. See the following for an example:
>
> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/multi_column_index.html
>
> George Essig
>
Thanks a million. I had actually just found the answer after some
more googling, but I hadn't seen that page and it happens to be
exactly what I wanted.
As a side note I'd like to thank everyone here (and especially George,
in this case). I've been on these lists for quite a while and I'm
always amazed at the speed, accuracy and precision of the answers on
the PG mailing lists.
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-11 05:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-10 21:51 int4 in a GiST index Mike Rylander <mrylander@gmail.com>
2004-11-11 02:50 Re: int4 in a GiST index George Essig <george_essig@yahoo.com>
2004-11-11 05:53 ` Mike Rylander <mrylander@gmail.com>
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