How can I tell the performance difference?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: How can I tell the performance difference?
Date: 2002-05-16 21:29:50
Message-ID: 200205161429.50058.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Folks,

I have a view which will be used very, very often in my database. As such, I
need to evaluate which of the two following structures is the best for that
view. However, I can't easily figure out which of the two explain plans
looks more costly. Can anybody help?

view #1:
create view juris as
select juris_id, juris_name, juris_code, notes,
min(treeno) as lnode, max(treeno) as rnode
from juris_desc JOIN juris_tree USING (juris_id)
group by juris_id, juris_name, juris_code, notes;

EXPLAIN:
Subquery Scan juris (cost=2.86..3.10 rows=2 width=70)
-> Aggregate (cost=2.86..3.10 rows=2 width=70)
-> Group (cost=2.86..3.02 rows=16 width=70)
-> Sort (cost=2.86..2.86 rows=16 width=70)
-> Hash Join (cost=1.20..2.54 rows=16 width=70)
-> Seq Scan on juris_desc (cost=0.00..1.08 rows=8
width=62)
-> Hash (cost=1.16..1.16 rows=16 width=8)
-> Seq Scan on juris_tree (cost=0.00..1.16
rows=16 width=8)

View #2:
create view juris2 as
select juris_id, juris_name, juris_code, notes,
lnode, rnode
from juris_desc JOIN
(SELECT juris_id, min(treeno) as lnode, max(treeno) as rnode
FROM juris_tree GROUP BY juris_id)
jt USING (juris_id);

EXPLAIN
Hash Join (cost=1.60..2.72 rows=1 width=74)
-> Seq Scan on juris_desc (cost=0.00..1.08 rows=8 width=62)
-> Hash (cost=1.60..1.60 rows=2 width=8)
-> Subquery Scan jt (cost=1.48..1.60 rows=2 width=8)
-> Aggregate (cost=1.48..1.60 rows=2 width=8)
-> Group (cost=1.48..1.52 rows=16 width=8)
-> Sort (cost=1.48..1.48 rows=16 width=8)
-> Seq Scan on juris_tree (cost=0.00..1.16
rows=16 width=8)

I'd very much like to evaluate this before the database goes into production.

Thanks!
--
-Josh Berkus

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gregory Brauer 2002-05-16 21:54:21 Re: SQL over my head...
Previous Message Josh Berkus 2002-05-16 20:49:18 Re: Constraint problem