Re: WIP: avoiding tuple construction/deconstruction overhead - Mailing list pgsql-patches

From a_ogawa
Subject Re: WIP: avoiding tuple construction/deconstruction overhead
Date
Msg-id PIEMIKOOMKNIJLLLBCBBEEHJCFAA.a_ogawa@hi-ho.ne.jp
Whole thread Raw
In response to Re: WIP: avoiding tuple construction/deconstruction overhead  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: WIP: avoiding tuple construction/deconstruction overhead  (Bruce Momjian <pgman@candle.pha.pa.us>)
List pgsql-patches
Tom Lane wrote:
> a_ogawa <a_ogawa@hi-ho.ne.jp> writes:
> > (1)We can improve compare_heap() by using TableTupleSlot instead of
> > HeapTuple. Please see attached patch.
>
> Did you measure any performance improvement from that?  I considered it
> but thought it would likely be a wash or a loss, because in most cases
> only one attribute will be pulled from a tuple during comparetup_heap.
> slot_getattr cannot improve on heap_getattr in that case, and is quite
> likely to be slower.

I measured performance of heap_getattr and slot_getattr in
comparetup_heap.

I made the table which had ten varchar attributes, and registered
data for tests.  (Attached file includes SQL doing this.)
I carried out the following tests.

(case 1)
 test1: select * from sort_test order by v1 limit 100;
 test2: select * from sort_test order by v1, v2 limit 100;
 test3: select * from sort_test order by v1, v2, v3 limit 100;
 test4: select * from sort_test order by v1, v2, v3, v4 limit 100;
 test5: select * from sort_test order by v1, v2, v3, v4, v5 limit 100;

 result:        test1    test2    test3    test4    test5
-----------------------------------------------------------------------
 heap_getattr  2.149s   2.602s   3.204s   3.830s   4.159s
 slot_getattr  2.523s   3.422s   3.977s   4.453s   4.721s

(case 2)
 test1: select * from sort_test order by v10 limit 100;
 test2: select * from sort_test order by v10, v9 limit 100;
 test3: select * from sort_test order by v10, v9, v8 limit 100;
 test4: select * from sort_test order by v10, v9, v8, v7 limit 100;
 test5: select * from sort_test order by v10, v9, v8, v7, v6 limit 100;

 result:        test1    test2    test3    test4    test5
-----------------------------------------------------------------------
 heap_getattr  3.654s   5.549s   6.575s   7.367s   7.870s
 slot_getattr  4.027s   4.930s   5.249s   5.555s   5.756s

(case 3)
 test1: select * from sort_test order by v5 limit 100;
 test2: select * from sort_test order by v5, v6 limit 100;
 test3: select * from sort_test order by v5, v6, v7 limit 100;
 test4: select * from sort_test order by v5, v6, v7, v8 limit 100;
 test5: select * from sort_test order by v5, v6, v7, v8, v9 limit 100;

 result:        test1    test2    test3    test4    test5
-----------------------------------------------------------------------
 heap_getattr  2.657s   4.207s   5.194s   6.179s  6.662s
 slot_getattr  3.126s   4.233s   4.806s   5.271s  5.557s

In most cases, heap_getattr is fast.
When the following conditions occurred, slot_getattr is fast.
 (1)Tuple have varlen attributes.
 (2)Sort key have more than two attributes.
 (3)A position of a sort key is far from the head of tuple.
 (4)As for the data of a sort key, there be many repetition.
Actually it will be rare that these conditions are occurred.

Thinking from a result, I think that we had better continue using
heap_getattr in comparetup_heap.

regards,

--- Atsushi Ogawa

Attachment

pgsql-patches by date:

Previous
From: Cosimo Streppone
Date:
Subject: pg_autovacuum micro patch to display database name when ANALYZEing or VACUUMing tables
Next
From: Bruce Momjian
Date:
Subject: Re: WIP: avoiding tuple construction/deconstruction overhead