From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: sindysenorita@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index
Date: Sat, 05 Nov 2022 13:23:20 -0400
Message-ID: <2619371.1667669000@sss.pgh.pa.us> (raw)
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>
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 >= '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
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: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeff.janes@gmail.com, sindysenorita@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index
In-Reply-To: <2619371.1667669000@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox