From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Slow performance when querying millions of rows
Date: Wed, 29 Jun 2011 02:10:48 +0200
Message-ID: <4E0A6D88.8010407@fuzzy.cz> (raw)
In-Reply-To: <20110628235050.77a9baf7@mail.openroadsconsulting.com>
References: <20110628235050.77a9baf7@mail.openroadsconsulting.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: Slow performance when querying millions of rows
In-Reply-To: <4E0A6D88.8010407@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox