Re: Query with order by and limit is very slow - wrong index used

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: Nowak Micha* <michal(dot)nowak(at)me(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query with order by and limit is very slow - wrong index used
Date: 2011-10-04 23:33:23
Message-ID: 4E8B51730200002500041A45@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Nowak Micha*<michal(dot)nowak(at)me(dot)com> wrote:

> Lowering random_page_cost didn't help -- I've tried values 2.0 and
> 1.5.

First off, I don't remember you saying how much RAM is on the
system, but be sure to set effective_cache_size to the sum of your
shared_buffers and OS cache. I've often found that the optimizer
undervalues cpu_tuple_cost; try setting that to 0.05. Then,
depending on how well cached the active portion of your database is,
you may want to drop your random_page_cost down close to or equal to
seq_page_cost. If your cache hit rate is high enough, you might
want to drop *both* seq_page_cost and random_page_cost to something
as low as 0.1 or even 0.05.

The objective is to model the actual costs of your workload against
your data on your hardware. Sometimes that takes a bit of
tinkering.

-Kevin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Venkat Balaji 2011-10-05 02:50:18 Re: : PG9.0 - Checkpoint tuning and pg_stat_bgwriter
Previous Message Scott Marlowe 2011-10-04 22:47:10 Re: : PG9.0 - Checkpoint tuning and pg_stat_bgwriter