Re: Why should such a simple query over indexed columns be so slow?

On Mon, Jan 30, 2012 at 2:39 PM, Alessandro Gagliardi
<alessandro(at)path(dot)com> wrote:
> On Mon, Jan 30, 2012 at 1:25 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>
>> You can do "SHOW random_page_cost" yourself right now, too.
>>
> 4
>
> I also tried "SHOW seq_page_cost" and that's 1.
>
> Looking
> at http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-RANDOM-PAGE-COST
> I wonder if I should try reducing random_page_cost?
>
> Something that might help when it comes to advice on performance tuning is
> that this database is used only for analytics. It's essentially a partial
> replication of a production (document-oriented) database. So a lot of normal
> operations that might employ a series of sequential fetches may not actually
> be the norm in my case. Rather, I'm doing a lot of counts on data that is
> typically randomly distributed.

Yes try lowering it. Generally speaking, random page cost should
always be >= seq page cost. Start with a number between 1.5 and 2.0
to start with and see if that helps. You can make it "sticky" for
your user or database with alter user or alter database...