Re: Postgres 8.3, four times slower queries?

On Tue, 3 Mar 2009, Aaron Guyon wrote:

> 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.

if you haven't done a vaccum analyse on either installation then postgres'
idea of what sort of data is in the database is unpredictable, and as a
result it's not surprising that the two systems guess differently about
what sort of plan is going to be most efficiant.

try doing vaccum analyse on both databases and see what the results are.

David Lang