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
