postgresql not using index even though it's faster

From: Rudy Koento <rudy_koento(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: postgresql not using index even though it's faster
Date: 2003-08-27 03:32:50
Message-ID: 20030827033250.96989.qmail@web41601.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, I've created an index but it's not being used by
postgresql when doing a query. But doing an "explain
analyze" shows that with index, it's faster. Here's
the output:

------------------------
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT S.* FROM sales S, staff ST
WHERE S.staff_no=ST.staff_no AND ST.name='Rudy';

Nested Loop (cost=0.00..351.35 rows=808 width=51)
(actual time=0.39..11.82 rows=717 loops=1)
-> Index Scan using staff_pkey on staff st
(cost=0.00..5.86 rows=1 width=4) (actual
time=0.19..0.24 rows=1 loops=1)
Filter: (name = 'Rudy'::character varying)
-> Index Scan using sales_staff_no_idx on sales s
(cost=0.00..332.02 rows=1077 width=47) (actual
time=0.19..8.22 rows=717 loops=1)
Index Cond: (s.staff_no = "outer".staff_no)
Total runtime: 12.60 msec
(6 rows)

------------------------
SET enable_seqscan = on;
EXPLAIN ANALYZE SELECT S.* FROM sales S, staff ST
WHERE S.staff_no=ST.staff_no AND ST.name='Rudy';

Hash Join (cost=1.15..253.60 rows=808 width=51)
(actual time=0.30..64.83 rows=717 loops=1)
Hash Cond: ("outer".staff_no = "inner".staff_no)
-> Seq Scan on sales s (cost=0.00..193.90
rows=9690 width=47) (actual time=0.06..49.63 rows=9690
loops=1)
-> Hash (cost=1.15..1.15 rows=1 width=4) (actual
time=0.19..0.19 rows=0 loops=1)
-> Seq Scan on staff st (cost=0.00..1.15
rows=1 width=4) (actual time=0.18..0.18 rows=1
loops=1)
Filter: (name = 'Rudy'::character
varying)
Total runtime: 65.47 msec
(7 rows)

I admit that I don't really understand the output of
EXPLAIN, but it's rather obvious from the above result
that an index scan is faster?

Can anyone help me?

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-08-27 03:37:10 Re: [GENERAL] Replication Ideas
Previous Message Bruce Momjian 2003-08-27 03:25:41 Re: [GENERAL] Replication Ideas