Re: Gained %20 performance after disabling bitmapscan

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