MIME-Version: 1.0
From: Clemens Eisserer <linuxhippy@gmail.com>
Date: Tue, 28 Jan 2025 15:58:08 +0100
Message-ID: 
 <CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4+sqi4hg@mail.gmail.com>
Subject: 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"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4%2Bsqi4hg%40mail.gmail.com>
Precedence: bulk

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