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


From: "Dmitry Andrianov" <dimas(at)dataart(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Index
Date: 2001-06-09 18:33:18
Message-ID: D25012D499D87C4B8A03AF727E624195042FB3@exchange2000.universe.dart.spb (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

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)

test=# EXPLAIN SELECT * FROM lnames WHERE name='ivanov';

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


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.

Dmitry Andrianov

pgsql-novice by date

Next:From: Joe MoracaDate: 2001-06-09 20:21:55
Subject: Re: How do I start postmaster with -i for tcp-ip
Previous:From: albert DURANTONDate: 2001-06-08 19:14:50
Subject: (no subject)

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