Now that all those optimizations have been discussed, it is time to see what kind of execution plan is produced by PostgreSQL:
test=# explain SELECT * FROM v, c WHERE v.aid = c.cid AND cid = 4;
QUERY PLAN
----------------------------------------------------------------
Nested Loop (cost=1.71..17.78 rows=1 width=12)
-> Nested Loop (cost=1.14..9.18 rows=1 width=8)
-> Index Only Scan using idx_a on a
(cost=0.57..4.58 rows=1 width=4)
Index Cond: (aid = 4)
-> Index Only Scan using idx_b on b
(cost=0.57..4.59 rows=1 width=4)
Index Cond: (bid = 4)
-> Index Only Scan using idx_c on c
(cost=0.57..8.59 rows=1 width=4)
Index Cond: (cid = 4)
(8 rows)
Note that the plans shown in this chapter are not necessarily 100% identical to what you will observe. Depending on how much data you have loaded, there might be slight variations. Costs might also depend on the physical alignment of data on the disk (order on disk). Please keep this in mind when running these examples.
As you can see, PostgreSQL will use three indexes. It is also interesting to see that PostgreSQL decides to go for a nested loop to join the data. This makes perfect sense because there is virtually no data coming back from the index scans. Therefore, using a loop to join things is perfectly feasible and highly efficient.