CODEWITHSUNDEEP
sqlpostgresqlindexing
john
john

Reputation: 35400

Is this index defined correctly for this join usage? (Postgres)

select
    *
from
    tbl1 as a
inner join
    tbl2 as b on
    tbl1.id=b.id
left join
    tbl3 as c on 
    tbl2.id=tb3.parent_id and
    tb3.some_col=2 and
    tb3.attribute_id=3

In the example above:

If I want optimal performance on the join, should I set the index on tbl3 as so?

parent_id,
some_col,
attribute_id

Upvotes: 0

Views: 21

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247250

The answer depends on the chosen join type.

  • If PostgreSQL chooses a nested loop or a merge outer join, your index is perfect.

  • If PostgreSQL chooses a hash outer join, the index won't help at all. In that case you need an index on (some_col, attribute_id).

Work with EXPLAIN to make the best choice for your case.

Note: If one of the conditions on some_col and attribute_id is not selective (doesn't filter out a significant number of rows), it is often better to omit that column in the index. In that case, it is better to get the benefit of a smaller index and more HOT updates.

Upvotes: 1

dfundako
dfundako

Reputation: 8324

My answer is "Maybe". I am speaking from experience with SQL Server, so someone please correct me if I am wrong and it is different in Postgres.

Your index looks fine for the most part. An issue that may arise is using the SELECT *. If tbl3 has more columns than what is defined in your index and you are querying those fields, they won't be in your index and the engine will have to do additional lookups outside that index.

Another thing would be based on the cardinality of your fields, meaning which are the most selective. If parent_id has a high cardinality, meaning very few duplicates, it could cause more reads against the index. However, if your lowest cardinality field is first and the db can quickly filter out huge chunks of data, that might be more efficient.

I have seen both work very well in SQL Server. SQL Server has even recommended indexes, I apply them, and then it recommends a different one based on field cardinality. Again, I am not familiar with the Postgres engine and I am just assuming these topics apply across both. If all else fails, create 3 indexes with different column order and see which one the engine likes the best.

Upvotes: 0

Related Questions