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

On 02/29/2016 10:05 AM, Geoff Winkless wrote:
> Just as a continuation of this, I can set effective_cache_size to 64MB
> and it will still use the single-column index, but PG flatly refuses
> to use the multicolumn index without effective_cache_size being an
> unfeasibly large number (2x the RAM in the machine, in this case).

I haven't been following this thread but did you try looking at the costs?

#seq_page_cost = 1.0                    # measured on an arbitrary scale
#random_page_cost = 4.0                 # same scale as above
#cpu_tuple_cost = 0.01                  # same scale as above
#cpu_index_tuple_cost = 0.005           # same scale as above
#cpu_operator_cost = 0.0025             # same scale as above
#effective_cache_size = 128MB

Especially seq_page_cost, random_page_cost and cpu_index_tuple_cost?

JD

>
> Geoff
>
>


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.