postgresql not using index even though it's faster

[Rudy Koento]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Karsten Hilbert]

> 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:

This sounds like someone needs to put a big fat link to
this FAQ item at the top of the PostgreSQL frontpage URL ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)