From: Bruno Almeida do Lago <teolupus@gmail.com>
To: pgsql-performance@postgresql.org
Subject: GROUP BY Vs. Sub SELECT
Date: Sun, 23 Apr 2006 17:57:40 -0300
Message-ID: <444bea48.5e66e367.75a4.0537@mx.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: teolupus@gmail.com
Subject: Re: GROUP BY Vs. Sub SELECT
In-Reply-To: <444bea48.5e66e367.75a4.0537@mx.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox