Thread: explain -> how to optimize?
here's an EXPLAIN on a VIEW which links a few tables:
=# explain SELECT * FROM RATING;
NOTICE: QUERY PLAN:
Merge Join (cost=11.18..11.51 rows=19 width=217)
-> Sort (cost=9.98..9.98 rows=19 width=189)
-> Merge Join (cost=9.24..9.58 rows=19 width=189)
-> Sort (cost=8.04..8.04 rows=19 width=161)
-> Hash Join (cost=4.44..7.64 rows=19 width=161)
-> Seq Scan on _rating r (cost=0.00..1.42 rows=42 width=45)
-> Hash (cost=4.42..4.42 rows=8 width=116)
-> Merge Join (cost=4.14..4.42 rows=8 width=116)
-> 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 t (cost=0.00..1.11 rows=11 width=32)
-> Sort (cost=1.08..1.08 rows=4 width=28)
-> Seq Scan on _edu e (cost=0.00..1.04 rows=4 width=28)
-> Sort (cost=1.52..1.52 rows=17 width=56)
-> Seq Scan on _course c (cost=0.00..1.17 rows=17 width=56)
-> Sort (cost=1.20..1.20 rows=8 width=28)
-> Seq Scan on _who f (cost=0.00..1.08 rows=8 width=28)
-> Sort (cost=1.20..1.20 rows=8 width=28)
-> Seq Scan on _who s (cost=0.00..1.08 rows=8 width=28)
EXPLAIN
okay, there's a ton of sorts and so forth in there that don't
seem to be needed; plus i'm sure i've got indexes on the
linked-to tables, so why the SEQ scans, i dunno.
how can i take this information and hunt down the slow stuff and
tweak it to perform more quickly?
--
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Wed, Mar 07, 2001 at 11:24:34AM -0600, will trillich wrote: > here's an EXPLAIN on a VIEW which links a few tables: Can you give us the definition of the VIEW? Also, what kinds of data are you dealing with? Stuff like the # of rows. Are the planner estimate's below reasonable? [...] > okay, there's a ton of sorts and so forth in there that don't > seem to be needed; plus i'm sure i've got indexes on the > linked-to tables, so why the SEQ scans, i dunno. It's using SEQ scans because it thinks they're faster than index scans -- in this case, is the planner incorrect? HTH, Neil -- Neil Conway <neilconway@home.com> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move. -- Douglas Adams
On Wed, Mar 07, 2001 at 06:39:30PM -0500, Neil Conway wrote:
> On Wed, Mar 07, 2001 at 11:24:34AM -0600, will trillich wrote:
> > here's an EXPLAIN on a VIEW which links a few tables:
>
> Can you give us the definition of the VIEW?
>
> Also, what kinds of data are you dealing with? Stuff like the
> # of rows. Are the planner estimate's below reasonable?
very tiny numbers to start with. no live data yet, only tinkering
with examples.
> [...]
> > okay, there's a ton of sorts and so forth in there that don't
> > seem to be needed; plus i'm sure i've got indexes on the
> > linked-to tables, so why the SEQ scans, i dunno.
>
> It's using SEQ scans because it thinks they're faster than index scans --
> in this case, is the planner incorrect?
the definition of the views and tables are rather involved,
so maybe i ought not post publicly and flood the list with
poorly-crafted flotsam...? does the planner change its tune for
larger tables?
--
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@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!