Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group