On Thu, Sep 25, 2008 at 8:24 AM, Jeff Amiel <JAmiel@istreamimaging.com> wrote:
> "PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC)
> 3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
> (test environment)
Update your pgsql version to 8.2.10 or whatever is latest. There was
some pathological planner behaviour that tom fixed. I believe it was
fixed between those versions.
> Picture a table called 'transaction' with 1 million rows.
> most (> 99% of the records have date1 and date2 values in the past
> (spread over 4 years)
> 99.99% of the records have a state OTHER than 'I'.
I'd definitely make a partial or functional index on that then. If
you test for not 'I' then make it on the date with that:
create index xyz on table (somedate_field[, another_date_field]...)
where status <> 'I';
Don't know if that would help, but I've seen it give the planner a
hint and produces a useful index.
Or for queries where it needs the opposite:
same as before then ... where status='I'
Just a thought. Functional / partial indexes can make a big
difference if you get it right, but don't create one unless you know
it helps because of update costs.
