From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-performance@postgresql.org
Subject: Re: Gained %20 performance after disabling bitmapscan
Date: Sat, 29 Dec 2018 13:11:16 -0700 (MST)
Message-ID: <1546114276294-0.post@n3.nabble.com> (raw)
In-Reply-To: <AM6PR07MB520725A8D1B1287AE313928FD7F90@AM6PR07MB5207.eurprd07.prod.outlook.com>
References: <AM6PR07MB520725A8D1B1287AE313928FD7F90@AM6PR07MB5207.eurprd07.prod.outlook.com>
changing parameters can have surprising effects. fyi, I tried disabling
bitmapscan and running the 113 queries (iirc) of the Join Order Benchmark
against them. Several improved. Here the 'Baseline' is the best previously
known plan, and 'Baseline+1' is the plan with enable_bitmapscan = false.
Notably:
NOTICE: Baseline [Planning time 90.349 ms, Execution time 12531.577
ms]
NOTICE: Baseline+1 [Planning time 81.473 ms, Execution time 7646.242
ms]
NOTICE: Total time benefit: 4894.211 ms, Execution time benefit:
4885.335 ms
shaved 4.9s off a 12.5s query, and:
NOTICE: Baseline [Planning time 198.983 ms, Execution time 2715.75
ms]
NOTICE: Baseline+1 [Planning time 183.204 ms, Execution time 1395.494
ms]
NOTICE: Total time benefit: 1336.035 ms, Execution time benefit:
1320.256 ms
gained nicely in percentage terms, and:
NOTICE: Baseline [Planning time 91.527 ms, Execution time 12480.151
ms]
NOTICE: Baseline+1 [Planning time 84.192 ms, Execution time 7918.974
ms]
NOTICE: Total time benefit: 4568.512 ms, Execution time benefit:
4561.177 ms
also had a nice 4.5s+ improvement, among other plan diffs.
This just shows that when you inject a new planning constraint into a
workload, at least some of the plans will probably get faster. In this case
a few of them got significantly faster either in absolute terms or in
percentage terms. Unsurprisingly, the great majority got slower.
/Jim
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jfinnert@amazon.com
Subject: Re: Gained %20 performance after disabling bitmapscan
In-Reply-To: <1546114276294-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox