executor relation handling - Mailing list pgsql-hackers
From | Amit Langote |
---|---|
Subject | executor relation handling |
Date | |
Msg-id | 468c85d9-540e-66a2-1dde-fec2b741e688@lab.ntt.co.jp Whole thread Raw |
Responses |
Re: executor relation handling
|
List | pgsql-hackers |
This is the continuation of the discussion at: https://www.postgresql.org/message-id/7500.1531920772%40sss.pgh.pa.us Actually, for more background on what I've written below, reading this email in the same discussion would help: https://www.postgresql.org/message-id/4114.1531674142@sss.pgh.pa.us Attached find a series of patches, the first of which tries to implement the main topic discussed in the above email, which is to eliminate execution-time locking of relations in PlannedStmt.rtable. One of the other patches removes the plan node fields that are obsoleted by eliminating execution-time locking of relations. Those fields served no purpose beside telling the executor which relations to lock, or more precisely which relations to lock before initializing the plan tree so that we don't end up upgrading lock strength due to same relation being both a source relation and target relation. When working on that, I noticed that planner fails to remove PlanRowMarks of relations that won't be scanned by a given plan, which results in executor redundantly locking relations that the planner already deemed unnecessary to scan. The locking would be gone with one of the proposed patches, but there are still a couple of overheads during executor initialization of having all those PlanRowMarks. For example, ExecInitLockRows or ExecInitModifyTable calling ExecFindRowMark would end up going over PlanRowMarks that won't ever be used, which especially grows worse with many partitions. Short description of each patch follows: 0001-Don-t-lock-range-table-relations-in-the-executor.patch This removes all instances of heap_open(relid, <not-NoLock>) in the executor code with heap_open(relid, NoLock). To verify that an appropriate lock is already taken on a relation by the time we get into executor, this also installs an assert-build-only check that confirms that lmgr indeed holds the lock. To remember which lock was taken when creating a given RTE_RELATION range table entry, this adds a lockmode field to RangeTblEntry. Finally, because we don't lock in the executor and hence there are no concerns about lock strength upgrade hazard, InitPlan doesn't need toinitialize ResultRelInfos and ExecRowMarks, in favor of doing that in the ExecInit* routines of respective nodes which need those ResultRelInfos and ExecLockRows. (This doesn't touch index relations though, as they don't have a range table entry.) 0002-Remove-useless-fields-from-planner-nodes.patch This removes some fields from PlannedStmt whose only purpose currently is to help InitPlan do certain things that it no longer does, as mentioned above. Also, some fields in Append, MergeAppend, ModifyTable, whose only purpose currently is to propagate partitioned table RT indexes so that executor could lock them, are removed. Removing them also means that the planner doesn't have to spend cycles initializing them. 0003-Prune-PlanRowMark-of-relations-that-are-pruned-from-.patch This prevents PlanRowMarks corresponding to relations that won't be scanned by a given plan from appearing in the rowMarks field of LockRows or ModifyTable nodes. This results in removing significant overhead from the executor initialization of those nodes, especially for partitioned tables with many partitions. 0004-Revise-executor-range-table-relation-opening-closing.patch This adds two arrays to EState indexed by RT indexes, one for RangeTblEntry's and another for Relation pointers. The former reduces the cost of fetching RangeTblEntry by RT index. The latter is used by a newly introduced function ExecRangeTableRelation(), which replaces heap_open for relations that are contained in the range table. If a given RTE's relation is opened by multiple times, only the first call of ExecRangeTableRelation will go to relcache. Although improving executor's performance is not the main goal of these patches, the fact that we're getting rid of redundant processing means there would be at least some speedup, especially with large number of relations in the range table, such as with partitioned tables with many partitions. * Benchmark script used: $ cat /tmp/select-lt.sql select * from lt where b = 999; 'lt' above is a list-partitioned table with 1000 partitions, with one partition for each value in the range 1..1000. $ for i in 1 2; > do > pgbench -n -Mprepared -T 60 -f /tmp/select-lt.sql > done master tps = 768.172129 (excluding connections establishing) tps = 764.180834 (excluding connections establishing) patch 0001 (no locking in the executor) tps = 775.060323 (excluding connections establishing) tps = 778.772917 (excluding connections establishing) patch 0002 (remove useless planner node fields) tps = 782.165436 (excluding connections establishing) tps = 759.417411 (excluding connections establishing patch 0003 (prune PlanRowMarks) tps = 783.558539 (excluding connections establishing) tps = 776.106055 (excluding connections establishing) patch 0004 (executor range table Relation open) tps = 778.924649 (excluding connections establishing) tps = 769.093431 (excluding connections establishing) Speedup is more pronounced with a benchmark that needs RowMarks, because one of the patches (0003) removes overhead around handling them. $ cat /tmp/select-lt-for-share.sql select * from lt where b = 999 for share; master tps = 94.095985 (excluding connections establishing) tps = 93.955702 (excluding connections establishing) patch 0001 (no locking in the executor) tps = 199.030555 (excluding connections establishing) tps = 197.630424 (excluding connections establishing) patch 0002 (remove useless planner node fields) tps = 194.384994 (excluding connections establishing) tps = 195.821362 (excluding connections establishing) patch 0003 (prune PlanRowMarks) tps = 712.544029 (excluding connections establishing) tps = 717.540052 (excluding connections establishing) patch 0004 (executor range table Relation open) tps = 725.189715 (excluding connections establishing) tps = 727.344683 (excluding connections establishing) Will add this to next CF. Thanks, Amit
Attachment
pgsql-hackers by date: