Hi,
On 2020-07-28 14:07:48 -0700, Andres Freund wrote:
> (I'm rebasing my tree that tries to reduce the overhead / allow caching
> / increase efficiency to current PG, but it's a fair bit of work)
FWIW, I created a demo workload for this, and repro'ed the issue with
that. Those improvements does make a very significant difference:
CREATE FUNCTION exec(text) returns text language plpgsql volatile
AS $f$
BEGIN
EXECUTE $1;
RETURN $1;
END;
$f$;
CREATE TABLE manypa(category text not null, data text not null) PARTITION BY LIST(category);
SELECT exec('CREATE TABLE manypa_'||g.i||' PARTITION OF manypa FOR VALUES IN('||g.i||')') FROM generate_series(1, 1000)
g(i);
INSERT INTO manypa(category, data) VALUES('1', '1');
EXPLAIN ANALYZE SELECT * FROM manypa WHERE data <> '17' and data <> '15' and data <> '13' AND data <> '11' AND data <>
'9'AND data <> '7' AND data <> '5' AND data <> '3' AND data <> '1';
Before:
Timing: Generation 335.345 ms, Inlining 51.025 ms, Optimization 11967.776 ms, Emission 9201.499 ms, Total 21555.645
ms
IR size: unoptimized: 9022868 bytes, optimized: 6206368 bytes
After:
Timing: Generation 261.283 ms, Inlining 30.875 ms, Optimization 1671.969 ms, Emission 18.557 ms, Total 1982.683 ms
IR size: unoptimized 8776100 bytes, optimized 115868 bytes
That obviously needs to be improved further, but it's already a lot
better. In particular after these changes the generated code could be
cached.
One thing that could make a huge difference here is to be able to
determine whether two expressions and/or tlists are equivalent
cheaply... I know that David has some need for that too.
Greetings,
Andres Freund
