Re: Indexes not used

From: David Olbersen <dave(at)slickness(dot)org>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Indexes not used
Date: 2001-03-15 23:31:58
Message-ID: Pine.LNX.4.31.0103151519530.7826-100000@bubbles.electricutopia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message D. Duccini 2001-03-16 14:26:14 Re: Indexes not used
Previous Message lee johnson 2001-03-15 22:46:13 Re: add primary key