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 <linuxhi...@gmail.com>
Date: Tuesday, January 28, 2025 at 6:58 AM
To: "pgsql-performa...@postgresql.org" <pgsql-performa...@postgresql.org>
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
