multicolumn index and setting effective_cache_size using human-readable-numbers

Поиск
Список
Период
Сортировка
От Geoff Winkless
Тема multicolumn index and setting effective_cache_size using human-readable-numbers
Дата
Msg-id CAEzk6fdKJMEB3amHMv+sP5=+TcETaip0GmmCQGt6EZQw1iUXEQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: multicolumn index and setting effective_cache_size using human-readable-numbers  (Jim Mlodgenski <jimmy76@gmail.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Stephen Frost
Дата:
Сообщение: Re: Only owners can ANALYZE tables...seems overly restrictive
Следующее
От: Jim Mlodgenski
Дата:
Сообщение: Re: multicolumn index and setting effective_cache_size using human-readable-numbers