Re: Slow performance when querying millions of rows

Dne 29.6.2011 01:50, Craig McIlwee napsal(a):
>> > work_mem: 512MB
>> > shared_buffers: 64MB, 512MB, and 1024MB, each yielded the same query
>> > plan and took the same amount of time to execute give or take a few
>> > seconds
>>
>> shared_buffers doesn't normally impact the query plan; it impacts how
>> much churn there is between the database and the operating system cache,
>> mainly important for making write-heavy work efficient. On Windows,
>> you'll probably be safe to set this to 512MB and forget about it. It
>> doesn't benefit from large values anyway.
>
> I was thinking that shared buffers controlled the amount of data,
> primarily table and index pages, that the database could store in memory
> at once. Based on that assumption, I thought that a larger value would
> enable an entire table + index to be in memory together and speed up the
> query. Am I wrong?

Well, you're right and wrong at the same time. The shared buffers really
controls the amount of data that may be read into the database cache,
that's true. But this value is not used when building the execution
plan. There's another value (effective_cache_size) that is used when
planning a query.

>> > Ive read documentation that says I should be able to set statistics
>> > values for an entire table as opposed to per column, but havent found
>> > how to do that. I guess I was either too lazy to update statistics on
>> > each column or just didnt think it would help much.

Link to the docs? According to

http://www.postgresql.org/docs/current/static/sql-altertable.html

it's possible to set this only at the column level. And of course
there's a GUC default_statistics_target that defines default value.

Tomas