"Efficiency" means to accomplish a goal with minimum effort. So what is efficient depends on the goal, and you cannot say something like "a query is executed efficiently if it takes less than the tenth of a second". Essentially, a query is efficient if there is no substantially faster way to do the task.
Another, more pragmatic, approach is to make queries efficient enough. If it does what you want it to do and the execution time and resource usage is fine for your purpose, stop worrying. You should also consider that optimizing a query to the theoretical optimum (e.g., by creating a specialized index) might negatively affect other parts of the system (e.g., data modifications become slower). You want to optimize the overall performance and resource usage of the system.
All that said, it should be clear that there can be no simple checklist that you can work off to ensure efficiency. But I can give you a short list of SQL anti-patterns that often lead to inefficient queries in my exoerience:
Don't use DISTINCT unless you are certain that it is required. It usually requires sorting, which is very expensive for large sets.
Avoid OR in WHERE conditions. It tends to prevent indexea from being used.
Use outer joins only if you are certain that an inner join won't do the trick. The database has fewer possibilities to rearrange such joins.
Use a normalized data model. Don't fall into the trap of using arrays or JSON in the database.
Use UNION ALL instead of UNION unless you need to eliminate duplicates. This is similar to DISTINCT.
Use WHERE EXISTS (/* subquery */) rather than WHERE x IN (/* subquery */). IN can always be rewritten as EXISTS, and the PostgreSQL optimizer is better at dealing with the latter.
These rules should be understood as rules of thumb.
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
