Re: SQL Question

From: Todd Lewis <lewis-todd(at)sbcglobal(dot)net>
To: ketema(at)ketema(dot)net
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL Question
Date: 2006-02-10 05:42:50
Message-ID: 43EC27DA.2050903@sbcglobal.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(at)ketema(dot)net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Warren Murray 2006-02-10 13:06:52 Creating Text Files from Tables
Previous Message Ketema Harris 2006-02-10 02:11:59 Re: SQL Question