Re: explain shows lots-o-preliminary sorting

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