Re: query using incorrect index - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: query using incorrect index
Date
Msg-id 501B9AEB02000025000493CD@gw.wicourts.gov
Whole thread Raw
In response to Re: query using incorrect index  (Russell Keane <Russell.Keane@inps.co.uk>)
Responses Re: query using incorrect index  (Russell Keane <Russell.Keane@inps.co.uk>)
List pgsql-performance
Russell Keane <Russell.Keane@inps.co.uk> wrote:

> "log_min_duration_statement";"1ms"

> "shared_buffers";"32MB"
> "work_mem";"1MB"

Those are pretty low values even for a 4GB machine.  I suggest the
following changes and additions, based on the fact that you seem to
have the active portion of the database fully cached.

shared_buffers = '160MB'
work_mem = '8MB'
seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.03
effective_cache_size = '2GB'

> Explain analyse with both indexes present but without the limit
> (uses the correct index):

> "Total runtime: 0.092 ms"

Part of problem is that it thinks it will find a matching row fairly
quickly, and having done so using the index it chose will mean it is
the *right* row.  The problem is that there are no matching rows, so
it has to scan the entire index.  More fine-grained statistics
*might* help.  If other techniques don't help, you can rewrite the
query slightly to create an optimization fence, but that should be a
last resort.  I agree with Robert that if you have a lot of queries
that select on "incoming" and/or "inactive", a conditional index
(with a WHERE clause in its definition) is likely to be very
helpful.

-Kevin

pgsql-performance by date:

Previous
From: Russell Keane
Date:
Subject: Re: query using incorrect index
Next
From: Russell Keane
Date:
Subject: Re: query using incorrect index