Re: Serious Performance Loss in 7.0.2??

Tim Perdue <tperdue(at)valinux(dot)com> writes:
>> Also, when you are doing the complete query with sort, does a
>> pg_sorttemp file appear in the database directory? If so, how big does
>> it get?

> Doesn't look like it. There is a (supposedly) dead pg_sorttemp file in
> that directory that is 74MB. Probably unrelated.

That's even odder. If the sort is being done entirely in memory (which
I'd expected given the amount of data and your -S setting, but it's good
to confirm) then it's basically a qsort() call, and there shouldn't be
any measurable difference between 6.5 and 7.0.

All else being equal that is. Since this is a sort on a char() field,
perhaps all else is not equal. In particular I'm suddenly wondering
if your 7.0 installation was compiled with LOCALE or MULTIBYTE support
and your 6.5 not. A few tens of thousands of strcoll() calls to do the
sort comparisons might account for the slowdown...

> My only remaining question is whether an index on 4,000,000 datestamps
> is going to be fast or not.

I'd expect no significant difference from your other indexes on that
table. If anything, it'll be faster than your existing index because
of the lack of duplicate keys.

regards, tom lane