How many rows are in grls? It's estimating 307 matching
rows in the first case and 20 in the second. At some
point due to needing to get the transactional information
for rows out of the heap, index scan becomes more expensive
than sequence scan as the number of rows retrieved increases.
This is discussed alot in the mailing lists. Look at the archives.
On Thu, 17 May 2001, Alexander Dederer wrote:
> IMN1=# explain select count(*) from grls WHERE active = 10;
> NOTICE: QUERY PLAN:
> Aggregate (cost=993.21..993.21 rows=1 width=0)
> -> Seq Scan on grls (cost=0.00..992.44 rows=307 width=0)
>
> IMN1=# explain select count(*) from grls WHERE popularity = 10;
> NOTICE: QUERY PLAN:
> Aggregate (cost=23.22..23.22 rows=1 width=0)
> -> Index Scan using grls_popularity on grls (cost=0.00..23.17 rows=20
> width=0)
>
> IMN1=# \di grls
> grls_active | index | alex
> grls_popularity | index | alex
>
> Why SELECT use Index grls_popularity and don't use index grls_active ?
> Both create and VACUUM ANALYZE grls?
>
> It's a BUG?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>