Re: POC, WIP: OR-clause support for indexes - Mailing list pgsql-hackers

On 1/25/25 12:04, Alexander Korotkov wrote:
> On Wed, Jan 15, 2025 at 10:24 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> causes SEGFAULT during index keys evaluation. I haven't dived into it
>> yet, but it seems quite a typical misstep and is not difficult to fix.
> 
> Segfault appears to be caused by a typo.  Patch used parent rinfo
> instead of child rinfo.  Fixed in the attached patch.
Great!
> 
> It appears that your first query also changed a plan after fixing
> this.  Could you, please, provide another example of a regression for
> short-circuit optimization, which is related to this patch?
Yes, it may be caused by the current lazy InitPlan evaluation strategy, 
which would only happen if it was really needed.

Examples:
---------

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT * FROM bitmap_split_or t1
WHERE t1.a=2 AND (t1.b=2 OR t1.b = (
    SELECT avg(x) FROM generate_series(1,1e6) AS x)::integer);

without optimisation:

  Index Scan using t_a_b_idx on bitmap_split_or t1 (actual rows=1 loops=1)
    Index Cond: (a = 2)
    Filter: ((b = 2) OR (b = ((InitPlan 1).col1)::integer))
    InitPlan 1
      ->  Aggregate (never executed)
            ->  Function Scan on generate_series x (never executed)
  Planning Time: 0.564 ms
  Execution Time: 0.182 ms

But having it as a part of an array, we forcedly evaluate it for (not 
100% sure) more precise selectivity estimation:

  Index Scan using t_a_b_idx on bitmap_split_or t1
    (actual rows=1 loops=1)
    Index Cond: ((a = 2) AND
                 (b = ANY (ARRAY[2, ((InitPlan 1).col1)::integer])))
    InitPlan 1
      ->  Aggregate (actual rows=1 loops=1)
            ->  Function Scan on generate_series x
                (actual rows=1000000 loops=1)
  Planning Time: 0.927 ms
  Execution Time: 489.933 ms

This also means that if, before the patch, we executed a query 
successfully, after applying the patch, we sometimes may get the error:
'ERROR: more than one row returned by a subquery used as an expression' 
because of early InitPlan evaluation. See the example below:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM bitmap_split_or t1
WHERE t1.a=2 AND (t1.b=2 OR t1.b = (
    SELECT random() FROM generate_series(1,1e6) AS x)::integer);

  Index Scan using t_a_b_idx on bitmap_split_or t1
    Index Cond: ((a = 2) AND (b = ANY (ARRAY[2, ((InitPlan 
1).col1)::integer])))
    InitPlan 1
      ->  Function Scan on generate_series x

I think optimisation should have never happened and this is another 
issue, isn't it?

-- 
regards, Andrei Lepikhov