Re: Improving executor performance

On 25 June 2016 05:00, Andres Freund Wrote:
>To: pgsql-hackers(at)postgresql(dot)org
>Subject: [HACKERS] Improving executor performance
>
>My observations about the performance bottlenecks I found, and partially
>addressed, are in rough order of importance (there's interdependence
>between most of them):
>
>1) Cache misses cost us a lot, doing more predictable accesses can make
> a huge difference. Without addressing that, many other bottlenecks
> don't matter all that much. I see *significant* performance
> improvements for large seqscans (when the results are used) simply
> memcpy()'ing the current target block.
>
> This partially is an intrinsic problem of analyzing a lot of data,
> and partially because our access patterns are bad.
>
>
>2) Baring 1) tuple deforming is the biggest bottleneck in nearly all
> queries I looked at. There's various places we trigger deforming,
> most ending in either slot_deform_tuple(), heap_getattr(),
> heap_deform_tuple().

Agreed,
I had also observed similar behavior specifically (2) while working on improving executor performance.
I had done some prototype work on this to improve performance by using native compilation.
Details of same is available at my blog:
http://rajeevrastogi.blogspot.in/2016/03/native-compilation-part-2-pgday-asia.html

>3) Our 1-by-1 tuple flow in the executor has two major issues:

Agreed, In order to tackle this IMHO, we should
1. Makes the processing data-centric instead of operator centric.
2. Instead of pulling each tuple from immediate operator, operator can push the tuple to its parent. It can be allowed to push until it sees any operator, which cannot be processed without result from other operator.
More details from another thread:
https://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB77159A9B904@szxeml521-mbs.china.huawei.com

Thanks and Regards,
Kumar Rajeev Rastogi