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
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 |