Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 1CD64475923
	for <pgsql-sql@postgresql.org>; Thu, 16 May 2002 17:28:20 -0400 (EDT)
Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250])
	by postgresql.org (Postfix) with ESMTP id 12202474E71
	for <pgsql-sql@postgresql.org>; Thu, 16 May 2002 17:28:14 -0400 (EDT)
Received: from [209.122.114.82] (HELO chocolate-mousse)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.5.9)
	with ESMTP id 1462227 for pgsql-sql@postgresql.org;
	Thu, 16 May 2002 14:28:14 -0700
Content-Type: text/plain;
  charset="us-ascii"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: How can I tell the performance difference?
Date: Thu, 16 May 2002 14:29:50 -0700
X-Mailer: KMail [version 1.4]
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200205161429.50058.josh@agliodbs.com>
X-Archive-Number: 200205/179
X-Sequence-Number: 7814

Folks,

I have a view which will be used very, very often in my database.  As such,=
 I=20
need to evaluate which of the two following structures is the best for that=
=20
view.  However, I can't easily figure out which of the two explain plans=20
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=3D2.86..3.10 rows=3D2 width=3D70)
  ->  Aggregate  (cost=3D2.86..3.10 rows=3D2 width=3D70)
        ->  Group  (cost=3D2.86..3.02 rows=3D16 width=3D70)
              ->  Sort  (cost=3D2.86..2.86 rows=3D16 width=3D70)
                    ->  Hash Join  (cost=3D1.20..2.54 rows=3D16 width=3D70)
                          ->  Seq Scan on juris_desc  (cost=3D0.00..1.08 ro=
ws=3D8=20
width=3D62)
                          ->  Hash  (cost=3D1.16..1.16 rows=3D16 width=3D8)
                                ->  Seq Scan on juris_tree  (cost=3D0.00..1=
.16=20
rows=3D16 width=3D8)

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=3D1.60..2.72 rows=3D1 width=3D74)
  ->  Seq Scan on juris_desc  (cost=3D0.00..1.08 rows=3D8 width=3D62)
  ->  Hash  (cost=3D1.60..1.60 rows=3D2 width=3D8)
        ->  Subquery Scan jt  (cost=3D1.48..1.60 rows=3D2 width=3D8)
              ->  Aggregate  (cost=3D1.48..1.60 rows=3D2 width=3D8)
                    ->  Group  (cost=3D1.48..1.52 rows=3D16 width=3D8)
                          ->  Sort  (cost=3D1.48..1.48 rows=3D16 width=3D8)
                                ->  Seq Scan on juris_tree  (cost=3D0.00..1=
.16=20
rows=3D16 width=3D8)

I'd very much like to evaluate this before the database goes into productio=
n.

Thanks!
--=20
-Josh Berkus