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

From: Clemens Eisserer <[email-masked]>
Date: Tue, 28 Jan 2025 15:58:08 +0100

Hello,

Any idea what could cause postgresql (16.0) to fall back to a SeqScan
when ORing a falsy one-time filter to a selection which would
otherwise use an index scan?

1.) Without the false one-time condition, the query uses the existing
index on owner to perform the lookup:
select * from mytable where owner = current_setting('my.wfsuser', true);
Bitmap Heap Scan on mytable  (cost=43.92..12523.30 rows=3548
width=2341) (actual time=0.032..0.033 rows=0 loops=1)
  Recheck Cond: ((owner)::text = current_setting('my.wfsuser'::text, true))
  ->  Bitmap Index Scan on mytable_owner_idx  (cost=0.00..43.04
rows=3548 width=0) (actual time=0.029..0.030 rows=0 loops=1)
        Index Cond: ((owner)::text = current_setting('my.wfsuser'::text, true))
Planning Time: 0.221 ms
Execution Time: 0.094 ms

2.) also a static condition resulting in a false value is correctly recognized:
select * from mytable  where current_setting('my.wfsuser'::text, true)
= 'admin'::text;
Result  (cost=0.01..158384.05 rows=709504 width=2341) (actual
time=0.008..0.009 rows=0 loops=1)
  One-Time Filter: (current_setting('my.wfsuser'::text, true) = 'admin'::text)
  ->  Seq Scan on mytable  (cost=0.01..158384.05 rows=709504
width=2341) (never executed)
Planning Time: 0.163 ms
Execution Time: 0.068 ms

3.) Yet when both filters are combined with OR, postgresql executes a SeqScan:
select * from mytable where owner = current_setting('my.wfsuser',
true) OR current_setting('my.wfsuser'::text, true) = 'admin'::text;
Gather  (cost=1000.00..158909.23 rows=7077 width=2341) (actual
time=2783.728..2786.520 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on mytable  (cost=0.00..157201.53 rows=2949
width=2341) (actual time=2744.147..2744.147 rows=0 loops=3)
        Filter: (((owner)::text = current_setting('my.wfsuser'::text,
true)) OR (current_setting('my.wfsuser'::text, true) = 'admin'::text))
        Rows Removed by Filter: 236501
Planning Time: 0.217 ms
Execution Time: 2786.575 ms

Thanks and best regards, Clemens

---

From: "Wetmore, Matthew (CTR)" <[email-masked]>
Date: Tue, 28 Jan 2025 15:34:47 +0000

Not an official postgres reply, I’m just some guy…

You added to the OR to ::text.

It would have to do a SEQ scan to perform a search ::text on the column, as it doesn’t know the column’s ::text value(s).

I think if you make another index on the column::text, the SEQ scan would be an index scan, but text indexing isn’t fast, so maybe the SEQ scan is still faster.  But it will improve the query run time.

The query planner will pick the fastest way, if a SEQ scan is faster, don’t get hung up on that, but create the ::text index to improve the run time.

Just my $0.02

--
Matt Wetmore
Data Engineer
Braze Certified Architect
415.416.9738

[signature_1095211592]

From: Clemens Eisserer <[email-masked]>
Date: Tuesday, January 28, 2025 at 6:58 AM
To: "[email-masked]" <[email-masked]>
Subject: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan

Hello, Any idea what could cause postgresql (16. 0) to fall back to a SeqScan when ORing a falsy one-time filter to a selection which would otherwise use an index scan? 1. ) Without the false one-time condition, the query uses the existing index

Hello,

Planning Time: 0.221 ms

Execution Time: 0.094 ms

= 'admin'::text;

Planning Time: 0.163 ms

Execution Time: 0.068 ms

  Workers Planned: 2

  Workers Launched: 2

Planning Time: 0.217 ms

---

From: Clemens Eisserer <[email-masked]>
Date: Wed, 29 Jan 2025 13:07:54 +0100

