Re: Query performance help with 'shadow table' approach.

Hi,

On 14 September 2011 07:44, Brian Fehrle <brianf(at)consistentstate(dot)com> wrote:
> 2. I have appropriate indexes where they need to be. The issue is in the
> query planner not using them due to it (i assume) just being faster to scan
> the whole table when the data set it needs is as large as it is.

Try to reduce random_page cost to 2, which biased planner towards
index scans, (set random_page = 2 before the query; assuming that
default seq_page_cost and random_page_cost are 1 and 4 respectively)
and run "explain analyze". Sometimes is worth to disable nested loops
join (set enable_nestloop = off). Finally you can increase
default_statistics_target (or ALTER TABLE SET STATISTICS) to 100 (8.4
has this as a default) on selected columns or table (and run analyze
on that table).

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)