Re: What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 1:12 PM, Douglas Alan <darkwater42(at)gmail(dot)com> wrote:
> On Thu, May 28, 2009 at 10:24 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
>>  OTOH, if you've got it all sussed out, then ignore the request for more information.
>
> I don't *know* if I have it "all sussed out", but I *do* know why
> Postgres is doing what it is doing in this particular case.  It's
> assuming that the value in question is evenly distributed throughout
> the table, when in actuality, the value in question is clustered at
> the very end of the table.

It's doing way more than that. My point above was that the query
planner is not JUST assuming the values are wel ordered. It's
assuming random_page_cost is x times more than sequential page cost,
it's assuming the table doesn't fit in effective cache, or shared
buffers, it's assuming lots of things based on how you've tuned (or
not) your database.

I'll finish in reply to your other post.