Sorry, attached patch.
Исходное сообщение
Тема: WIP Patch: Precalculate stable functions
Дата: 20-04-2017 19:56
От: Marina Polyakova
Кому: pgsql-hackers@postgresql.org
Hello everyone!
Now in Postgresql only immutable functions are precalculated; stable
functions are calculated for every row so in fact they don't differ from
volatile functions.
There's a proposal to precalculate stable and immutable functions (=
calculate once for all output rows, but as many times as function is
mentioned in query), if they don't return a set and their arguments are
constants or recursively precalculated functions. The same for
operators' functions, strict functions, tracking functions. It can be
very effective, for example, there's a comparison for full text search
in messages (Intel® Core™ i5-6500 CPU @ 3.20GHz × 4, RAM 8Gb):
Without precalculation:
EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) SELECT COUNT(*) FROM messages WHERE
body_tsvector @@ to_tsquery('postgres');
QUERY
PLAN
--
Aggregate (cost=18714.82..18714.83 rows=1 width=8) (actual
time=2275.334..2275.334 rows=1 loops=1)
Buffers: shared hit=309234 read=184261
-> Bitmap Heap Scan on messages (cost=66.93..18702.34 rows=4991
width=0) (actual time=70.661..224
7.462 rows=151967 loops=1)
Recheck Cond: (body_tsvector @@ to_tsquery('postgres'::text))
Rows Removed by Index Recheck: 118531
Heap Blocks: exact=56726 lossy=33286
Buffers: shared hit=309234 read=184261
-> Bitmap Index Scan on message_body_idx (cost=0.00..65.68
rows=4991 width=0) (actual time=
54.599..54.599 rows=151967 loops=1)
Index Cond: (body_tsvector @@
to_tsquery('postgres'::text))
Buffers: shared hit=1 read=37
Planning time: 0.493 ms
Execution time: 2276.412 ms
(12 rows)
With precalculation:
EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) SELECT COUNT(*) FROM messages WHERE
body_tsvector @@ to_tsquery('postgres');
QUERY
PLAN
--
Aggregate (cost=192269.70..192269.71 rows=1 width=8) (actual
time=1458.679..1458.680 rows=1 loops=1)
Buffers: shared hit=309234 read=184261
-> Bitmap Heap Scan on messages (cost=1445.68..191883.51
rows=154474 width=0) (actual time=70.069
..1433.999 rows=151967 loops=1)
Recheck Cond: (body_tsvector @@ to_tsquery('postgres'::text))
Rows Removed by Index Recheck: 118531
Heap Blocks: exact=56726 lossy=33286
Buffers: shared hit=309234 read=184261
-> Bitmap Index Scan on message_body_idx (cost=0.00..1406.81
rows=154474 width=0) (actual t
ime=56.149..56.149 rows=151967 loops=1)
Index Cond: (body_tsvector @@
to_tsquery('postgres'::text))
Buffers: shared hit=1 read=37
Planning time: 1.644 ms
Execution time: 1459.836 ms
(12 rows)
Patch is attached. It isn't done yet:
- changing documentation (partly because of next lines);
- precalculation of expressions IS DISTINCT FROM and NULLIF which use
nonvolatile equality operators;
- precalculation of expressions "scalar op ANY/ALL (array)" which use
nonvolatile operators;
- precalculation of row compare expressions which use nonvolatile
operators.
--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
+7 926 92 00 265From 46d590281129083d524751805797ef0a3c386df0 Mon Sep 17 00:00:00 2001
From: Marina Polyakova
Date: Thu, 20 Apr 2017 19:23:05 +0300
Subject: [PATCH 2/2] Precalculate stable functions
Now in Postgresql only immutable functions are precalculated; stable functions
are calculated for every row so in fact they don't differ from volatile
functions.
In this patch function / operator is precalculated if:
1) it doesn't return set,
2) it's not volatile itself,
3) its arguments are constants or nonvolatile too (functions or operators).
Costs are changed to reflect the changed behaviour.
---
src/backend/executor/execExpr.c| 41 ++
src/backend/executor/execExprInterp.c | 196 +-
src/backend/optimizer/path/costsize.c | 84 ++-
src/include/fmgr.h | 4 +
src/include/nodes/primnodes.h | 2 +
.../expected/precalculate_stable_functions.out | 784 +
src/test/regress/serial_schedule | 1 +
.../regress/sql/precalculate_stable_functions.sql | 240 +++
8 files changed, 1321 insertions(+), 31 deletions(-)
create mode 100644 src/test/regress/expected/precalculate_stable_functions.out
create mode 100644 src/test/regress/sql/precalculate_stable_functions.sql
diff