Short answer the first query is in essence performing 3 queries to group
the data, you take a big performance hit having the select within a
select. In most cases you can drop the select within a select down to
the where section, either by a sub select or in this case a
relationship. Counting elements based upon a relationship that are true
is a lot quicker.
If you were dealing with a larger data set (say 1 million records) you
would see a real big difference where the 1st query could take hours or
never complete because it has sucked up all database resources. The
second one would still complete within minutes.
Ketema Harris wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>