public inbox for [email protected]  

help / color / mirror / Atom feed

From: Wetmore, Matthew  (CTR) <[email protected]>
To: Clemens Eisserer <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
Date: Tue, 28 Jan 2025 15:34:47 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4+sqi4hg@mail.gmail.com>
References: <CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4+sqi4hg@mail.gmail.com>

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 <[email protected]>
Date: Tuesday, January 28, 2025 at 6:58 AM
To: "[email protected]" <[email protected]>
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






Attachments:

  [image/png] image001.png (8.1K, ../[email protected]/3-image001.png)
  download | view image

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox