From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
cc: sindysenorita@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding
 index
In-reply-to: <946195.1663718159@sss.pgh.pa.us>
References: <17618-7a2240bfaa7e84ae@postgresql.org>
 <349136.1663601052@sss.pgh.pa.us>
 <CAMkU=1wqvsMwnQTrQ0zoRo2AkYhuEY-RuzpaXrvz7iNu38CVqg@mail.gmail.com>
 <739953.1663701274@sss.pgh.pa.us> <946195.1663718159@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 20 Sep 2022 19:55:59 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2619370.1667669000.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 05 Nov 2022 13:23:20 -0400
Message-ID: <2619371.1667669000@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2619371.1667669000%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
>> I wonder if that isn't backwards, ie we should prefer to put duplicates
>> in bitmapqualorig (the recheck condition) instead of qpqual (the filter=
).
>> If my head is screwed on correctly today, that should allow us to skip
>> checking the condition much of the time, and the skip would be safe
>> if the index is correctly asserting that no recheck is needed.

> Flipping the removal around has the effect I expected on the plan shape,
> but some of the regression test queries now give the wrong answer, so
> there's something faulty about that analysis.

BTW, after looking more closely I see my mistake.  An example of the
sort of plan that fails with that change is

  Sort
    Sort Key: proname
    ->  Bitmap Heap Scan on pg_proc
-         Filter: (proname ~~ 'RI\_FKey%del'::text)
+         Recheck Cond: (proname ~~ 'RI\_FKey%del'::text)
          ->  Bitmap Index Scan on pg_proc_proname_args_nsp_index
                Index Cond: ((proname >=3D 'RI_FKey'::text) AND (proname <=
 'RI_FKez'::text))
 (6 rows)

The difficulty here is pretty obvious: the original clause is stricter
than the index conditions generated from it.  So even if the index
enforces the index conditions exactly, we still need to check the
original clause, and so it can't be relegated to the recheck field.
To improve this, we'd need to track which elements of bitmapqualorig
correspond exactly to index conditions, which we don't do ATM.

			regards, tom lane