multicolumn index and setting effective_cache_size using human-readable-numbers

From: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: multicolumn index and setting effective_cache_size using human-readable-numbers
Date: 2016-02-29 13:56:25
Message-ID: CAEzk6fdKJMEB3amHMv+sP5=+TcETaip0GmmCQGt6EZQw1iUXEQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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? Is
there a blowup in the planner checking effective_cache_size value not
expecting the human-readable value?

Thanks for suggestions

Geoff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Mlodgenski 2016-02-29 14:06:57 Re: multicolumn index and setting effective_cache_size using human-readable-numbers
Previous Message Stephen Frost 2016-02-29 13:31:38 Re: Only owners can ANALYZE tables...seems overly restrictive