Re: Identity projection

On Saturday, February 09, 2013 9:03 AM Tom Lane wrote:
> Amit kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> >> if (!is_projection_capable_plan(result_plan) &&
> compare_tlist_exprs(sub_tlist, result_plan->targetlist) )
>
> > Sorry, the check I suggested in last mail should be as below:
>
> > if (!is_projection_capable_plan(result_plan) &&
> !compare_tlist_exprs(sub_tlist, result_plan->targetlist) )
>
> You know, I was thinking that compare_tlist_exprs() was a pretty
> unhelpfully-chosen name for a function returning boolean, and this
> thinko pretty much proves the point. It'd be better to call it
> something like equivalent_tlists(), tlists_are_equivalent(), etc.
> (I'm not caring for the emphasis on the exprs either, because I think
> it'll also be necessary to compare resjunk fields for instance.)

We cannot directly compare expressions in target list as even if expressions
are equal, below node (ex. APPEND) will
not do projection, and hence expr will not be evaluated.
So I have checked if both expr's are Var and have same varattno. I have
included tests of expr and resjunk in equivalent_tlists().
I am not able see the need of comparison for resno and ressortgroupref, so I
have not include the check for them in equivalent_tlists().
Updated patch is attached with this mail.

Conclusion of Test
---------------------
1. The performance improvement is very good (upto 60%) for table with 30
columns
2. The performance improvement is good (upto 36%) even when table contains 2
columns

Configuration
------------------

Os - Suse -11.2
CPU - Intel(R) Xeon(R) L5408 @ 2.13GHz
Ram - 24G

shared_buffers - 4GB

Test Data
-----------
30 Columns
----------
CREATE TABLE tbl_parent (c01 int, c02 numeric, c03 int, c04 int, c05 int,
c06 int, c07 int, c08 int, c09 int, c10 int,
c11 numeric, c12 numeric, c13 numeric, c14 numeric, c15
numeric, c16 numeric, c17 numeric, c18 numeric, c19 numeric, c20 numeric,
c21 int, c22 numeric, c23 int, c24 int, c25 int, c26
int, c27 int, c28 int, c29 int, c30 int);

CREATE TABLE tbl_child () INHERITS(tbl_parent);

INSERT INTO tbl_child (SELECT n, floor(random() * 10000), n+2, n+3, n+4,
n+5, n+6, n+7, n+8, n+9,
n+10, n+11, n+12, n+13, n+14, n+15, n+16, n+17,
n+18, n+19,
n+20, n+21, n+22, n+23, n+24, n+25, n+26, n+27,
n+28, n+29 FROM generate_series(0, 10000000 - 1) n);

EXPLAIN ANALYZE SELECT * FROM tbl_parent;

Original -
9995.1994 ms
Patched -
3817.268 ms (~61%)

10 Columns
--------------
CREATE TABLE tbl_parent (c01 int, c02 numeric, c03 int, c04 int, c05 int,
c06 numeric, c07 numeric, c08 numeric, c09 numeric, c10 numeric);

CREATE TABLE tbl_child () INHERITS(tbl_parent);

INSERT INTO tbl_child (SELECT n, floor(random() * 10000), n+2, n+3, n+4,
n+5, n+6, n+7, n+8, n+9 FROM generate_series(0, 10000000 - 1) n);

EXPLAIN ANALYZE SELECT * FROM tbl_parent;

Original -
6404.2992 ms
Patched -
3374.2356 ms (~47%)

2 Columns
---------
CREATE TABLE tbl_parent (c01 numeric, c02 int);

CREATE TABLE tbl_child () INHERITS(tbl_parent);

INSERT INTO tbl_child (SELECT floor(random() * 10000), n FROM
generate_series(0, 10000000 - 1) n);

EXPLAIN ANALYZE SELECT * FROM tbl_parent;

Original -
4952.5758 ms
Patched -
3162.2286 ms (~36%)

With Regards,
Amit Kapila.