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

From Tom Lane
Subject Re: BUG #17618: unnecessary filter column <> text even after adding index
Date
Msg-id 2619371.1667669000@sss.pgh.pa.us
Whole thread Raw
In response to Re: BUG #17618: unnecessary filter column <> text even after adding index  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
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



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index
Next
From: PG Bug reporting form
Date:
Subject: BUG #17679: REFRESH MATERIALIZED VIEW CONCURRENTLY leaves temporary files?