On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane
<tgl@sss.pgh.pa.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.