public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Clemens Eisserer <[email protected]>
Cc: [email protected]
Subject: Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan
Date: Wed, 29 Jan 2025 10:10:09 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFvQSYTKG7ifK9D8LMdWbwVOQiUeOeQgtOikRL9TT5S_4we0nQ@mail.gmail.com>
References: <CAFvQSYQNKfhGyacHN3JNMR5jkV0Hf1H91mq3L28Nqm4+sqi4hg@mail.gmail.com>
	<CALdn98CDs6r_hb6pDVudt3FaKhG1k+aUu+KrE+jxkgpHvzQ-ng@mail.gmail.com>
	<CALdn98Bk-FB4Q1GgpVR0vhzFuauap4bK4_rZk5dsb5U6T=Ujcw@mail.gmail.com>
	<CAFvQSYTKG7ifK9D8LMdWbwVOQiUeOeQgtOikRL9TT5S_4we0nQ@mail.gmail.com>

Clemens Eisserer <[email protected]> writes:
> It seems like the check on the stable value of check
> current_setting('my.wfsuser', true)= 'admin' will somehow make the
> index lookup unusesable - but i have no idea why :/

You just haven't thought hard about the difference between AND and OR.

Given
	SELECT ... WHERE per-row-condition AND stable-condition
the planner can legitimately separate out the stable-condition
and test it once in a gating plan level, because if it's false
then no rows need be returned so the table scan need not happen.
Then, with the only qual enforced at the scan level being the
per-row-condition, we're free to use that as an index condition.

But, given
	SELECT ... WHERE per-row-condition OR stable-condition
we can't do much of anything.  If the stable-condition is true
then *all* rows need to be returned, and that basically forces
a seqscan.  An index doesn't help.

The closest thing to what you want that is possible in SQL is

	SELECT ... WHERE per-row-condition
	UNION
	SELECT ... WHERE stable-condition

The planner will not automatically transform your query
to this, mainly because that loses in general.  The UNION
is pretty expensive to do, and it might discard duplicate
rows that the original query would have kept.

If we had an if-then-else kind of plan node, maybe we
could do

	IF stable-condition
	THEN
		SELECT ... WHERE true
	ELSE
		SELECT ... WHERE per-row-condition

where the two subplans would be a seqscan and an indexscan.
But this hasn't come up often enough to motivate anyone
to build such a thing.

In the meantime, you might think about doing the if-then-else
manually on the application side.

			regards, tom lane

view thread (4+ messages)

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