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

On Mon, Feb 29, 2016 at 8:56 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> 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
> (http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html
> says "equality constraints on leading columns ... will be used to
> limit the portion of the index that is scanned")
>
> http://explain.depesz.com/s/suv
>
> If I create an individual index on just the linked key, the explain
> shows the index being used and the query takes 1.7s.
>
> http://explain.depesz.com/s/b9ZS
>
> Now here's the odd bit:
>
> SET effective_cache_size TO '2146435072'
>
> causes the index to be used.
>
> SET effective_cache_size TO '2047MB'
>
> causes it to use tablescan. Shouldn't those two be equivalent?

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

> Is
> there a blowup in the planner checking effective_cache_size value not
> expecting the human-readable value?
>
> Thanks for suggestions
>
> Geoff
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>