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

The suggested fixes have helped a lot, but it is still rather slow. The time
varies and can be upwards of 10 to 20 seconds on a ~47M row table. Is this
normal? Similar queries on an indexed varchar column in Oracle with about
1/2 as many rows execute at least a hundred times faster.

----- Original Message -----
From: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>
To: "Robert Wille" <rwille(at)iarchives(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>; "Russell Black"
<russell(dot)black(at)iarchives(dot)com>
Sent: Tuesday, March 26, 2002 4:47 PM
Subject: Re: [GENERAL] Why are selects so slow on large tables, even
whenindexed?

> 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
>