From: | "Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)com> |
---|---|
To: | "Pgsql General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Index problem.... GIST (tsearch2) |
Date: | 2004-10-08 04:18:21 |
Message-ID: | 20041008041821.6418@mail.net-virtual.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks, you are right, I mis-typed the statements (lack of sleep
*shrug*), thanks for parsing through it...
Your suggestion did resolve the situation nicely!
- Greg
>"Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2004-10-08 04:35:50 | Re: flattened tables with normalized tables |
Previous Message | Dennis Gearon | 2004-10-08 04:08:18 | Re: flattened tables with normalized tables |