Index

From: "Dmitry Andrianov" <dimas(at)dataart(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Index
Date: 2001-06-12 17:44:45
Message-ID: D25012D499D87C4B8A03AF727E624195022BDE@exchange2000.universe.dart.spb
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi.

I just installed PG 7.1.2, created a table, consisting of single
varchar(32) column 'name' and filled it with ~3M records (real last name
from our user database). After that i have created index on this column
and VACUUM ANALYZEd the table.

test=# \d lnames
Table "lnames"
Attribute | Type | Modifier
-----------+-----------------------+----------
name | character varying(32) |
Index: idx_main

test=# \d idx_main
Index "idx_main"
Attribute | Type
-----------+-----------------------
name | character varying(32)
btree

test=# EXPLAIN SELECT * FROM lnames WHERE name='ivanov';
NOTICE: QUERY PLAN:

Index Scan using idx_main on lnames (cost=0.00..4847.52 rows=1271
width=12)

EXPLAIN

I think, PG should respond instantly on queries like "SELECT * FROM
lnames WHERE name='ivanov'". And it does unless there are many records
with the same value. But in case when there are about 3000 records with
name='ivanov', it takes about a half a minute for Postgres before I can
even see a first row.

Why is that?

Also interesting:

SELECT COUNT(*) FROM lnames WHERE name='ivanov';

this query takes about 20 seconds on table without an index
(sequentional scan) and 40 seconds (2 times more!) after index has been
built and analyzed.

Could anybody explain me what i'm doing wrong, please?

Thanks in advance.

Regards,
Dmitry Andrianov

Browse pgsql-general by date

  From Date Subject
Next Message Thalis A. Kalfigopoulos 2001-06-12 17:55:02 Oracle news article
Previous Message Doug McNaught 2001-06-12 17:39:00 Re: ideas on optimization