Tim Perdue <tperdue@valinux.com> writes:
> Tom Lane wrote:
>> Hard to tell with this much info. How many rows are actually retrieved
>> by the query (the planner is guessing 34, is that anywhere in the right
>> ballpark? How big is the table, anyway?)
>>
>> Also, what's the definition of the index idx_mail_archive_list_yr_mo?
>>
>> It might help to see the EXPLAIN VERBOSE output also --- I'm wondering
>> if all the WHERE clauses are getting used as index keys or not...
> OK - there are 5851 rows in this query.
> idx_mail_archive_list_yr_mo is an index on
> mail_list (int)
> mail_year (int)
> mail_month(int)
> With 5850 rows to sort, I wouldn't expect it to be lightning fast, but
> there is a very definite difference from 6.5.3 (or 6.4.x).
> As requested by "The Hermit Hacker", I took out the ORDER BY and it was
> instantaneous.
> I'm sending the explain verbose separately to you as it's very big.
The explain verbose looks just like it should: all three WHERE clauses
are being used as indexkeys. So I'm mystified. 7.0 is not doing
anything obviously wrong here, and I do not understand what 6.5 might
have done differently. Given the query as posed and the available
index, there is no other alternative but an indexscan followed by sort.
I like Andreas' suggestion of rearranging things so that the indexscan
will produce already-sorted output (since that will allow the LIMIT to
stop the indexscan without reading the whole month's traffic). But that
doesn't answer the question of why 7.0 is so much slower given the same
query as 6.5.
How long does it take to doSELECT count(*) FROM tbl_mail_archive WHERE fld_mail_list=35 ANDfld_mail_year=2000 AND
fld_mail_month=1
? That should tell us how much time is being spent in the indexscan.
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?
regards, tom lane
