public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Tom Lane <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Check lateral references within PHVs for memoize cache keys
Date: Sun, 9 Jul 2023 13:11:41 +1200
Message-ID: <CAApHDvpHDuciiK4r9d-aM9Q7BWvM6a98YRZC3kkTAN7q7DCneA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAMbWs48jLxn0pAPZpJ50EThZ569Xrw+=4Ac3QvkpQvNszbeoNg@mail.gmail.com>
<CAMbWs48TGnh8s=9-hJge9Et4Crf9q+_oU4FVekLoX4ommi4CVg@mail.gmail.com>
<CAMbWs4_E2HcTJ0b2srM3hwnQbH5SogkZMM399WUG4gTQSa1FOQ@mail.gmail.com>
<[email protected]>
On Sun, 9 Jul 2023 at 05:28, Tom Lane <[email protected]> wrote:
> More generally, it's not clear to me why we should need to look inside
> lateral PHVs in the first place. Wouldn't the lateral PHV itself
> serve fine as a cache key?
For Memoize specifically, I purposefully made it so the expression was
used as a cache key rather than extracting the Vars from it and using
those. The reason for that was that the expression may result in
fewer distinct values to cache tuples for. For example:
create table t1 (a int primary key);
create table t2 (a int primary key);
create statistics on (a % 10) from t2;
insert into t2 select x from generate_Series(1,1000000)x;
insert into t1 select x from generate_Series(1,1000000)x;
analyze t1,t2;
explain (analyze, costs off) select * from t1 inner join t2 on t1.a=t2.a%10;
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop (actual time=0.015..212.798 rows=900000 loops=1)
-> Seq Scan on t2 (actual time=0.006..33.479 rows=1000000 loops=1)
-> Memoize (actual time=0.000..0.000 rows=1 loops=1000000)
Cache Key: (t2.a % 10)
Cache Mode: logical
Hits: 999990 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using t1_pkey on t1 (actual
time=0.001..0.001 rows=1 loops=10)
Index Cond: (a = (t2.a % 10))
Heap Fetches: 0
Planning Time: 0.928 ms
Execution Time: 229.621 ms
(11 rows)
view thread (9+ 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]
Subject: Re: Check lateral references within PHVs for memoize cache keys
In-Reply-To: <CAApHDvpHDuciiK4r9d-aM9Q7BWvM6a98YRZC3kkTAN7q7DCneA@mail.gmail.com>
* 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