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
