Re: Postgres 8.3, four times slower queries?

On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Are you sure you are comparing apples to apples here? Same configure
> options for the builds, same parameter values in postgresql.conf, both
> databases ANALYZEd, etc? And are they running on the same hardware?
>

Thank you for looking at this Tom. Yes, we have made sure we are comparing
apples to apples here. The postgresql.confs are identical, as are the
configure flags:
--disable-debug --enable-shared --enable-thread-safety --with-perl
--with-pam --without-docdir --without-tcl --without-python --without-krb5
--without-ldap --without-bonjour --enable-integer-datetimes
--prefix=/opt/postgresql

However, the db was not analyzed. I'll attached the new explain analyze of
the queries with the db analyzed, but 8.2 still beats 8.3.

The tests are both being run on the same machine, a Quad-core AMD Opteron
Processor 2212
(each with 1024 KB cache) and 4GB of RAM.

I find it telling that the query plan differs so much between postgres 8.2.
and
8.3. For example, why does the 8.3. planner choose to perform so many seq
scans? I know seq scans are faster than index scans for small tables, but
these tables have 60K+ rows... surely an index scan would have been a better
choice here? If you look at the 8.2. query plan, it is very clean in
comparison, index scans all the way through. I can't help but think the 8.3
planner is simply failing to make the right choices in our case. Another
question would be, why are there so many hash joins in the 8.3 plan now?
All
our indexes are btrees...

Any light that can be shed on what going on with the 8.3. planner would be
much
appreciated. Thanks in advance.