Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan

Clemens Eisserer <linuxhippy(at)gmail(dot)com> writes:
> It seems like the check on the stable value of check
> current_setting('my.wfsuser', true)= 'admin' will somehow make the
> index lookup unusesable - but i have no idea why :/

You just haven't thought hard about the difference between AND and OR.

Given
SELECT ... WHERE per-row-condition AND stable-condition
the planner can legitimately separate out the stable-condition
and test it once in a gating plan level, because if it's false
then no rows need be returned so the table scan need not happen.
Then, with the only qual enforced at the scan level being the
per-row-condition, we're free to use that as an index condition.

But, given
SELECT ... WHERE per-row-condition OR stable-condition
we can't do much of anything. If the stable-condition is true
then *all* rows need to be returned, and that basically forces
a seqscan. An index doesn't help.

The closest thing to what you want that is possible in SQL is

SELECT ... WHERE per-row-condition
UNION
SELECT ... WHERE stable-condition

The planner will not automatically transform your query
to this, mainly because that loses in general. The UNION
is pretty expensive to do, and it might discard duplicate
rows that the original query would have kept.

If we had an if-then-else kind of plan node, maybe we
could do

IF stable-condition
THEN
SELECT ... WHERE true
ELSE
SELECT ... WHERE per-row-condition

where the two subplans would be a seqscan and an indexscan.
But this hasn't come up often enough to motivate anyone
to build such a thing.

In the meantime, you might think about doing the if-then-else
manually on the application side.

regards, tom lane