Identity projection

Hello.

A very simple query shown below on partitioned tables can run for
four times as long as that on non-partitioned tables when the
whole data can be loaded onto memory.

* QUERY : SELECT * FROM t;
* EXEC TREE : Result(Append(SeqScan)) for partitioned
: SeqScan for non-partitioned

One of the cause seems to be the Result node attached over an
Append node does projection regardless of the necessity.

When executing the query for non-partitioned tables, the top node
receives the tuples in relation heap. Meanwhile, for paritioned
one, the Result node executes projection which is redundant.

This patch reduces run time of such queries by 45% when result
recored has 30 columns and seems to have no harm for performance.
You can see the details of performance measurment later in this
message.

=====
Discussion underlying this patch.

As far as I saw, identity projection can be done by returning
econtext->ecxt_outertuple in ExecProject(). node->ps.ps_projInfo
== NULL also can indicate to do so but I preferred to capsulate
them into ExecProject() rather than scattering codes outside.

# 'identity projection' here means that the projected tuple
# contains all orignal attributes in original order.

The next thing to think is when to do so.

ExecBuildProjectionInfo() which controls direct mapping of tuple
slot had been seemed suitable to do that. But I couldn't find
apropriate condition for judging properly whether the lower tuple
slot can be thrown up to the upper node.

Then, I found set_upper_references() seems suitable. The types
and positions of tlist attributes can be examined in quite clear
way there.

More aggressively, Result node could be pruned for
non-projection-capable plans when it is an identity projection.
But set_upper_references() is called far after here so the
projection is unidentified as identity projection or not at the
moment.

Pros and Cons.

The most important advantage of this patch is significant
reduction of runtime for some part of queries returns many
columns for partitioned tables, and union ops.

The most poor point of this patch, I suppose, might be adding
similar examination for tlists, say, exam for direct mapping in
set_upper_references().. This may be implemented smarter than
this patch.

Additionally to that, two steps of copying the flag
tlist_lower_cogruent from Plan node to PlanState node and finally
onto ProjectionInfo seems somewhat ugly..

=====
Patch summary.

This patch introduces 'identity projection' in the process below.

1. set_upper_references() judges the 'congruency' (is this right
wording?) between subplan->targetlist and plan->tagetlist. And
stores the result in plan->tlist_lower_congruent.

2. ExecAssignProjectionInfo() passes tlist_lower_congruent to
ExecBuildProjectionInfo() and the ProjectionInfo created is
marked as 'identity' according to the handed
tlist_lower_congruent. I suppose ExecAssignProjectionInfo()
may be called with tlist_lower_congruent == true only from
ExecInitResult(), ExecInitGroup() and ExecInitAgg().

3. ExecProject() does 'identity projection' if needed.

======
Performance measurement details.

The effect of this patch becomes greater as more columns exists.
The reduction of execution time was up to 45% for select * for a
partitioned table which has 30 columns, and 14% for single
column.

Exec time for non-partitioned (child) table seemes not to be
influenced by this patch.

The detail of the result follows. The result figures are the
averages for ten times run.

OS CentOS6.3
CPU Inter Core i7 965 @ 3.2GHz
Memory 6GB

shared_buffers=2GB # No block read occurred on select.

### TEST FOR RESULT NODE
# CREATE TABLE parent (c01 int, c02 numeric,... cxx int);
# CREATE TABLE child () INHERITS(parent);
# INSERT INTO child (SELECT n, floor(random() * 10000),..
# FROM generate_series(0, 10000000 - 1) n);

30 columns
EXPLAIN ANALYZE SELECT * FROM parent;
original: 4868 ms
patched : 2691 ms (-45%)

EXPLAIN ANALYZE SELECT * FROM child;
original: 1252 ms
patched : 1125 ms (-10%)

15 columns
EXPLAIN ANALYZE SELECT * FROM parent;
original: 3785 ms
patched : 2685 ms (-29%)

EXPLAIN ANALYZE SELECT * FROM child0;
original: 1108 ms
patched : 1091 ms (-1.5%)

2 columns
EXPLAIN ANALYZE SELECT * FROM parent;
original: 3785 ms
patched : 2560 ms (-32%)

EXPLAIN ANALYZE SELECT * FROM child;
original: 1108 ms
patched : 973 ms (-12%)

1 column
EXPLAIN ANALYZE SELECT * FROM parent;
original: 2998 ms
patched : 2593 ms (-14%)

EXPLAIN ANALYZE SELECT * FROM child;
original: 1141 ms
patched : 969 ms (-15%)

### TEST FOR GROUP NODE
# CREATE TABLE tbl (c01 int, c02 numeric, c03 int);
# INSERT INTO tbl (SELECT n, floor(random() * 10000),..
# FROM generate_series(0, 10000000 - 1) n);

3 columns
EXPLAIN ANALYZE SELECT * FROM tbl GROUP BY c01, c02, c03;
original: 860 ms
patched : 775 ms (-9.9%)

Negative check - additional time to process identity check on planning.

# CREATE OR REPLACE FUNCTION test () RETURNS int AS $BODY$
DECLARE
r int;
BEGIN
r := 0;
LOOP
PERFORM * FROM parenta LIMIT 1;
r := r + 1;
EXIT WHEN r > 100000;
END LOOP;
RETURN 1;
END;
$BODY$ LANGUAGE plpgsql;

EXPLAIN ANALYZE SELECT test()
original: 2695 ms
patched : 2622 ms (-3%)

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center