On Mon, Feb 29, 2016 at 8:56 AM, Geoff Winkless <pgsqlad...@geoff.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
