Skip to content

Commit d69d45a

Browse files
david-rowleyDavid Rowley
david-rowley
and
David Rowley
committed
Speedup child EquivalenceMember lookup in planner
When planning queries to partitioned tables, we clone all EquivalenceMembers belonging to the partitioned table into em_is_child EquivalenceMembers for each non-pruned partition. For partitioned tables with large numbers of partitions, this meant the ec_members list could become large and code searching that list would become slow. Effectively, the more partitions which were present, the more searches needed to be performed for operations such as find_ec_member_matching_expr() during create_plan() and the more partitions present, the longer these searches would take, i.e., a quadratic slowdown. To fix this, here we adjust how we store EquivalenceMembers for em_is_child members. Instead of storing these directly in ec_members, these are now stored in a new array of Lists in the EquivalenceClass, which is indexed by the relid. When we want to find EquivalenceMembers belonging to a certain child relation, we can narrow the search to the array element for that relation. To make EquivalenceMember lookup easier and to reduce the amount of code change, this commit provides a pair of functions to allow iteration over the EquivalenceMembers of an EC which also handles finding the child members, if required. Callers that never need to look at child members can remain using the foreach loop over ec_members, which will now often be faster due to only parent-level members being stored there. The actual performance increases here are highly dependent on the number of partitions and the query being planned. Performance increases can be visible with as few as 8 partitions, but the speedup is marginal for such low numbers of partitions. The speedups become much more visible with a few dozen to hundreds of partitions. With some tested queries using 56 partitions, the planner was around 3x faster than before. For use cases with thousands of partitions, these are likely to become significantly faster. Some testing has shown planner speedups of 60x or more with 8192 partitions. Author: Yuya Watari <watari.yuya@gmail.com> Co-authored-by: David Rowley <dgrowleyml@gmail.com> Reviewed-by: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Andrey Lepikhov <a.lepikhov@postgrespro.ru> Reviewed-by: Alena Rybakina <lena.ribackina@yandex.ru> Reviewed-by: Dmitry Dolgov <9erthalion6@gmail.com> Reviewed-by: Amit Langote <amitlangote09@gmail.com> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Tested-by: Thom Brown <thom@linux.com> Tested-by: newtglobal postgresql_contributors <postgresql_contributors@newtglobalcorp.com> Discussion: https://postgr.es/m/CAJ2pMkZNCgoUKSE%2B_5LthD%2BKbXKvq6h2hQN8Esxpxd%2Bcxmgomg%40mail.gmail.com
1 parent 105b2cb commit d69d45a

File tree

9 files changed

+446
-120
lines changed

9 files changed

+446
-120
lines changed

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -7847,14 +7847,13 @@ conversion_error_callback(void *arg)
78477847
EquivalenceMember *
78487848
find_em_for_rel(PlannerInfo *root, EquivalenceClass *ec, RelOptInfo *rel)
78497849
{
7850-
ListCell *lc;
7851-
78527850
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
7851+
EquivalenceMemberIterator it;
7852+
EquivalenceMember *em;
78537853

7854-
foreach(lc, ec->ec_members)
7854+
setup_eclass_member_iterator(&it, ec, rel->relids);
7855+
while ((em = eclass_member_iterator_next(&it)) != NULL)
78557856
{
7856-
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
7857-
78587857
/*
78597858
* Note we require !bms_is_empty, else we'd accept constant
78607859
* expressions which are not suitable for the purpose.
@@ -7908,7 +7907,10 @@ find_em_for_rel_target(PlannerInfo *root, EquivalenceClass *ec,
79087907
while (expr && IsA(expr, RelabelType))
79097908
expr = ((RelabelType *) expr)->arg;
79107909

7911-
/* Locate an EquivalenceClass member matching this expr, if any */
7910+
/*
7911+
* Locate an EquivalenceClass member matching this expr, if any.
7912+
* Ignore child members.
7913+
*/
79127914
foreach(lc2, ec->ec_members)
79137915
{
79147916
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
@@ -7918,9 +7920,8 @@ find_em_for_rel_target(PlannerInfo *root, EquivalenceClass *ec,
79187920
if (em->em_is_const)
79197921
continue;
79207922

7921-
/* Ignore child members */
7922-
if (em->em_is_child)
7923-
continue;
7923+
/* Child members should not exist in ec_members */
7924+
Assert(!em->em_is_child);
79247925

79257926
/* Match if same expression (after stripping relabel) */
79267927
em_expr = em->em_expr;

‎src/backend/nodes/outfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -465,7 +465,9 @@ _outEquivalenceClass(StringInfo str, const EquivalenceClass *node)
465465

466466
WRITE_NODE_FIELD(ec_opfamilies);
467467
WRITE_OID_FIELD(ec_collation);
468+
WRITE_INT_FIELD(ec_childmembers_size);
468469
WRITE_NODE_FIELD(ec_members);
470+
WRITE_NODE_ARRAY(ec_childmembers, node->ec_childmembers_size);
469471
WRITE_NODE_FIELD(ec_sources);
470472
/* Only ec_derives_list is written; hash is not serialized. */
471473
WRITE_NODE_FIELD(ec_derives_list);

0 commit comments

Comments
 (0)