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

From: Clemens Eisserer <linuxhippy(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
Date: 2025-01-28 14:58:08
Message-ID: CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4+sqi4hg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Wetmore, Matthew (CTR) 2025-01-28 15:34:47 Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
Previous Message Wong, Kam Fook (TR Technology) 2025-01-27 17:41:51 RE: [EXT] Re: Read-only connectios optimizatios