Re: The planner hates me.

On Thu, Sep 25, 2008 at 8:24 AM, Jeff Amiel <JAmiel(at)istreamimaging(dot)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.