From: | Bill Moran <wmoran(at)collaborativefusion(dot)com> |
---|---|
To: | Ben <bench(at)silentmedia(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: quickly getting the top N rows |
Date: | 2007-10-04 18:14:11 |
Message-ID: | 20071004141411.9509fe66.wmoran@collaborativefusion.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
In response to Ben <bench(at)silentmedia(dot)com>:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is doing a full table scan on foo, then
> sorting it, then doing the limit. I have a more complex primary key, but I
> was hoping the same concept would still apply. Am I doing something wrong,
> or just expecting something that doesn't exist?
Show us the explain.
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.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2007-10-04 18:22:42 | Re: quickly getting the top N rows |
Previous Message | Mark Lewis | 2007-10-04 18:10:24 | Re: quickly getting the top N rows |