explain shows lots-o-preliminary sorting

CREATE VIEW course AS
SELECT
e.code AS educode,
e.name AS eduname,
t.code AS topiccode,
t.name AS topicname,
c.id,
c.topic,
c.code,
c.hrs,
c.num,
c.name,
c.descr,
c.created,
c.modified,
c.editor,
c.status
FROM
_edu e,
_topic t,
_course c
WHERE
c.topic = t.id -- maybe this should be swapped
AND
t.edu = e.id -- with this ??
;

psql=> explain select * from course;
NOTICE: QUERY PLAN:

Merge Join (cost=4.14..4.42 rows=8 width=238)
-> Sort (cost=2.63..2.63 rows=5 width=60)
-> Merge Join (cost=2.38..2.57 rows=5 width=60)
-> Sort (cost=1.30..1.30 rows=11 width=32)
-> Seq Scan on _topic (cost=0.00..1.11 rows=11 width=32)
-> Sort (cost=1.08..1.08 rows=4 width=28)
-> Seq Scan on _edu (cost=0.00..1.04 rows=4 width=28)
-> Sort (cost=1.52..1.52 rows=17 width=178)
-> Seq Scan on _course (cost=0.00..1.17 rows=17 width=178)

EXPLAIN

there's FOUR sort items mentioned there, and that's before the
merge join (results will not be sorted in any particular order).

which document will allay my 'holy cow is this ever gonna slow
down my database performance' concerns? (perhaps by saying that
sorting is just a myth, or by telling me how to get this puppy to
not sort at all -- and to use the indexes that i've defined for
all these joined fields...!)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'

will(at)serensoft(dot)com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Merge joins sorta need their input to be sorted.

will trillich wrote:
>
> CREATE VIEW course AS
> SELECT
> e.code AS educode,
> e.name AS eduname,
> t.code AS topiccode,
> t.name AS topicname,
> c.id,
> c.topic,
> c.code,
> c.hrs,
> c.num,
> c.name,
> c.descr,
> c.created,
> c.modified,
> c.editor,
> c.status
> FROM
> _edu e,
> _topic t,
> _course c
> WHERE
> c.topic = t.id -- maybe this should be swapped
> AND
> t.edu = e.id -- with this ??
> ;
>
> psql=> explain select * from course;
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=4.14..4.42 rows=8 width=238)
> -> Sort (cost=2.63..2.63 rows=5 width=60)
> -> Merge Join (cost=2.38..2.57 rows=5 width=60)
> -> Sort (cost=1.30..1.30 rows=11 width=32)
> -> Seq Scan on _topic (cost=0.00..1.11 rows=11 width=32)
> -> Sort (cost=1.08..1.08 rows=4 width=28)
> -> Seq Scan on _edu (cost=0.00..1.04 rows=4 width=28)
> -> Sort (cost=1.52..1.52 rows=17 width=178)
> -> Seq Scan on _course (cost=0.00..1.17 rows=17 width=178)
>
> EXPLAIN
>
> there's FOUR sort items mentioned there, and that's before the
> merge join (results will not be sorted in any particular order).
>
> which document will allay my 'holy cow is this ever gonna slow
> down my database performance' concerns? (perhaps by saying that
> sorting is just a myth, or by telling me how to get this puppy to
> not sort at all -- and to use the indexes that i've defined for
> all these joined fields...!)
>
> --
> It is always hazardous to ask "Why?" in science, but it is often
> interesting to do so just the same.
> -- Isaac Asimov, 'The Genetic Code'
>
> will(at)serensoft(dot)com
> http://newbieDoc.sourceforge.net/ -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com

On Wed, Mar 28, 2001 at 07:40:21PM -0500, Joseph Shraibman wrote:
> Merge joins sorta need their input to be sorted.

okay. what other kinds of joins are there that do NOT need
pre-sorting of subqueries?

more generally, what changes are possible to enhance performance
of select queries? forcing indexes vs. seqential scans, using
different joins that don't need sorting, etc. what's available?

and is there a /usr/share/doc/postgresql-doc/*/*/* file somewhere
that delineates all this?

> will trillich wrote:
> >
> > CREATE VIEW course AS
> > SELECT
> > e.code AS educode,
> > e.name AS eduname,
> > t.code AS topiccode,
> > t.name AS topicname,
> > c.name,
> > c.descr
> > FROM
> > _edu e,
> > _topic t,
> > _course c
> > WHERE
> > c.topic = t.id -- maybe this should be swapped
> > AND
> > t.edu = e.id -- with this ??
> > ;
> >
> > psql=> explain select * from course;
> > NOTICE: QUERY PLAN:
> >
> > Merge Join (cost=4.14..4.42 rows=8 width=238)
> > -> Sort (cost=2.63..2.63 rows=5 width=60)
> > -> Merge Join (cost=2.38..2.57 rows=5 width=60)
> > -> Sort (cost=1.30..1.30 rows=11 width=32)
> > -> Seq Scan on _topic (cost=0.00..1.11 rows=11 width=32)
> > -> Sort (cost=1.08..1.08 rows=4 width=28)
> > -> Seq Scan on _edu (cost=0.00..1.04 rows=4 width=28)
> > -> Sort (cost=1.52..1.52 rows=17 width=178)
> > -> Seq Scan on _course (cost=0.00..1.17 rows=17 width=178)
> >
> > EXPLAIN
> >
> > there's FOUR sort items mentioned there, and that's before the
> > merge join (results will not be sorted in any particular order).
> >
> > which document will allay my 'holy cow is this ever gonna slow
> > down my database performance' concerns? (perhaps by saying that
> > sorting is just a myth, or by telling me how to get this puppy to
> > not sort at all -- and to use the indexes that i've defined for
> > all these joined fields...!)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'

will(at)serensoft(dot)com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

On Wed, Mar 28, 2001 at 10:27:36PM -0600, will trillich wrote:

> more generally, what changes are possible to enhance performance
> of select queries? forcing indexes vs. seqential scans, using
> different joins that don't need sorting, etc. what's available?

You're not going to know what your queries' performance is until you
populate the database with plenty of data. Look at the query plan
below, and notice the "rows" figures that are listed. With 8 or 10
rows getting selected, it doesn't really matter which query plan gets
selected.

See the section on performance in the online PG manual. It's on
www.postgresql.org, in the user's lounge.

The real trick is to create artificial data that's sufficiently
similar to real data that you can reliable measure performance with
it.

Chris

> > will trillich wrote:
> > >
> > > psql=> explain select * from course;
> > > NOTICE: QUERY PLAN:
> > >
> > > Merge Join (cost=4.14..4.42 rows=8 width=238)
> > > -> Sort (cost=2.63..2.63 rows=5 width=60)
> > > -> Merge Join (cost=2.38..2.57 rows=5 width=60)
> > > -> Sort (cost=1.30..1.30 rows=11 width=32)
> > > -> Seq Scan on _topic (cost=0.00..1.11 rows=11 width=32)
> > > -> Sort (cost=1.08..1.08 rows=4 width=28)
> > > -> Seq Scan on _edu (cost=0.00..1.04 rows=4 width=28)
> > > -> Sort (cost=1.52..1.52 rows=17 width=178)
> > > -> Seq Scan on _course (cost=0.00..1.17 rows=17 width=178)
> > >
> > > EXPLAIN

--
chris(at)mt(dot)sri(dot)com -----------------------------------------------------
Chris Jones SRI International, Inc.
www.sri.com