Re: Index problem.... GIST (tsearch2)

"Net Virtual Mailing Lists" <mailinglists@net-virtual.com> writes:
> I have a table like this with some indexes as identified:

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

[ raises eyebrow... ]  It'd be easier to offer advice if you accurately
depicted what you'd done.  The above isn't even syntactically valid.

I suppose what you meant is

CREATE INDEX sometable_category1_idx ON sometable (is_null(category1));

The main problem with this is that before 8.0 there are no stats on
functional indexes, and so the planner has no idea that the condition
is_null(category1)='f' is very selective.  (If you looked at the
rowcount estimates from EXPLAIN this would be pretty obvious.)

What I would suggest is that you forget the functional indexes and use
partial indexes:

CREATE INDEX sometable_category1_idx ON sometable (category1)
WHERE category1 IS NOT NULL;

SELECT * from sometable WHERE category1 IS NOT NULL AND data_fti @@
to_tsquery('default', 'postgres');

7.4 has a reasonable chance of figuring out that the category1_idx
is the thing to use if you cast it this way.

            regards, tom lane