From: | Ben <bench(at)silentmedia(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)collaborativefusion(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: quickly getting the top N rows |
Date: | 2007-10-04 18:33:39 |
Message-ID: | Pine.LNX.4.64.0710041126010.30864@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, 4 Oct 2007, Bill Moran wrote:
> However, 2 guesses:
> 1) You never analyzed the table, thus PG has awful statistics and
> doesn't know how to pick a good plan.
> 2) You have so few rows in the table that a seq scan is actually
> faster than an index scan, which is why PG uses it instead.
No, the tables are recently analyzed and there are a couple hundred
thousand rows in there. But I think I just figured it out.... it's a
3-column index, and two columns of that index are the same for every row.
When I drop those two columns from the ordering restriction, the index
gets used and things speed up 5 orders of magnitude.
Maybe the planner is smart enough to think that if a column in the order
by clause is identical for most rows, then using an index won't help....
but not smart enough to realize that if said column is at the *end* of the
order by arguments, after columns which do sort quite well, then it should
use an index after all.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-10-04 18:52:10 | Re: quickly getting the top N rows |
Previous Message | Andreas Kretschmer | 2007-10-04 18:22:42 | Re: quickly getting the top N rows |