pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedchange the default value of enable_bitmapscan to off
3+ messages / 3 participants
[nested] [flat]
* change the default value of enable_bitmapscan to off
@ 2023-01-14 14:51 hehaochen@hotmail.com <hehaochen@hotmail.com>
2023-01-14 15:00 ` Re: change the default value of enable_bitmapscan to off Pavel Stehule <pavel.stehule@gmail.com>
2023-01-14 15:57 ` Re: change the default value of enable_bitmapscan to off Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: hehaochen@hotmail.com @ 2023-01-14 14:51 UTC (permalink / raw)
To: pgsql-performance
The default is enable_bitmapscan on. However, TPC-H.query17 get slower running on my NVMe SSD (WD SN850) after switching on the parameter: latency drop from 9secs to 16secs. During a B-tree Index Scan, bitmapscan optimization converts random I/O into sequential. However, many users use SSDs rather than HDDs. But they may not know the trick. Is there a possibility that can change the default value to off?
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: change the default value of enable_bitmapscan to off
2023-01-14 14:51 change the default value of enable_bitmapscan to off hehaochen@hotmail.com <hehaochen@hotmail.com>
@ 2023-01-14 15:00 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pavel Stehule @ 2023-01-14 15:00 UTC (permalink / raw)
To: hehaochen@hotmail.com <hehaochen@hotmail.com>; +Cc: pgsql-performance
Hi
so 14. 1. 2023 v 15:51 odesÃlatel hehaochen@hotmail.com <
hehaochen@hotmail.com> napsal:
> The default is enable_bitmapscan on. However, TPC-H.query17 get slower
> running on my NVMe SSD (WD SN850) after switching on the parameter: latency
> drop from 9secs to 16secs. During a B-tree Index Scan, bitmapscan
> optimization converts random I/O into sequential. However, many users use
> SSDs rather than HDDs. But they may not know the trick. Is there a
> possibility that can change the default value to off?
>
I don't think it can be disabled by default.
When you have fast SSD disk, then common setting is decreasing
random_page_cost to some value to 2 or maybe 1.5
Regards
Pavel
>
> Thanks!
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: change the default value of enable_bitmapscan to off
2023-01-14 14:51 change the default value of enable_bitmapscan to off hehaochen@hotmail.com <hehaochen@hotmail.com>
@ 2023-01-14 15:57 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-01-14 15:57 UTC (permalink / raw)
To: hehaochen@hotmail.com <hehaochen@hotmail.com>; +Cc: pgsql-performance
"hehaochen@hotmail.com" <hehaochen@hotmail.com> writes:
> The default is enable_bitmapscan on. However, TPC-H.query17 get slower running on my NVMe SSD (WD SN850) after switching on the parameter: latency drop from 9secs to 16secs. During a B-tree Index Scan, bitmapscan optimization converts random I/O into sequential. However, many users use SSDs rather than HDDs. But they may not know the trick. Is there a possibility that can change the default value to off?
Use ALTER SYSTEM SET, or edit postgresql.conf:
https://www.postgresql.org/docs/current/config-setting.html
Note that changing planner parameters on the basis of a single
query getting slower is a classic beginner error. You need
to think about the totality of the installation's workload.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-01-14 15:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-14 14:51 change the default value of enable_bitmapscan to off hehaochen@hotmail.com <hehaochen@hotmail.com>
2023-01-14 15:00 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-01-14 15:57 ` Tom Lane <tgl@sss.pgh.pa.us>
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