Index problem.... GIST (tsearch2)

From: "Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)com>
To: "Pgsql General" <pgsql-general(at)postgresql(dot)org>
Subject: Index problem.... GIST (tsearch2)
Date: 2004-10-07 21:52:00
Message-ID: 20041007215200.31633@mail.net-virtual.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have a table like this with some indexes as identified:

CREATE TABLE sometable (
data TEXT,
data_fti TSVECTOR,
category1 INTEGER,
category2 INTEGER,
category3 INTEGER
);

CREATE OR REPLACE FUNCTION is_null(anyelement) RETURNS BOOLEAN AS 'SELECT
$1 IS NULL;' LANGUAGE 'SQL' IMMUTABLE;
CREATE FUNCTION sometable_category1_idx ON sometable (category1);
CREATE FUNCTION sometable_category2_idx ON sometable (category2);
CREATE FUNCTION sometable_category3_idx ON sometable (category3);

CREATE FUNCTION sometable_data_fti_idx ON sometable USING gist(data_fti);

When I do a query like this, it uses sometable_category1_idx and is very
fast (it only returns a few rows out of several thousand)

SELECT * from sometable WHERE is_null(category1)='f';

When I do a query like this though it is slow because it insists on doing
the full-text index first:

SELECT * from sometable WHERE is_null(category1)='f' AND data_fti @@
to_tsquery('default', 'postgres');

How can I make this query first use the is_null index?... It strikes me
that this would almost always be faster then doing the full-text search
first, right?...

Thanks!

- Greg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Net Virtual Mailing Lists 2004-10-07 22:55:18 Re: Index problem.... GIST (tsearch2)
Previous Message David Bitner 2004-10-07 21:07:08 regular expression searches