My question is regarding use of indexes.
Should I start indexing right from the start or when performance problem arises?
We can also create temporary index while executing a query. What are the pros and cons of such techniques?
Indexing strategy tends to evolve as usage patterns emerge. That said, there are also strategies and design guidelines that can be applied up front.
Beyond the above, take a gradual and holistic approach to implementing new indexes. By holistic, I mean assess the potential benefit and impact to all queries and existing indexes when evaluating an addition.
A not uncommon problem in SQL Server circles is overindexing, as a result of guidance from the missing index DMVs and SSMS hints. Neither of these tools evaluate existing indexes and will merrily suggest you create a new 6 column index rather than add a single column to an existing 5 column index.
Kimberly Tripp has some excellent material on indexing strategy that while SQL focused is applicable to other platforms. For the SQL Server folk, there are some handy tools for identifying duplicates like the example above.
This usually only applies for rarely run queries, typically ETL. You need to assess:
There's really risks associated with both approaches:
Option a) Index from the start, but not realize you have created a number of indexes which are never used. These add some overhead (most noticeably to queries that modify data, but also with optimization of SELECT statements trying to identify the best index).
You will need to discipline yourself to identify indexes no longer being used and try and remove them (PostgreSQL can do this; unfortunately MySQL by comparison is very weak at this out of the box.)
Option b) Don't add indexes until people start complaining, or your diagnostic tools trigger that certain queries are slow and could be improved.
The risk that you introduce is that you don't have a big enough time window between when you notice you need the index, and when you have to add it.
PostgreSQL does support building indexes
Option (b) tends to be my preference, but I think a hybrid of both options is probably the best solution. It has to do with your confidence level as to whether you think an index will actually be used.
What makes this a particularly complex discussion is that it is usually easy to change indexes, but it is harder to change schema. I do not want to promote the delayed reaction of b as an excuse to be reckless.
In addition to Mark's answer
You can get a feel by having realistic test data at expected quantities. I've seen many, many (too many) cases where a query runs OK with a 1000 rows but not the million in production.
If you can, work on a copy of production later on,
Of course, I've seen the odd problem only in production because of usage patterns when everything else is identical
Temporary indexes? Outside of ETL load patterns, if you need them once you'll need them again. Don't forget: an index create/drop is a write and is logged = more load
Just to add a few things.
This is my approach.
I will try to answer only the first question. If you can estimate even roughly from the beginning how many records you'll have in your tables after a certain amount of time, than I'd say it's better to start from the beginning to design some indexes. Try to use some test tools or test scripts that will automate as many calls as possible for the application calls that you think will be most often used and you'll see what table scans can be avoided from the beginning.
It will be a guess work at the beginning, but in time, as you have proper usage statistics, you'll have a clearer image.