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