I got an interesting question, and I can probably see both sides of any debate,
but.....
Say you have a fairly large table, several million records. In this table you
have a key that has a fairly good number of duplicate rows. It is a users
favorites table, each user will have a number of entries.
My problem is, if you do a select by the user name, it does an index scan. If
you do a select from the whole table, ordered by the user name, it does a
sequential scan not an index scan. It is arguable that this may be a faster
query, but at the cost of many more resources and a very long delay before any
results are returned. Is this the best behavior?
Anyone have any opinions?
cdinfo=# select count(*) from favorites ; count
---------4626568
(1 row)
cdinfo=# explain select * from favorites where scene_name_full = 'someone' ;
NOTICE: QUERY PLAN:
Index Scan using fav_snf on favorites (cost=0.00..258.12 rows=64 width=73)
EXPLAIN
cdinfo=# explain select * from favorites order by scene_name_full ;
NOTICE: QUERY PLAN:
Sort (cost=1782827.92..1782827.92 rows=4724736 width=73) -> Seq Scan on favorites (cost=0.00..113548.36 rows=4724736
width=73)
EXPLAIN
cdinfo=# set enable_seqscan=FALSE ;
SET VARIABLE
cdinfo=# explain select * from favorites order by scene_name_full ;
NOTICE: QUERY PLAN:
Index Scan using fav_snf on favorites (cost=0.00..18682771.23 rows=4724736
width=73)
EXPLAIN
