public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Ranier Vilela <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: jian he <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Nikolay Shaplov <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Peter Geoghegan <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Mon, 27 Jan 2025 15:52:16 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdvehQbCpQG9zYr739y2nXQkE=FYsat5VE-pDoB4RDPfBw@mail.gmail.com>
References: <[email protected]>
	<CAPpHfduzBgV3AecMU0jFqOSjK9iP86HiHEzj2Hv6hLqWu7JJFQ@mail.gmail.com>
	<CAPpHfduqOuvh6i=jYDPWnAUg325hsOLUAW9r_awdirQRA7uzHA@mail.gmail.com>
	<[email protected]>
	<CAPpHfdtPkA3XGM+FOanXfFNm45YEMkuqpfL0ZSL7uOP1bHPPEg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
	<[email protected]>
	<CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
	<[email protected]>
	<CAPpHfdtgcye9ELaLHw4ZOUxWQ9t1P=oBDJX8KwwOHqasESdXQQ@mail.gmail.com>
	<CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAPpHfdvehQbCpQG9zYr739y2nXQkE=FYsat5VE-pDoB4RDPfBw@mail.gmail.com>

On 1/25/25 12:04, Alexander Korotkov wrote:
> On Wed, Jan 15, 2025 at 10:24 AM Andrei Lepikhov <[email protected]> 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

view thread (38+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: POC, WIP: OR-clause support for indexes
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox