How to write index query in postgresql

Question

in sql server index query written as follows SELECT * FROM TabEmp WITH (INDEX(idx_TabEmp))

in same way how to write query in postgresql and how to use indexes in postgresql.

Answer 1

PostgreSQL comes with a very advanced planner, that does it's best to use all possible means in order to deliver query details in the most efficient way.

When it comes to the indexes, one has only to create them on the proper columns, most likely the ones used in the FROM (explicit notation), WHERE or ORDER BY clauses. As documentation says:

Once an index is created, no further intervention is required: the system will update the index when the table is modified, and it will use the index in queries when it thinks doing so would be more efficient than a sequential table scan.

Of course, you will have to ANALYZE your tables and look through the EXPLAIN output to understand what actually database is doing to process your query.

---

EDIT

My main everyday job is administration of a set of Billing production systems, running on ORACLE, so I know what hints are. As administrator I know my schemas and data very well, and in most cases I do know what is the best plan to execute my query.

Now, as ORACLE provide lots of different features, it's optimizer simply cannot “catch up” on the amount of possible paths. Therefore they went another way and added hints. And I do use them, 'cos there is no other way for me to force the specific plan that I know should perform best, no matter what I do with stats on the involved relations.

PostgreSQL went another way. Instead of providing hints, they were spending lots of hours in making the planner as good as it is today. I'm impressed, and this is where PostgreSQL just kicks all other RDBMSes out of the ring. IMHO.

Yes, not always you get the best plans with PostgreSQL. But there are ways to reach the goal. As other have already pointed out:

1. you should know your data and you should know what the best plan would look like;
2. use proper tools, like EXPLAIN (analyze, buffers) together with http://explain.depesz.com/
3. make sure you have proper stats;
4. play with enable_* settings to really get the right plan;
5. if still no luck, look into the data and understand the reasons why chosen plan is not the best one. I assume that you will find a data distribution skew that don't match the way PostgreSQL gather stats for some particular attributes involved in your query;
6. adjust SET STATISTICS for the columns that you've found to have data skews;
7. if still no luck, you can use other techniques, like range or list partitioning, partial indexes, CTEs, there're lots of.

In my experience with PostgreSQL it was never necessary to use hints. And despite the fact that adding a hint is “fast and easy”, I always prefer to solve the badly performing query via common means, as hint might do a bad job in case data distribution or database usage pattern changes (quite common scenario).

The only place where I see hints might be usable: hotfixing production issues of a closed source software you're using / supporting. But this also is not the case if you do a deep and thorough QA and Performance testing before rolling out new releases.

If you're still in need of a hints, take a look into plantuner by Oleg Bartunov and Teodor Sigaev. This extension is not in the standard PostgreSQL distribution, but given Oleg and Teodor are in the core PostgreSQL Development team (they created GIN, GIST and FTS for the project), I would expect the code to be quite stable.