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