Inner joins involving three tables

I have a SELECT state开发者_JAVA百科ment that has three inner joins involving two tables. Apart from creating indexes on the columns referenced in the ON and WHERE clauses, is there other things I can do to optimize the joins, as in rewriting the query?

SELECT
  ...
FROM
  my_table AS t1
INNER JOIN
  my_table AS t2
ON 
  t2.id = t1.id
INNER JOIN
  other_table AS t3
ON
  t2.id = t3.id
WHERE
 ...

You can tune PostgreSQL config, VACUUM ANALIZE and all general optimizations.

If this is not enough and you can spend few days you may write code to create materialized view as described in postgresql wiki.

You likely have an error in your example, because you're selecting the same record from my_table twice, you could really just do:

SELECT
  ...
FROM
  my_table AS t1
INNER JOIN
  other_table AS t3
ON
  t1.id = t3.id
WHERE
 ...

Because in your example code t1 Will always be t2.

But lets assume you mean ON t2.idX = t1.id; then to answer your question, you can't get much better performance than what you have, you could index them or you could go further and define them as foreign key relationships (which wouldn't do too much in terms of performance benefits compared to non-index vs indexing them).

You might instead like to look at restricting your where clause and perhaps that is where your indexing would be as (if not more) beneficial.

You could write your query as using WHERE EXISTS (if you don't need to select data from all three tables) rather than INNER JOINS but the performance will be almost identical (except when this is itself inside a nested query) as it still needs find the records.

In PostgreSQL. most of your tuning will not be on the actual query. The goal is to help the optimizer figure out how best to execute your declarative query, not to specify how to do it from your program. That isn't to say that sometimes queries can't be optimized themselves, or that they might not need to be, but this doesn't have any of the problem areas that I am aware of, unless you are retrieving a lot more records than you need to (which I have seen happen occasionally).

The fist thing to do is to run vacuum analyze to make sure you have optimal statistics. Then use explain analyze to compare expected query performance to actual. From that point, we'd look at indexes etc. There isn't anything in this query that needs to be optimized on a query level. However without looking at your actual filters in your where clause and the actual output of explain analyze there isn't much that can be suggested.

Typically you tweak the db to choose a better query plan rather than specifying it in your query. That's usually the PostgreSQL way. The comment is of course qualified by noting there are exceptions.