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

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 <linuxhippy(at)gmail(dot)com>
Date: Tuesday, January 28, 2025 at 6:58 AM
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)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