MIME-Version: 1.0
References: 
 <CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4+sqi4hg@mail.gmail.com>
 <CALdn98CDs6r_hb6pDVudt3FaKhG1k+aUu+KrE+jxkgpHvzQ-ng@mail.gmail.com>
 <CALdn98Bk-FB4Q1GgpVR0vhzFuauap4bK4_rZk5dsb5U6T=Ujcw@mail.gmail.com>
In-Reply-To: 
 <CALdn98Bk-FB4Q1GgpVR0vhzFuauap4bK4_rZk5dsb5U6T=Ujcw@mail.gmail.com>
From: Clemens Eisserer <linuxhippy@gmail.com>
Date: Wed, 29 Jan 2025 13:07:54 +0100
Message-ID: 
 <CAFvQSYTKG7ifK9D8LMdWbwVOQiUeOeQgtOikRL9TT5S_4we0nQ@mail.gmail.com>
Subject: Re: Why ORing with a false one-time filter turns an Index-Lookup into
 a SeqScan
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAFvQSYTKG7ifK9D8LMdWbwVOQiUeOeQgtOikRL9TT5S_4we0nQ%40mail.gmail.com>
Precedence: bulk

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)=3D
'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 =3D 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)=3D '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
<daniel.blanch@hoplasoftware.com>:
>
> Better said:
>
> current_setting() is STABLE
>
> If a function is IMMUTABLE it is run only once per statement and it can b=
e run during optimization phase, before it's planned or executed.
> If a function is STABLE it is run only once but after planning, during ex=
ecution.
> 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 plpgsq=
l that returns all records if user is admin or returns just one record othe=
rwise.
>
> Cheers.
>
>
> El mar, 28 ene 2025 a las 23:26, Daniel Blanch Bataller (<daniel.blanch@h=
oplasoftware.com>) escribi=C3=B3:
>>
>> 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 alw=
ays returns the same result when given the same argument values; that is, i=
t does not do database lookups or otherwise use information not directly pr=
esent in its argument list. If this option is given, any call of the functi=
on with all-constant arguments can be immediately replaced with the functio=
n 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 state=
ments. 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 modifi=
ed by the current command.) Also note that the current_timestamp family of =
functions qualify as stable, since their values do not change within a tran=
saction.
>>
>> VOLATILE indicates that the function value can change even within a sing=
le table scan, so no optimizations can be made. Relatively few database fun=
ctions are volatile in this sense; some examples are random(), currval(), t=
imeofday(). But note that any function that has side-effects must be classi=
fied volatile, even if its result is quite predictable, to prevent calls fr=
om being optimized away; an example is setval().
>>
>>
>> random() for example is volatile. That is if you call to random() in a s=
elect, random is evaluated per each row.
>>
>>
>> test=3D# 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=3D# SELECT proname, provolatile
>> FROM pg_proc
>> WHERE proname =3D '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 IMMUTAB=
LE
>>
>> test=3D# 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=3D# SET my.username =3D 'admin';
>> SET
>> test=3D# EXPLAIN SELECT * FROM test WHERE id =3D 1 OR myfunction() =3D '=
admin'; -- predicate is always true, all rows are evaluated
>>                        QUERY PLAN
>> ---------------------------------------------------------
>> Seq Scan on test  (cost=3D0.00..21.00 rows=3D1000 width=3D19)
>> (1 fila)
>>
>> test=3D# EXPLAIN SELECT * FROM test WHERE id =3D 1 OR myfunction() =3D '=
other'; -- predicate is true only when id is 1
>>                               QUERY PLAN
>> -----------------------------------------------------------------------
>>  Index Scan using test_pkey on test  (cost=3D0.28..8.29 rows=3D1 width=
=3D19)
>>    Index Cond: (id =3D 1)
>> (2 filas)
>> (1 fila)
>>
>>
>> I hope this helps.
>>
>>
>> El mar, 28 ene 2025 a las 15:58, Clemens Eisserer (<linuxhippy@gmail.com=
>) escribi=C3=B3:
>>>
>>> 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 =3D current_setting('my.wfsuser', tru=
e);
>>> Bitmap Heap Scan on mytable  (cost=3D43.92..12523.30 rows=3D3548
>>> width=3D2341) (actual time=3D0.032..0.033 rows=3D0 loops=3D1)
>>>   Recheck Cond: ((owner)::text =3D current_setting('my.wfsuser'::text, =
true))
>>>   ->  Bitmap Index Scan on mytable_owner_idx  (cost=3D0.00..43.04
>>> rows=3D3548 width=3D0) (actual time=3D0.029..0.030 rows=3D0 loops=3D1)
>>>         Index Cond: ((owner)::text =3D current_setting('my.wfsuser'::te=
xt, true))
>>> Planning Time: 0.221 ms
>>> Execution Time: 0.094 ms
>>>
>>> 2.) also a static condition resulting in a false value is correctly rec=
ognized:
>>> select * from mytable  where current_setting('my.wfsuser'::text, true)
>>> =3D 'admin'::text;
>>> Result  (cost=3D0.01..158384.05 rows=3D709504 width=3D2341) (actual
>>> time=3D0.008..0.009 rows=3D0 loops=3D1)
>>>   One-Time Filter: (current_setting('my.wfsuser'::text, true) =3D 'admi=
n'::text)
>>>   ->  Seq Scan on mytable  (cost=3D0.01..158384.05 rows=3D709504
>>> width=3D2341) (never executed)
>>> Planning Time: 0.163 ms
>>> Execution Time: 0.068 ms
>>>
>>> 3.) Yet when both filters are combined with OR, postgresql executes a S=
eqScan:
>>> select * from mytable where owner =3D current_setting('my.wfsuser',
>>> true) OR current_setting('my.wfsuser'::text, true) =3D 'admin'::text;
>>> Gather  (cost=3D1000.00..158909.23 rows=3D7077 width=3D2341) (actual
>>> time=3D2783.728..2786.520 rows=3D0 loops=3D1)
>>>   Workers Planned: 2
>>>   Workers Launched: 2
>>>   ->  Parallel Seq Scan on mytable  (cost=3D0.00..157201.53 rows=3D2949
>>> width=3D2341) (actual time=3D2744.147..2744.147 rows=3D0 loops=3D3)
>>>         Filter: (((owner)::text =3D current_setting('my.wfsuser'::text,
>>> true)) OR (current_setting('my.wfsuser'::text, true) =3D 'admin'::text)=
)
>>>         Rows Removed by Filter: 236501
>>> Planning Time: 0.217 ms
>>> Execution Time: 2786.575 ms
>>>
>>> Thanks and best regards, Clemens
>>>
>>>