>> >> There is only one search for which your 5 rows will be at the start of >> the table. In general, you need an index on the table in order to >> find your data with any efficiency. If you have an index that matches >> your search pattern then the search will stop after looking at 6 rows >> if it determines that the 6th row does not follow sequentially in the >> index after the other 5 rows. There is no difference whether there >> are 10 or 10 million rows in the database. > > I recently read from a Postgres guru that is not good to index a field with too few elements in > large table. > > For example, suppose you have a field with a boolean value. This is not a good idea to index this > field. This is a big penalization for every DB engine. Is better let him to do the sequence search > instead of a index search. And this sequence search is the penalization I talk about. > > Also I did some benchmarks of this using postgres and really this makes a diference for inserting, > updating and deleting a row..... > > I think it is best to share all this info with you. please visit: > > http://techdocs.postgresql.org/techdocs/pgsqladventuresep3.php > > There is a better description of when index and when not. from a database guru, not me. ;-) > > Best regards,
Yes, fair enough, you're shouldn't be indexing on a type column (or a Boolean column) in order to determine whether you've found your N records or not. Since there are, in general, never N records in this column it won't tell you anything. You still need a common foreign key somewhere else in your database that you can index on. If you don't have such a key but you do have a database that supports hard coded limits on search size then yes, you could improve performance but having the hard limit. In such a case, the average performance would be 50% better for the hard stop (but you've moved hard coded business logic into your SQL query which doesn't seem like a good idea to me). However, the whole issue goes away with proper database design... --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
