Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On Mon, Feb 29, 2016 at 3:02 PM, Geoff Winkless wrote: > > Perhaps I'm not being clear. Index 1 has field a and is used in the join no > matter how small I set effective_cache_size (even 32mb). Index 2 has fields > a,b but will not be used at ecs of 3gb, 6gb, whatever up til

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 Feb 2016 22:47, "Kevin Grittner" wrote: > > On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless wrote: > > > I'm not really sure what changes I could make that would make one > > index that's ostensibly equivalent to the other not be attractive to > >

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless wrote: > I'm not really sure what changes I could make that would make one > index that's ostensibly equivalent to the other not be attractive to > the planner though. I can mess with those figures but as I said before > the

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 February 2016 at 18:31, Joshua D. Drake wrote: > I haven't been following this thread but did you try looking at the costs? Thanks for the response... > #seq_page_cost = 1.0# measured on an arbitrary scale > #random_page_cost = 4.0

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 02/29/2016 10:05 AM, Geoff Winkless wrote: Just as a continuation of this, I can set effective_cache_size to 64MB and it will still use the single-column index, but PG flatly refuses to use the multicolumn index without effective_cache_size being an unfeasibly large number (2x the RAM in the

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

Just as a continuation of this, I can set effective_cache_size to 64MB and it will still use the single-column index, but PG flatly refuses to use the multicolumn index without effective_cache_size being an unfeasibly large number (2x the RAM in the machine, in this case). Geoff -- Sent via

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 February 2016 at 14:07, Geoff Winkless wrote: > On 29 February 2016 at 14:06, Jim Mlodgenski wrote: >> No they are not the same. When you don't include a unit for >> effective_cache_size, it defaults to page size so you're saying 2146435072 * >> 8K >

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 February 2016 at 14:06, Jim Mlodgenski wrote: > No they are not the same. When you don't include a unit for > effective_cache_size, it defaults to page size so you're saying 2146435072 * > 8K Hah. Thanks Jim, like I said I was sure I'd be missing something :) Geoff

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On Mon, Feb 29, 2016 at 8:56 AM, Geoff Winkless wrote: > I'm sure I'm missing something here. > > A query takes 50 seconds; it's doing a seq-scan on a joined table, > even though the table is joined via a field that's the leftmost column > in a multicolumn index >

[GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

I'm sure I'm missing something here. A query takes 50 seconds; it's doing a seq-scan on a joined table, even though the table is joined via a field that's the leftmost column in a multicolumn index (http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html says "equality constraints on