Re: Indexes not used - Mailing list pgsql-novice

From David Olbersen
Subject Re: Indexes not used
Date
Msg-id Pine.LNX.4.31.0103151519530.7826-100000@bubbles.electricutopia.net
Whole thread Raw
In response to Re: Indexes not used  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Indexes not used
List pgsql-novice
Just as an example, here's the query plan of the *SAME* query before and after a
VACUUM ANALYZE

Notice the way the two plans are *COMPLETELY* different. l_portal_statuses and
b_portal_statuses only have *3 rows* right now, but there was no way for the
planner to know that. Anyway, this should be evidence that a good VACUUM ANALYZE
periodically is a Good Thing(tm).

(BTW, the site that this database drives is now significantly more responsive)

Before:
----------------------------------------
Merge Join  (cost=97.62..170.37 rows=1000 width=110)
  ->  Index Scan using l_portal_statuses_pkey on l_portal_statuses lps (cost=0.00..59.00 rows=1000 width=16)
  ->  Sort  (cost=97.62..97.62 rows=100 width=94)
    ->  Merge Join  (cost=22.67..94.30 rows=100 width=94)
      ->  Index Scan using b_portal_statuses_pkey on b_portal_statuses bps  (cost=0.00..59.00 rows=1000 width=16)
      ->  Sort  (cost=22.67..22.67 rows=10 width=78)
        ->  Seq Scan on contracts c  (cost=0.00..22.50 rows=10 width=78)

After:
----------------------------------------
Nested Loop  (cost=0.00..3.47 rows=1 width=110)
  ->  Nested Loop  (cost=0.00..2.40 rows=1 width=94)
    ->  Seq Scan on contracts c  (cost=0.00..1.34 rows=1 width=78)
    ->  Seq Scan on b_portal_statuses bps  (cost=0.00..1.03 rows=3 width=16)
  ->  Seq Scan on l_portal_statuses lps  (cost=0.00..1.03 rows=3 width=16)

-- Dave


pgsql-novice by date:

Previous
From: lee johnson
Date:
Subject: Re: add primary key
Next
From: "D. Duccini"
Date:
Subject: Re: Indexes not used