Hi Daniel,

Thanks a lot for the detailed analysis and the suggestions, however I
am not sure marking the proeprty read as immuble is safe - because I
intend to use it as STABLE function - constant during execution of one
statement but might return different values in different
statement-invokations.

What puzzles me is that without ORing both conditions, postgres
behaves as expected.
When just filtering with WHERE current_setting('my.wfsuser', true)=
'admin' it correctly detects this is a static condition, and either
reads all rows or omits reading the rows alltogether.
for filtering just WHERE owner = current_setting('my.wfsuser', true)
it treats the current_setting invokation as stable and does a normal
index lookup just taking a few ms.
but when combining both conditions with an OR everything seems to fall
appart, and instead of an index lookup / seqscan I always get the
(slow) seqscan.

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 :/

Best regards, Clemens

Am Mi., 29. Jan. 2025 um 08:43 Uhr schrieb Daniel Blanch Bataller
<[email-masked]>:
>
> Better said:
>
> current_setting() is STABLE
>
> If a function is IMMUTABLE it is run only once per statement and it can be run during optimization phase, before it's planned or executed.
> If a function is STABLE it is run only once but after planning, during execution.
> if a function is VOLATILE it is run for every row during executiong time.
>
> Said so i would use a function for what you want, A function using plpgsql that returns all records if user is admin or returns just one record otherwise.
>
> Cheers.
>
>
> El mar, 28 ene 2025 a las 23:26, Daniel Blanch Bataller (<[email-masked]>) escribió:
>>
>> I hope this gives you a clue of what it's going on:
>>
>> Functions can be marked as
>> INMUTABLE
>> STABLE
>> VOLATILE
>>
>> IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.
>>
>> STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.
>>
>> VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().
>>
>>
>> random() for example is volatile. That is if you call to random() in a select, random is evaluated per each row.
>>
>>
>> test=# SELECT random() FROM generate_series(1, 3);
>>        random
>> ---------------------
>>  0.13773282234297923
>>   0.5954521821166239
>>  0.23865666511706607
>> (3 filas)
>>
>> Random is marked as volatile so it is executed for every row.
>>
>> test=# SELECT proname, provolatile
>> FROM pg_proc
>> WHERE proname = 'random';
>>  proname | provolatile
>> ---------+-------------
>>  random  | v
>>
>>
>> To make your function run only once, IMMUTABLE function type seems to be the type of that does the trick. (
>>
>> You can wrap current_setting in your own function and mark it as IMMUTABLE
>>
>> test=# CREATE OR REPLACE FUNCTION myfunction() RETURNS TEXT IMMUTABLE LANGUAGE sql AS $$ SELECT current_setting('my.username') $$;
>> CREATE FUNCTION
>>
>> Now if you use myfunction() it behaves as expected;
>>
>>
>> test=# SET my.username = 'admin';
>> SET
>> test=# EXPLAIN SELECT * FROM test WHERE id = 1 OR myfunction() = 'admin'; -- predicate is always true, all rows are evaluated
>>                        QUERY PLAN
>> ---------------------------------------------------------
>> Seq Scan on test  (cost=0.00..21.00 rows=1000 width=19)
>> (1 fila)
>>
>> test=# EXPLAIN SELECT * FROM test WHERE id = 1 OR myfunction() = 'other'; -- predicate is true only when id is 1
>>                               QUERY PLAN
>> -----------------------------------------------------------------------
>>  Index Scan using test_pkey on test  (cost=0.28..8.29 rows=1 width=19)
>>    Index Cond: (id = 1)
>> (2 filas)
>> (1 fila)
>>
>>
>> I hope this helps.
>>
>>
>> El mar, 28 ene 2025 a las 15:58, Clemens Eisserer (<[email-masked]>) escribió:
>>>
>>> Hello,
>>>

---

From: Tom Lane <[email-masked]>
Date: Wed, 29 Jan 2025 10:10:09 -0500

> 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