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

Index

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 or flat)
Thread:
Lists: pgsql-novice
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


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-2014 The PostgreSQL Global Development Group