Re: POC, WIP: OR-clause support for indexes

Hi!

On 07.03.2024 17:51, Alexander Korotkov wrote:
> Hi!
>
> On Tue, Mar 5, 2024 at 9:59 AM Andrei Lepikhov 
> <a.lepikhov@postgrespro.ru> wrote:
> > On 5/3/2024 12:30, Andrei Lepikhov wrote:
> > > On 4/3/2024 09:26, jian he wrote:
> > ... and the new version of the patchset is attached.
>
> I made some revisions for the patchset.
> 1) Use hash_combine() to combine hash values.
> 2) Upper limit the number of array elements by MAX_SAOP_ARRAY_SIZE.
> 3) Better save the original order of clauses by putting hash entries 
> and untransformable clauses to the same list.  A lot of differences in 
> regression tests output have gone.
Thank you for your changes. I agree with them.
>
> One important issue I found.
>
> # create table t as (select i::int%100 i from generate_series(1,10000) i);
> # analyze t;
> # explain select * from t where i = 1 or i = 1;
>                      QUERY PLAN
> -----------------------------------------------------
>  Seq Scan on t  (cost=0.00..189.00 rows=200 width=4)
>    Filter: (i = ANY ('{1,1}'::integer[]))
> (2 rows)
>
> # set enable_or_transformation = false;
> SET
> # explain select * from t where i = 1 or i = 1;
>                      QUERY PLAN
> -----------------------------------------------------
>  Seq Scan on t  (cost=0.00..189.00 rows=100 width=4)
>    Filter: (i = 1)
> (2 rows)
>
> We don't make array values unique.  That might make query execution 
> performance somewhat worse, and also makes selectivity estimation 
> worse.  I suggest Andrei and/or Alena should implement making array 
> values unique.
>
>
I have corrected this and some spelling mistakes. The 
unique_any_elements_change.no-cfbot file contains changes.

While I was correcting the test results caused by such changes, I 
noticed that the same behavior was when converting the IN expression,
and this can be seen in the result of the regression test:

  EXPLAIN (COSTS OFF)
  SELECT unique2 FROM onek2
  WHERE stringu1 IN ('A', 'A') AND (stringu1 = 'A' OR stringu1 = 'A');
                                 QUERY PLAN
---------------------------------------------------------------------------
   Bitmap Heap Scan on onek2
     Recheck Cond: (stringu1 < 'B'::name)
    Filter: ((stringu1 = ANY ('{A,A}'::name[])) AND (stringu1 = 'A'::name))
     ->  Bitmap Index Scan on onek2_u2_prtl
  (4 rows)

-- 
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company