X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5E54F3A433C for ; Wed, 10 Nov 2004 21:51:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35850-02 for ; Wed, 10 Nov 2004 21:51:35 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.201]) by svr1.postgresql.org (Postfix) with ESMTP id 29F4C3A4318 for ; Wed, 10 Nov 2004 21:51:36 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a36so245271rnf for ; Wed, 10 Nov 2004 13:51:36 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=RqmdZQjgst+zmt/yqZ/KZj0wti6ZCJOiFMGscTRXzSYx6Sx1q17F2Hjr2dvt1k0JgsYeMY2Er3m2Om9P81tWdjH02daqLJ+vZ8EAdNlrtxG+/z/I6wI/prPyJGEGK0gnwHRRwVhTuyReD+GRZdyOeRosJ67VhbU5jyBYfl7Yhas= Received: by 10.38.82.80 with SMTP id f80mr354036rnb; Wed, 10 Nov 2004 13:51:36 -0800 (PST) Received: by 10.38.126.17 with HTTP; Wed, 10 Nov 2004 13:51:35 -0800 (PST) Message-ID: Date: Wed, 10 Nov 2004 16:51:35 -0500 From: Mike Rylander Reply-To: Mike Rylander To: pgsql-performance@postgresql.org Subject: int4 in a GiST index Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/176 X-Sequence-Number: 9130 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