Index problem.... GIST (tsearch2)

Поиск
Список
Период
Сортировка
От Net Virtual Mailing Lists
Тема Index problem.... GIST (tsearch2)
Дата
Msg-id 20041007215200.31633@mail.net-virtual.com
обсуждение исходный текст
Ответы Re: Index problem.... GIST (tsearch2)  (Greg Stark <gsstark@mit.edu>)
Re: Index problem.... GIST (tsearch2)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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






В списке pgsql-general по дате отправления:

Предыдущее
От: Kris Jurka
Дата:
Сообщение: Re: [JDBC] Problem with boolean type
Следующее
От: "Net Virtual Mailing Lists" (by way of Net Virtual Mailing Lists
Дата:
Сообщение: Re: Index problem.... GIST (tsearch2)