Thread: Help on analyzing performance problem.

I have a problem with a _very_ slow query.  I'm not sure how to understand
the explain output.  Can anyone tell from the following where I might look
for a bottleneck in the system?

Thank,
Travis

psql:test.sql:1: NOTICE:  QUERY PLAN:

Nested Loop  (cost=3.03..240.90 rows=4 width=124)
  ->  Nested Loop  (cost=3.03..150.16 rows=7 width=92)
        ->  Merge Join  (cost=3.03..3.63 rows=11 width=76)
              ->  Sort  (cost=1.52..1.52 rows=17 width=32)
                    ->  Seq Scan on oilday  (cost=0.00..1.17 rows=17
width=32)
              ->  Sort  (cost=1.52..1.52 rows=17 width=44)
                    ->  Seq Scan on oilday  (cost=0.00..1.17 rows=17
width=44)
        ->  Index Scan using well_pkey on well  (cost=0.00..13.45 rows=1
width=16)
              SubPlan
                ->  Materialize  (cost=11.44..11.44 rows=82 width=12)
                      ->  Nested Loop  (cost=0.00..11.44 rows=82 width=12)
                            ->  Seq Scan on usernames  (cost=0.00..1.03
rows=1 width=4)
                            ->  Index Scan using comp_index on well
(cost=0.00..10.21 rows=15 width=8)
  ->  Index Scan using well_pkey on well  (cost=0.00..13.45 rows=1 width=32)
        SubPlan
          ->  Materialize  (cost=11.44..11.44 rows=82 width=12)
                ->  Nested Loop  (cost=0.00..11.44 rows=82 width=12)
                      ->  Seq Scan on usernames  (cost=0.00..1.03 rows=1
width=4)
                      ->  Index Scan using comp_index on well
(cost=0.00..10.21 rows=15 width=8)

"Travis Bauer" <trbauer@cs.indiana.edu> writes:
> I have a problem with a _very_ slow query.  I'm not sure how to understand
> the explain output.

There's some basic info about EXPLAIN in the "understanding performance"
section of the user manual (at least today, it's at
http://www.postgresql.org/docs/postgres/c4962.htm).  The thing I usually
try to do first is understand how the estimated row counts (rows=N)
relate to reality.  For this you need to know how large the tables are
and how restrictive the WHERE clauses related to each table are, so that
you can figure out the true output row count for each part of the query
plan.

The EXPLAIN output alone is not too useful without seeing both the query
text and the context information about table sizes &etc.

            regards, tom lane