Skip site navigation (1) Skip section navigation (2)

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

From: Douglas Alan <darkwater42(at)gmail(dot)com>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: What is the right way to deal with a table with rows that are not in a random order?
Date: 2009-05-28 02:43:20
Message-ID: ce6334d00905271943x5e38e5f6l2e7ca132103575a9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, May 27, 2009 at 8:54 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:


> If you're putting a LIMIT on it, why does it return millions of results?
>

It *doesn't* return millions of results with the LIMIT on it.  It just does
a sequential scan of the table and doesn't find any results until it gets to
the last quarter of the table. Sequentially scanning through 3/4 of the huge
table before it gets a single match takes a very long time.

As I said, in my original post, Postgres's approach would be completely
reasonable in this case,* if* the rows that it was looking for were
sprinkled randomly throughout the table.  But they're *not* in this case --
they're all at the end.

Can you pick out an interesting query and give some specifics, like:
> * the query
> * the EXPLAIN ANALYZE output (or EXPLAIN without ANALYZE if it takes too
> long to even run once)
> * EXPLAIN ANALYZE output if you force the index scan
> * the statistics for the relevant columns, such as histogram and
> correlation


As I mentioned, the situation is very simple, and easy to understand what is
going on.  There's absolutely no mystery as to why Postgres is doing what
it's doing.  25% of the table matches the query.  Postgres *knows* this due
to the statistics histogram for the column.  Postgres is deciding to do a
sequential scan because it knows that 25% of the rows match the query.

Unfortunately, in this case, that's a poor approach.

|>ouglas

In response to

Responses

pgsql-general by date

Next:From: Anirban PalDate: 2009-05-28 06:04:56
Subject: Re: Postgres registry access using java
Previous:From: Jeff DavisDate: 2009-05-28 00:54:27
Subject: Re: What is the right way to deal with a table with rows that are not in a random order?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group