Why are selects so slow on large tables, even when indexed?

From: "Robert Wille" <rwille(at)iarchives(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Russell Black" <russell(dot)black(at)iarchives(dot)com>
Subject: Why are selects so slow on large tables, even when indexed?
Date: 2002-03-26 23:28:54
Message-ID: 00af01c1d51d$fe859620$0864a8c0@zucchini
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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). The first three columns were filled with values, the fourth was left null. After populating the table, I created an index on the first column (a non-unique index, as the column contains duplicate values) and then VACUUMed. Select statements involving only the indexed column are pathetically slow (tens of minutes). Some examples:

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.

Attached are two scripts. One creates the table, the other populates it. I typed "create index index_a on a(id)" and "vacuum" by hand. I see this behavior both on Windows and RedHat Linux using PostgreSQL version 7.1.3 in both cases. Any idea why the performance is so poor? Can this be corrected by tuning?

Attachment Content-Type Size
populate-table.sql application/octet-stream 3.4 KB
create-table.sql application/octet-stream 269 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dan Langille 2002-03-26 23:41:15 Re: Why are selects so slow on large tables, even when indexed?
Previous Message Robert Wille 2002-03-26 23:18:35 Why are selects so slow on large tables, even when indexed?