On Thu, Sep 25, 2008 at 8:24 AM, Jeff Amiel <[EMAIL PROTECTED]> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
