Re: Problem with index in OR'd expression

postgresql(dot)org(at)tgice(dot)com writes:
> 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.

My mistake, I was thinking of Table.IndexedCol IS NULL.

> 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?

No, and there's no "confusion" about it: with that WHERE clause, the
plan might have to return every row in the table. The index is useless.

Since you know that the two OR'd conditions are mutually exclusive,
perhaps you could transform the query into a UNION operation, as in this
example:

regression=# prepare foo(int) as select * from tenk1 where $1 is null union all select * from tenk1 where unique2 = $1;
PREPARE
regression=# explain analyze execute foo(42);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..561.02 rows=10001 width=244) (actual time=0.169..0.201 rows=1 loops=1)
-> Result (cost=0.00..458.00 rows=10000 width=244) (actual time=0.012..0.012 rows=0 loops=1)
One-Time Filter: ($1 IS NULL)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) (never executed)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..3.01 rows=1 width=244) (actual time=0.142..0.166 rows=1 loops=1)
Index Cond: (unique2 = $1)
Total runtime: 1.092 ms
(7 rows)

Because of the one-time filter, the seqscan isn't executed unless
needed.

However I'm not sure that this sort of approach scales up if you have
more than one of these conditions in a query ...

regards, tom lane