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
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 |