postgresql not using index even though it's faster
От | Rudy Koento |
---|---|
Тема | postgresql not using index even though it's faster |
Дата | |
Msg-id | 20030827033250.96989.qmail@web41601.mail.yahoo.com обсуждение исходный текст |
Ответы |
Re: postgresql not using index even though it's faster
Re: postgresql not using index even though it's faster |
Список | 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
В списке pgsql-general по дате отправления: