Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: postgresql not using index even though it's faster

    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?

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    index scan

    in some circumstances the planner makes a seq scan before an index scan

    it is always better to turn seq scan off

    or if you have some special queries

    write

    set seq scan=off
    [query]
    set seq scan on

    this is the best you can do

    there are planner improvements in 7.4
    when it is out of beta try this version
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •