public inbox for [email protected]
help / color / mirror / Atom feedWhy ORing with a false one-time filter turns an Index-Lookup into a SeqScan
4+ messages / 3 participants
[nested] [flat]
* Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
@ 2025-01-28 14:58 Clemens Eisserer <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Clemens Eisserer @ 2025-01-28 14:58 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
@ 2025-01-28 15:34 Wetmore, Matthew (CTR) <[email protected]>
parent: Clemens Eisserer <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Wetmore, Matthew (CTR) @ 2025-01-28 15:34 UTC (permalink / raw)
To: Clemens Eisserer <[email protected]>; pgsql-performance
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 protected]>
Date: Tuesday, January 28, 2025 at 6:58 AM
To: "[email protected]" <[email protected]>
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,
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
Attachments:
[image/png] image001.png (8.1K, ../../[email protected]/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
@ 2025-01-29 12:07 Clemens Eisserer <[email protected]>
parent: Clemens Eisserer <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Clemens Eisserer @ 2025-01-29 12:07 UTC (permalink / raw)
To: pgsql-performance
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 protected]>:
>
> 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 protected]>) 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 protected]>) escribió:
>>>
>>> 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
>>>
>>>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
@ 2025-01-29 15:10 Tom Lane <[email protected]>
parent: Clemens Eisserer <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-01-29 15:10 UTC (permalink / raw)
To: Clemens Eisserer <[email protected]>; +Cc: pgsql-performance
Clemens Eisserer <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-01-29 15:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-28 14:58 Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan Clemens Eisserer <[email protected]>
2025-01-28 15:34 ` Wetmore, Matthew (CTR) <[email protected]>
2025-01-29 12:07 ` Clemens Eisserer <[email protected]>
2025-01-29 15:10 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox