I saw advices like Indexes should not be used on small tables or Index on small table may decrease performance`. Can anybody provide magnitude of table size when creating indices makes sense, besides trial and error?
Background: I want to add indices to several tables in database and looking for a general rule to start with.
You're missing the point of @a_horse_with_no_name's comments. You create indexes to satisfy queries but whether or not they're chosen is a result of statistics. Whether or not choosing them (or not) is correct action is a factor of the accuracy of statistics to model the workload, environment, and indexes.
Statistics take into account things like selectivity to determine block/page fetches. To determine how many blocks you must retrieve you need to have a value for table size (rows), row width (bytes per row), page size, and page fragmentation.
The point is it's complex, to make matters even more complex most of the values are configurable or can be tuned by either the distro, the user, or the person who compiles the database.
So you're working at the problem from the wrong angle, the right question isn't
Are all of those factors correct to create an index?
But instead,
For this given query, can I use an index?
That is to say, go ahead and create the index. Then if the index is not used in the query and you think it should be, you can come back here and paste the query, the schema, the result of EXPLAIN ANALYZE, and your configuration parameters and hope someone will break it down for you as to why your index which could have been used was not chosen.
As an aside, one of the most powerful tools to determine that is set enable_seq_scan = 0;. By disabling sequential table scan, you can usually force the planner to use an index. If it uses your index and it's slower, you know that PostgreSQL made the right plan. That's often the case. The planner is smarter than 98% of the users; and, this method is the "check that it's powered on" of query optimization.
