Re: Index problem.... GIST (tsearch2) - Mailing list pgsql-general

Than you very much Andrew...

Yes you are right.. I mis-typeed CREATE INDEX.. ;-)

The actual create indexes are as you suggested:

CREATE INDEX sometable_category1_idx ON sometable (is_null(category1));
CREATE INDEX sometable_category2_idx ON sometable (is_null(category2));
CREATE INDEX sometable_category3_idx ON sometable (is_null(category3));


In an effort to simplify the problem, I was indicating the columns are
integers.. They are, in fact, ltree and everytime I did a "SELECT * from
sometable WHERE category1 IS NOT NULL", it shows that it does a
sequential scan.. Thats the reason I created the is_null function
(perhaps that was a bad idea, but I just could not make it work)...

Regardless, it still won't use my is_null index first, any way around that?..

- Greg

>Hi:
>
>> 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);
>>
>
>Alright ... there's something whacky here.  I think you meant to use CREATE
>INDEX?  Is that right?
>
>CREATE INDEX sometable_category1_idx ON sometable (category1);
>
>> 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?...
>>
>
>
>It looks to me like there are better ways to accomplish what you are doing.
>
>Your indexes have indexed the value of the column ... not the value of your
>function is_null(column).  Which you could also do like this:
>
>CREATE INDEX sometable_category1_idx ON sometable (is_null(category1));
>
>That would probably speed up the second query ... but I think that would be
>slower than leaving an index on the INTEGER value and writing the query like
>this:
>
>SELECT * from sometable WHERE category1 IS NOT NULL
> AND data_fti @@ to_tsquery('default', 'postgres');
>
>Why bother to write a function when what you want is supported right in
SQL?
>Avoids a function call.  You might want to think about exactly what you want
>to index ... indexes can be costly if not used on frequent access columns.
>
>
>
>HTH,
>
>
>Andy
>