Re: Indexes not used - Mailing list pgsql-novice

Maybe I'm not getting something here...but how is a sequential scan EVER
faster than a B-tree / index lookup on a database with over 500,000
records?

Certainly I could split out the data, and do some "roll-up" ops on the
information in there, it just seems odd that in 6.5.x it was using the
indices and was blazing fast

Now in 7.0.3 its like they are not even considered...at least on this
particular table....other tables they seem to be working

On Thu, 15 Mar 2001, David Olbersen wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
-----------------------------------------------------------------------------