Re: [HACKERS] Partition-wise aggregation/grouping

Поиск
Список
Период
Сортировка
От Jeevan Chalke
Тема Re: [HACKERS] Partition-wise aggregation/grouping
Дата
Msg-id CAM2+6=VKVby_9PD+ePqy7Po_6-+uhOik-oHwTJefZXe1VKAtfg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] Partition-wise aggregation/grouping  (Rafia Sabih <rafia.sabih@enterprisedb.com>)
Ответы Re: [HACKERS] Partition-wise aggregation/grouping
Список pgsql-hackers
Дерево обсуждения


On Tue, Feb 13, 2018 at 12:37 PM, Rafia Sabih <rafia.sabih@enterprisedb.com> wrote:
 
I was testing this patch for TPC-H benchmarking and came across following results,

Thanks Rafia for testing this with TPC-H benchmarking.
 

Q1 completes in 229 secs with patch and in 66 secs without it. It looks like with this patch the time of parallel seq scan itself is elevated for some of the partitions. Notice for partitions, lineitem_3, lineitem_7, lineitem_10, and linietem_5 it is some 13 secs which was somewhere around 5 secs on head.

Q6 completes in some 7 secs with patch and it takes 4 secs without it. This is mainly caused because with the new parallel append, the parallel operator below it (parallel index scan in this case) is not used, however, on head it was the append of all the parallel index scans, which was saving quite some time.
 
I see that partition-wise aggregate plan too uses parallel index, am I missing something?
 

Q18 takes some 390 secs with patch and some 147 secs without it. 

This looks strange. This patch set does not touch parallel or seq scan as such. I am not sure why this is happening. All these three queries explain plan shows much higher execution time for parallel/seq scan.

However, do you see similar behaviour with patches applied, "enable_partition_wise_agg = on" and "enable_partition_wise_agg = off" ?
 
Also, does rest of the queries perform better with partition-wise aggregates?
 

The experimental setup for these tests is as follows,
work_mem = 500MB
shared_buffers = 10GB
effective_cache_size = 4GB
seq_page_cost = random+page_cost = 0.01
enable_partition_wise_join = off

Partitioning info:
Total 10 partitions on tables - lineitem and orders each with partitioning key being l_orderkey and o_orderkey respectively.

Please find the attached file for explain analyse outputs of each of the reported query.
--
Regards,
Rafia Sabih



--
Jeevan Chalke
Technical Architect, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

В списке pgsql-hackers по дате отправления: