Re: BUG #17618: unnecessary filter column <> text even after adding index - Mailing list pgsql-bugs

From Richard Guo
Subject Re: BUG #17618: unnecessary filter column <> text even after adding index
Date
Msg-id CAMbWs4-o-VLk3gXqz7W7ZS384S5mvW5noB8f_BKzU_c77DBjXg@mail.gmail.com
Whole thread Raw
In response to Re: BUG #17618: unnecessary filter column <> text even after adding index  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BUG #17618: unnecessary filter column <> text even after adding index
List pgsql-bugs

On Wed, Sep 21, 2022 at 7:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
 
I think we may have a minor mistake when constructing the qpqual list in
create_bitmap_scan_plan. The qpqual list is supposed to be scan_clauses
minus indexquals. So we check each scan clause to see if it is redundant
with any indexqual, by using equal, checking EC or using
predicate_implied_by.

Note that the indexqual here may not be the form that has been going
through constant folding. Such as in this case with a boolean index, the
indexqual would be converted to 'indexkey expression = TRUE' by
match_boolean_index_clause. And that may make us fail to tell the scan
clause is redundant.

The comment of predicate_implied_by() says

 * The top-level List structure of each list corresponds to an AND list.
 * We assume that eval_const_expressions() has been applied and so there
 * are no un-flattened ANDs or ORs (e.g., no AND immediately within an AND,
 * including AND just below the top-level List structure).

So I think we need to run eval_const_expressions on indexquals before we
check for duplicate clauses, something like attached.

Thanks
Richard
Attachment

pgsql-bugs by date:

Previous
From: Andres Freund
Date:
Subject: Re: BUG #17620: JIT-Compiler causes Segmentation Fault
Next
From: Tom Lane
Date:
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index