Optimizing nested ConvertRowtypeExpr execution

From: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Optimizing nested ConvertRowtypeExpr execution
Date: 2018-02-26 11:50:05
Message-ID: CAFjFpReDR9EGJ18dcEjz5eey+5o-NHvZSTRZa7BbZcrjtmi8gA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,
In a multi-level partitioned table, a parent whole-row reference gets
translated into nested ConvertRowtypeExpr with child whole-row
reference as the leaf. During the execution, the child whole-row
reference gets translated into all all intermediate parents' whole-row
references, ultimately represented as parent's whole-row reference.
AFAIU, the intermediate translations are unnecessary. The leaf child
whole-row can be directly translated into top parent's whole-row
reference. Here's a WIP patch which does that by eliminating
intermediate ConvertRowtypeExprs during ExecInitExprRec().

I tested the performance with two-level partitions, and 1M rows, on my
laptop selecting just the whole-row expression. I saw ~20% improvement
in the execution time. Please see the attached test and its output
with and without patch.

For two-level inheritance hierarchy, this patch doesn't show any
performance difference since the plan time hierarchy is flattened into
single level.

Instead of the approach that the patch takes, we might modify
adjust_appendrel_attrs() not to produce nested ConvertRowtypeExprs in
the first place. With that we may get rid of code which handles nested
ConvertRowtypeExprs like is_converted_whole_row_reference(). But I
haven't tried that approach yet.

Suggestions/comments welcome.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Attachment Content-Type Size
pg_nest_cre.patch text/x-patch 1.5 KB
nest_cre.out application/octet-stream 5.6 KB
nest_cre.out.patch text/x-patch 7.2 KB
nest_cre.out.wo_patch application/octet-stream 7.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2018-02-26 11:50:31 Re: [HACKERS] Add support for tuple routing to foreign partitions
Previous Message Jeevan Chalke 2018-02-26 11:38:30 Re: [HACKERS] Partition-wise aggregation/grouping