Re: Why are selects so slow on large tables, even when

On Tue, 2002-03-26 at 18:28, Robert Wille wrote:
> To test PostgreSQL's scalability, I created a table with approximately 76M rows.
> The table had four columns: a bigint, a varchar(32), another bigint
> and a varchar(80).

> select count(*) from a where id < 0; /* returns 0 rows */
> select * from a where id=5; /* returns a handful of rows */
>
> 76M rows is a lot, but it shouldn't be that bad when id is indexed.

A couple things:

(1) You indicated that you ran VACUUM. You'll need to run VACUUM ANALYZE
(or just ANALYZE) to update the planner's statistics. For your
particular situation, this is essential.

(2) There is a long-standing bug with indexes on int8 columns: if you
use a numeric literal as a qualifier, it will be converted to an int4,
so the index won't be used. There is an easy work-around:

select * from a where id = 5; /* won't use index if id is int8 */
select * from a where id = 5::int8; /* will use index, if appropriate */

(3) You can get more information on the decisions Postgres is making
when executing your query through the use of EXPLAIN. In this instance,
it will likely tell you that the index isn't being used at all, and a
sequential scan is being performed.

If you follow suggestions #1 and #2, you should see markedly improved
performance. Let us know the results...

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC