join vs. subquery

Hi -

This is based on a discussion I was having with neilc on IRC. He
suggested I post it here. Sorry for the length - I'm including
everything he requested.

I'm comparing the speeds of the following two queries. I was curious
why query 1 was faster than query 2:

query 1:
Select layer_number
FROM batch_report_index
WHERE device_id = (SELECT device_id FROM device_index WHERE device_name
='CP8M')
AND technology_id = (SELECT technology_id FROM technology_index WHERE
technology_name = 'G12');

query 2:
Select b.layer_number
FROM batch_report_index b, device_index d, technology_index t
WHERE b.device_id = d.device_id
AND b.technology_id = t.technology_id
AND d.device_name = 'CP8M'
AND t.technology_name = 'G12';

Here were my first runs:
(query 1 explain analyze)
Seq Scan on batch_report_index (cost=6.05..12370.66 rows=83 width=4)
(actual time=19.274..1903.110 rows=61416 loops=1)
Filter: ((device_id = $0) AND (technology_id = $1))
InitPlan
-> Index Scan using device_index_device_name_key on device_index
(cost=0.00..4.88 rows=1 width=4) (actual time=0.310..0.320 rows=1
loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Seq Scan on technology_index (cost=0.00..1.18 rows=1 width=4)
(actual time=0.117..0.149 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 1947.896 ms
(8 rows)

(query 2 explain analyze)
Hash Join (cost=6.06..12380.70 rows=46 width=4) (actual
time=35.509..2831.685 rows=61416 loops=1)
Hash Cond: ("outer".technology_id = "inner".technology_id)
-> Hash Join (cost=4.88..12375.87 rows=638 width=8) (actual
time=34.584..2448.862 rows=61416 loops=1)
Hash Cond: ("outer".device_id = "inner".device_id)
-> Seq Scan on batch_report_index b (cost=0.00..10182.74
rows=436374 width=12) (actual time=0.100..1373.085 rows=436374 loops=1)
-> Hash (cost=4.88..4.88 rows=1 width=4) (actual
time=0.635..0.635 rows=0 loops=1)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.505..0.520 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Hash (cost=1.18..1.18 rows=1 width=4) (actual time=0.348..0.348
rows=0 loops=1)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1
width=4) (actual time=0.198..0.239 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 2872.252 ms
(12 rows)

On neilc's suggestion, I did a vacuum analyze, then turned off hash
joins. Here's query 2, no hash joins:

(query 2 explain analyze)
Nested Loop (cost=0.00..15651.44 rows=46 width=4) (actual
time=22.079..2741.103 rows=61416 loops=1)
Join Filter: ("inner".technology_id = "outer".technology_id)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1 width=4)
(actual time=0.178..0.218 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
-> Nested Loop (cost=0.00..15642.29 rows=638 width=8) (actual
time=21.792..2530.470 rows=61416 loops=1)
Join Filter: ("inner".device_id = "outer".device_id)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.331..0.346 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Seq Scan on batch_report_index b (cost=0.00..10182.74
rows=436374 width=12) (actual time=0.070..1437.938 rows=436374 loops=1)
Total runtime: 2782.628 ms
(10 rows)

He then suggested I turn hash_joins back on and put an index on the
batch_report_table's device_id. Here's query 2 again:

(query 2 explain analyze)
Hash Join (cost=1.18..2389.06 rows=46 width=4) (actual
time=1.562..2473.554 rows=61416 loops=1)
Hash Cond: ("outer".technology_id = "inner".technology_id)
-> Nested Loop (cost=0.00..2384.24 rows=638 width=8) (actual
time=0.747..2140.160 rows=61416 loops=1)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.423..0.435 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Index Scan using b_r_device_index on batch_report_index b
(cost=0.00..2365.82 rows=1083 width=12) (actual time=0.288..1868.118
rows=61416 loops=1)
Index Cond: (b.device_id = "outer".device_id)
-> Hash (cost=1.18..1.18 rows=1 width=4) (actual time=0.359..0.359
rows=0 loops=1)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1
width=4) (actual time=0.198..0.237 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 2515.950 ms
(11 rows)

He then suggested I increase the statistics on batch_report_index & run
the query again. I "set statistics" for both
the device_id and technology_id column to 900, vacuum analyzed, and
re-ran the query (it's still slower than query
1 run after the same contortions ):

(query 2 explain analyze)
Hash Join (cost=1.18..1608.49 rows=46 width=4) (actual
time=1.437..1499.414 rows=61416 loops=1)
Hash Cond: ("outer".technology_id = "inner".technology_id)
-> Nested Loop (cost=0.00..1603.66 rows=638 width=8) (actual
time=0.613..1185.826 rows=61416 loops=1)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.246..0.259 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Index Scan using b_r_device_index on batch_report_index b
(cost=0.00..1589.93 rows=708 width=12) (actual time=0.324..928.888
rows=61416 loops=1)
Index Cond: (b.device_id = "outer".device_id)
-> Hash (cost=1.18..1.18 rows=1 width=4) (actual time=0.358..0.358
rows=0 loops=1)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1
width=4) (actual time=0.196..0.238 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 1538.302 ms

At this point, he said "send it to the -perform mailing list". So here
I am.

The relevant table schemas as of right now:
reg=# \d batch_report_index
Table "public.batch_report_index"
Column | Type |
Modifiers
-----------------+------------------------
+---------------------------------------------------------------------
batch_report_id | integer | not null default
nextval('"batch_report__batch_report__seq"'::text)
lot | character varying(16) |
tool_id | integer | not null
technology_id | integer | not null
device_id | integer | not null
reticle_id | integer | not null
layer_id | integer | not null
layer_number | integer | not null
image_id | integer |
start_date | date |
start_time | time without time zone |
stop_date | date |
stop_time | time without time zone |
in_system | boolean | default false
Indexes:
"batch_report_index_pkey" primary key, btree (batch_report_id)
"b_r_device_index" btree (device_id)
"batch_report_stop_date_indexing" btree (stop_date)
"batch_report_tool_id_index" btree (tool_id)

reg=# \d technology_index
Table "public.technology_index"
Column | Type | Modifiers
-----------------+---------
+---------------------------------------------------------------------
technology_id | integer | not null default
nextval('"technology_in_technology_id_seq"'::text)
technology_name | text | not null
Indexes:
"technology_index_pkey" primary key, btree (technology_id)
"technology_in_technology_na_key" unique, btree (technology_name)

reg=# \d device_index
Table "public.device_index"
Column | Type | Modifiers
-------------+---------
+----------------------------------------------------------------
device_id | integer | not null default
nextval('"device_index_device_id_seq"'::text)
device_name | text | not null
Indexes:
"device_index_pkey" primary key, btree (device_id)
"device_index_device_name_key" unique, btree (device_name)

Thanks for the great work y'all do!