Skip site navigation (1) Skip section navigation (2)

Re: Problem with index in OR'd expression

From: postgresql(dot)org(at)tgice(dot)com
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Problem with index in OR'd expression
Date: 2006-12-27 17:02:02
Message-ID: 4592A70A.60108@tgice.com (view raw or flat)
Thread:
Lists: pgsql-general
Tom Lane wrote:
> postgresql(dot)org(at)tgice(dot)com writes:
>> I would submit that in that situation, it would be 
>> reasonable for a user to expect my suggested syntax to still use the 
>> indicated indexes.
> 
> The only thing that will make that work is if "indexed_col IS NULL" were
> an indexable condition, which it isn't because the PG index API only
> supports "indexed_col operator something" as an indexable condition
> (IS NULL is not an operator, and even if it were, there's no "something"
> on its righthand side).  Fixing this has been on the radar screen for
> awhile, but it's not done, largely for lack of agreement about a
> reasonably clean way to change that API.

Sorry to keep this issue alive even longer, Tom, but I think I may've 
been unclear with my example.

I was referring to the situation where one has this in a WHERE clause:

   ((vConstant IS NULL) OR (Table.IndexedCol = vConstant))

where vConstant is a *constant* parameter in a pl/pgsql function.

In the latest versions (8.1 *or* 8.2), would you expect this to 
successfully use the index on Table.IndexedCol and not have PG be 
confused (into a sequential scan) by the (vConstant IS NULL) expression?

As I indicated, I'm currently running 8.0.x, and am wondering whether it 
would be worth the effort to upgrade to 8.1 or 8.2 (Gentoo doesn't yet 
have PG at 8.2, and I'm a bit lazy with installing things outside of 
Portage) to solve this issue or whether I should just enable a 
workaround for now and keep an eye on future releases for a better 
solution to this problem.

Thanks again,

John

In response to

Responses

pgsql-general by date

Next:From: A. KretschmerDate: 2006-12-27 17:03:29
Subject: Re: Is PostgreSQL for this?
Previous:From: Stuart GrimshawDate: 2006-12-27 16:58:36
Subject: Re: ERROR: could not access status of transaction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group