X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4922F11F7C5C for ; Sun, 23 Apr 2006 18:01:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 96630-02 for ; Sun, 23 Apr 2006 17:57:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.225]) by postgresql.org (Postfix) with ESMTP id 6ECDD11F6717 for ; Sun, 23 Apr 2006 17:57:45 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i32so757762wra for ; Sun, 23 Apr 2006 13:57:45 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:subject:date:organization:mime-version:content-type:content-transfer-encoding:x-mailer:x-mimeole:thread-index:message-id; b=TgJEei1NOTRhPYmfgR2lPlV+95lMy78rG6qsGzCJBS0VbLGYa5ikqtojsNXw6H/NNdh2e8qfqleC5xp0NQ5hfITgRDKqGIdA/RMD0upsRYbFk32wqLy6ohMX6O+yqznXXIr61nslGNhi7ai6BOP24DNeD4JjHDeATddJO2XkL2k= Received: by 10.54.115.20 with SMTP id n20mr179166wrc; Sun, 23 Apr 2006 13:57:44 -0700 (PDT) Received: from sbew318anc22 ( [200.157.135.150]) by mx.gmail.com with ESMTP id 12sm2925090wrl.2006.04.23.13.57.43; Sun, 23 Apr 2006 13:57:44 -0700 (PDT) From: "Bruno Almeida do Lago" To: Subject: GROUP BY Vs. Sub SELECT Date: Sun, 23 Apr 2006 17:57:40 -0300 Organization: G&P MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcZnGI8osE8PVGWPQpGtuc6NjVorpQ== Message-ID: <444bea48.5e66e367.75a4.0537@mx.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/515 X-Sequence-Number: 18599 Hi, I'm just getting familiar with EXPLAIN ANALYZE output, so I'd like to get some help to identify which one of the following queries would be better: teste=# EXPLAIN ANALYZE SELECT aa.avaliacao_id, MAX(aa.avaliacao_versao) AS avaliacao_versao, a.avaliacao_nome, aa.editar teste-# FROM teo01tb104_areas_avaliacoes aa, teo01tb201_avaliacoes a teste-# WHERE aa.avaliacao_id=10 teste-# AND aa.avaliacao_id=a.avaliacao_id teste-# GROUP BY aa.avaliacao_id, a.avaliacao_nome, aa.editar; QUERY PLAN ---------------------------------------------------------------------------- ----------------------------------------------------------------------- HashAggregate (cost=45.93..45.94 rows=1 width=52) (actual time=0.466..0.469 rows=1 loops=1) -> Nested Loop (cost=4.04..45.66 rows=27 width=52) (actual time=0.339..0.356 rows=1 loops=1) -> Bitmap Heap Scan on teo01tb201_avaliacoes a (cost=2.01..8.49 rows=3 width=47) (actual time=0.219..0.223 rows=1 loops=1) Recheck Cond: (avaliacao_id = 10) -> Bitmap Index Scan on teo01tb201_avaliacoes_pk (cost=0.00..2.01 rows=3 width=0) (actual time=0.166..0.166 rows=1 loops=1) Index Cond: (avaliacao_id = 10) -> Bitmap Heap Scan on teo01tb104_areas_avaliacoes aa (cost=2.03..12.30 rows=9 width=9) (actual time=0.060..0.066 rows=1 loops=1) Recheck Cond: (avaliacao_id = 10) -> Bitmap Index Scan on teo01tb104_areas_avaliacoes_pk (cost=0.00..2.03 rows=9 width=0) (actual time=0.040..0.040 rows=1 loops=1) Index Cond: (avaliacao_id = 10) Total runtime: 1.339 ms (11 rows) teste=# SELECT a.avaliacao_id, a.avaliacao_versao, a.avaliacao_nome, aa.editar teste-# FROM teo01tb201_avaliacoes a, teo01tb104_areas_avaliacoes aa teste-# WHERE a.avaliacao_id=10 teste-# AND a.avaliacao_versao=(SELECT MAX(avaliacao_versao) teste(# FROM teo01tb201_avaliacoes teste(# WHERE avaliacao_id=10) teste-# AND a.avaliacao_id=aa.avaliacao_id; avaliacao_id | avaliacao_versao | avaliacao_nome | editar --------------+------------------+----------------+-------- 10 | 1 | Teste | t (1 row) teste=# EXPLAIN ANALYZE SELECT a.avaliacao_id, a.avaliacao_versao, a.avaliacao_nome, aa.editar teste-# FROM teo01tb201_avaliacoes a, teo01tb104_areas_avaliacoes aa teste-# WHERE a.avaliacao_id=10 teste-# AND a.avaliacao_versao=(SELECT MAX(avaliacao_versao) teste(# FROM teo01tb201_avaliacoes teste(# WHERE avaliacao_id=10) teste-# AND a.avaliacao_id=aa.avaliacao_id; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------ Nested Loop (cost=6.20..22.38 rows=9 width=52) (actual time=0.573..0.596 rows=1 loops=1) InitPlan -> Result (cost=4.16..4.17 rows=1 width=0) (actual time=0.315..0.319 rows=1 loops=1) InitPlan -> Limit (cost=0.00..4.16 rows=1 width=4) (actual time=0.257..0.261 rows=1 loops=1) -> Index Scan Backward using teo01tb201_avaliacoes_pk on teo01tb201_avaliacoes (cost=0.00..12.48 rows=3 width=4) (actual time=0.245..0.245 rows=1 loops=1) Index Cond: (avaliacao_id = 10) Filter: (avaliacao_versao IS NOT NULL) -> Index Scan using teo01tb201_avaliacoes_pk on teo01tb201_avaliacoes a (cost=0.00..5.83 rows=1 width=51) (actual time=0.410..0.420 rows=1 loops=1) Index Cond: ((avaliacao_id = 10) AND (avaliacao_versao = $1)) -> Bitmap Heap Scan on teo01tb104_areas_avaliacoes aa (cost=2.03..12.30 rows=9 width=5) (actual time=0.110..0.114 rows=1 loops=1) Recheck Cond: (avaliacao_id = 10) -> Bitmap Index Scan on teo01tb104_areas_avaliacoes_pk (cost=0.00..2.03 rows=9 width=0) (actual time=0.074..0.074 rows=1 loops=1) Index Cond: (avaliacao_id = 10) Total runtime: 1.418 ms (15 rows) I think 2nd would be better, since when database grow up the GROUP BY may become too costly. Is that right? Regards, Bruno