pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I tell the performance difference?
2+ messages / 2 participants
[nested] [flat]
* How can I tell the performance difference?
@ 2002-05-16 21:29 Josh Berkus <josh@agliodbs.com>
2002-05-16 22:22 ` Re: How can I tell the performance difference? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 2+ messages in thread
From: Josh Berkus @ 2002-05-16 21:29 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How can I tell the performance difference?
2002-05-16 21:29 How can I tell the performance difference? Josh Berkus <josh@agliodbs.com>
@ 2002-05-16 22:22 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 2+ messages in thread
From: Joe Conway @ 2002-05-16 22:22 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-16 22:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-16 21:29 How can I tell the performance difference? Josh Berkus <josh@agliodbs.com>
2002-05-16 22:22 ` Joe Conway <mail@joeconway.com>
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