Hello,
I have a table like this with some indexes as identified:
CREATE TABLE sometable (
dataTEXT,
data_ftiTSVECTOR,
category1 INTEGER,
category2 INTEGER,
category3 INTEGER
);
CREATE OR REPLACE FUNCTION is_null(anyelement) RETURNS BOOLEAN AS 'SELECT
$1 IS
"Net Virtual Mailing Lists" <[EMAIL PROTECTED]> writes:
> 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
"Net Virtual Mailing Lists" <[EMAIL PROTECTED]>(by way of Net Virtual Mailing Lists
<[EMAIL PROTECTED]>) writes:
> >SELECT * from sometable WHERE category1 IS NOT NULL
> > AND data_fti @@ to_tsquery('default', 'postgres');
What you can do is a partial index:
create index fulltextindex on some
Greg Stark <[EMAIL PROTECTED]> writes:
> 8.0 will have statistics on how often is_null() will return false. But that
> isn't really going to solve your problem since it still won't have any idea
> how many rows the full text search will find.
> I don't even know of anything you can do to influence
"Net Virtual Mailing Lists" <[EMAIL PROTECTED]> 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
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" <[EMAIL PROTECTED]> writes:
>> I have a table like this with some indexes as identified:
>
>> CREATE O
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_
