Right. In PostgreSQL you can see explicity that the hash join is different for the two queries:
With the view:
" Hash Cond: (("outer".item_id = "inner".item_id) AND ("outer".revision_id = "inner".live_revision))"
with the join on cr_items, cr_revisions
"Hash Cond: ("outer".revision_id = "inner".live_revision)"
It would be interesting to see how Oracle plans the two different queries.
Here is the second query rewritten to add item_id to the where clause
explain analyze select count(*) from cr_items ci, cr_revisions cr where ci.item_id=cr.item_id and ci.live_revision=cr.revision_id;
QUERY PLAN
-------------------------------------------------------------------------------\
---------------------------------------------------
Aggregate (cost=2200.15..2200.15 rows=1 width=0) (actual time=560.144..560.146 rows=1 loops=1)
-> Hash Join (cost=623.38..2200.14 rows=1 width=0) (actual time=128.619..517.290 rows=16383 loops=1)
Hash Cond: (("outer".item_id = "inner".item_id) AND ("outer".revision_id = "inner".live_revision))
-> Seq Scan on cr_revisions cr (cost=0.00..809.69 rows=18469 width=8) (actual time=0.076..72.671 rows=18469 loops=1)
-> Hash (cost=471.25..471.25 rows=17025 width=8) (actual time=127.608..127.608 rows=0 loops=1)
-> Seq Scan on cr_items ci (cost=0.00..471.25 rows=17025 width=8) (actual time=0.090..75.695 rows=17025 loops=1)
Total runtime: 561.095 ms
(7 rows)
