Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Peter Geoghegan
On Mon, Jun 19, 2017 at 4:51 PM, Peter Geoghegan wrote: > This would make only the first lookup for each distinct value on the > outer side actually do an index scan on the inner side. I can imagine > the optimization saving certain queries from consuming a lot of memory >

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Peter Geoghegan
On Mon, Jun 19, 2017 at 4:35 PM, Andres Freund wrote: >> I think that this is the way index scan prefetch is normally >> implemented. Index scans will on average have a much more random >> access pattern than what is typical for bitmap heap scans, making this >> optimization

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Andres Freund
On 2017-06-19 15:21:20 -0700, Peter Geoghegan wrote: > On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > > Unfortunately, it is only implemented in very narrow circumstances. You > > have to be doing bitmap index scans of many widely scattered rows to make it > > useful.

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Peter Geoghegan
On Mon, Jun 19, 2017 at 3:25 PM, Alvaro Herrera wrote: > Surely you could prefetch all the heap pages pointed to by index items > in the current leaf index page ... I'm sure that you could do that too. I'm not sure how valuable each prefetching optimization is. I can

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Alvaro Herrera
Peter Geoghegan wrote: > On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > > Unfortunately, it is only implemented in very narrow circumstances. You > > have to be doing bitmap index scans of many widely scattered rows to make it > > useful. I don't think that this is

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Peter Geoghegan
On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > Unfortunately, it is only implemented in very narrow circumstances. You > have to be doing bitmap index scans of many widely scattered rows to make it > useful. I don't think that this is all that common of a situation.

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Bruce Momjian
On Mon, Jun 19, 2017 at 10:49:59AM -0500, Merlin Moncure wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > > If you have a RAID, set it to the number of spindles in your RAID and forget > > it. It is usually one of the less interesting knobs to play with.

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Jeff Janes
On Mon, Jun 19, 2017 at 8:49 AM, Merlin Moncure wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > > If you have a RAID, set it to the number of spindles in your RAID and > forget > > it. It is usually one of the less interesting knobs to

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Merlin Moncure
On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > If you have a RAID, set it to the number of spindles in your RAID and forget > it. It is usually one of the less interesting knobs to play with. (Unless > your usage pattern of the database is unusual and exact fits the

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Jeff Janes
On Sun, Jun 18, 2017 at 7:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sun, Jun 18, 2017 at 6:02 PM, Patrick B > wrote: > >> Hi guys. >> >> I just wanna understand the effective_io_concurrency value better. >> >> My current Master database server

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 Thread Andreas Kretschmer
Am 19.06.2017 um 03:02 schrieb Patrick B: Hi guys. I just wanna understand the effective_io_concurrency value better. My current Master database server has 16 vCPUS and I use effective_io_concurrency = 0. What can be the benefits of increasing that number? Also, do you guys have any

Re: [GENERAL] effective_io_concurrency increasing

2017-06-18 Thread David G. Johnston
On Sun, Jun 18, 2017 at 6:02 PM, Patrick B wrote: > Hi guys. > > I just wanna understand the effective_io_concurrency value better. > > My current Master database server has 16 vCPUS and I use > ​​ > effective_io_concurrency = 0. > ​It seems as though the number of

Re: [GENERAL] effective_io_concurrency increasing

2017-06-18 Thread Melvin Davidson
*As per the docs:* *1. This is dependent on whether or not you are using a RAID disk,2. "Some experimentation may be needed to find the best value"* *IOW, there is no general recommendation.* On Sun, Jun 18, 2017 at 9:24 PM, Patrick B wrote: > > > 2017-06-19 13:19

Re: [GENERAL] effective_io_concurrency increasing

2017-06-18 Thread Patrick B
2017-06-19 13:19 GMT+12:00 Melvin Davidson : > > > On Sun, Jun 18, 2017 at 9:02 PM, Patrick B > wrote: > >> Hi guys. >> >> I just wanna understand the effective_io_concurrency value better. >> >> My current Master database server has 16 vCPUS and I

Re: [GENERAL] effective_io_concurrency increasing

2017-06-18 Thread Melvin Davidson
On Sun, Jun 18, 2017 at 9:02 PM, Patrick B wrote: > Hi guys. > > I just wanna understand the effective_io_concurrency value better. > > My current Master database server has 16 vCPUS and I > use effective_io_concurrency = 0. > > What can be the benefits of increasing

[GENERAL] effective_io_concurrency increasing

2017-06-18 Thread Patrick B
Hi guys. I just wanna understand the effective_io_concurrency value better. My current Master database server has 16 vCPUS and I use effective_io_concurrency = 0. What can be the benefits of increasing that number? Also, do you guys have any recommendations? I'm using PG 9.2 and the official