Обсуждение: How can I tell the performance difference?

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
Scanon 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 rnodeFROM 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.60rows=2 width=8)       ->  Subquery Scan jt  (cost=1.48..1.60 rows=2 width=8)             ->  Aggregate
(cost=1.48..1.60rows=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

Josh Berkus wrote:
> 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?
> 

I would use PHP (or Perl or something) to generate some sample data 
(i.e. a few hundred thousand records or so) and then use EXPLAIN ANALYZE 
(after VACUUM ANALYZE of course ;-) ), instead of trying to guess 
performance based on EXPLAIN output that won't be relevant once you have 
data.

Just my 2 cents . . .

Joe