Re: [HACKERS] Partition-wise aggregation/grouping

Поиск

Список

Период

Сортировка

От	Rafia Sabih
Тема	Re: [HACKERS] Partition-wise aggregation/grouping
Дата	27 февраля 2018 г. 12:32:24
Msg-id	CAOGQiiPysxtT6spykX7UewDg11amFeFG_H+6MGVpJq=oV+oUPw@mail.gmail.com обсуждение исходный текст
Ответ на	Re: [HACKERS] Partition-wise aggregation/grouping (Jeevan Chalke <jeevan.chalke@enterprisedb.com>)
Список	pgsql-hackers

Дерево обсуждения

On Wed, Feb 14, 2018 at 8:35 PM, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:

On Wed, Feb 14, 2018 at 12:17 PM, Rafia Sabih <rafia.sabih@enterprisedb.com> wrote:
On Tue, Feb 13, 2018 at 6:21 PM, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:

I see that partition-wise aggregate plan too uses parallel index, am I missing something?

You're right, I missed that, oops.

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.

Yeah strange it is.

Off-list I have asked Rafia to provide me the perf machine access where she is doing this bench-marking to see what's going wrong.
Thanks Rafia for the details.

What I have observed that, there are two sources, one with HEAD and other with HEAD+PWA. However the configuration switches were different. Sources with HEAD+PWA has CFLAGS="-ggdb3 -O0" CXXFLAGS="-ggdb3 -O0" flags in addition with other sources. i.e. HEAD+PWA is configured with debugging/optimization enabled which account for the slowness.

I have run EXPLAIN for these three queries on both the sources having exactly same configuration switches and I don't find any slowness with PWA patch-set.

Thus, it will be good if you re-run the benchmark by keeping configuration switches same on both the sources and share the results.

Thanks

Interesting. I checked with keeping configure flags same for both the repos,

on head:

CONFIGURE = '--enable-cassert' 'CFLAGS=-ggdb3 -O0' 'CXXFLAGS=-ggdb3 -O0' 'prefix=/data/rafia.sabih/pg_head/install/'

On head+PWA:

CONFIGURE = '--enable-cassert' 'CFLAGS=-ggdb3 -O0' 'CXXFLAGS=-ggdb3 -O0' 'prefix=/data/rafia.sabih/pg_part_pa/install/'

The queries I previously reported are now performing same as on head with the above mentioned configuration. However, I further experimented with partitionwise_join set to true, and found following cases of regression,

Q17 was taking some 1400 secs on head but with PWA it's taking some 1600 secs, looks like append of scan+aggregates is coming to be costlier than that of just scan.

Q20 took 470 secs on head and with PWA it's taking 630 secs, the execution plan is changed a lot, one thing in particular with the patch is not using parallel bitmap heap scan on lineitem table.

The experimental settings were kept same as before with the change of partitionwise_join = 1.

Please find the attached zip for the explain analyse outputs.

However, do you see similar behaviour with patches applied, "enable_partition_wise_agg = on" and "enable_partition_wise_agg = off" ?

I tried that for query 18, with patch and enable_partition_wise_agg = off, query completes in some 270 secs. You may find the explain analyse output for it in the attached file. I noticed that on head the query plan had parallel hash join however with patch and no partition-wise agg it is using nested loop joins. This might be the issue.

Also, does rest of the queries perform better with partition-wise aggregates?

As far as this setting goes, there wasn't any other query using partition-wise-agg, so, no.

BTW, just an FYI, this experiment is on scale factor 20.

--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/

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