How can I tell the performance difference?
От | Josh Berkus |
---|---|
Тема | How can I tell the performance difference? |
Дата | |
Msg-id | 200205161429.50058.josh@agliodbs.com обсуждение исходный текст |
Список | 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 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
В списке pgsql-sql по дате отправления: