Thanks I eventually came to the same conclusion as your first
suggested answer, but don't really understand the second one. Can you
explain why the second one works? It is faster. I looked at the
query plans and the second does seem like it would be much faster.
First:
Sort (cost=99164.54..99165.04 rows=200 width=2)
Sort Key: personid
-> HashAggregate (cost=82905.40..99156.90 rows=200 width=2)
-> Seq Scan on test a (cost=0.00..82885.00 rows=2040 width=2)
SubPlan
-> Aggregate (cost=40.61..40.62 rows=1 width=0)
-> Seq Scan on test b (cost=0.00..40.60 rows=1 width=0)
Filter: ((typeid = 3) AND (personid = $0))
SubPlan
-> Aggregate (cost=40.61..40.62 rows=1 width=0)
-> Seq Scan on test b (cost=0.00..40.60 rows=1 width=0)
Filter: ((typeid = 3) AND (personid = $0))
Second:
Sort (cost=158.59..159.09 rows=200 width=4)
Sort Key: ev.personid
-> HashAggregate (cost=142.45..150.95 rows=200 width=4)
-> Hash Left Join (cost=45.65..106.75 rows=2040 width=4)
Hash Cond: (("outer".personid = "inner".personid) AND
("outer".rowid = "inner".rowid))
-> Seq Scan on test ev (cost=0.00..30.40 rows=2040 width=4)
-> Hash (cost=35.50..35.50 rows=2030 width=4)
-> Seq Scan on test ev2 (cost=0.00..35.50
rows=2030 width=4)
Filter: (typeid <> 3)
Ketema J. Harris
www.ketema.net
ketema@ketema.net
