From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Cole <cole.jeff@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re:
Date: Tue, 06 Mar 2007 11:40:53 -0500
Message-ID: <5644.1173199253@sss.pgh.pa.us> (raw)
In-Reply-To: <43756010-6EF6-4F91-B84E-E7D29599C3E8@gmail.com>
References: <9BAE31C9-93A6-48AE-AFED-3BB1947D13F1@gmail.com>
<25081.1173146092@sss.pgh.pa.us>
<43756010-6EF6-4F91-B84E-E7D29599C3E8@gmail.com>
Jeff Cole <cole.jeff@gmail.com> writes:
> Hi Tom, thanks for the response. Here are the pg_stats. I think I
> understand what the stats say, but I don't know what to conclude from
> them.
OK, the symptom_id row claims there are only 80 distinct values of
symptom_id in symptom_reports. This is a bit low (looks like the true
state of affairs is that all but 2 of the 108 entries of symptoms are
represented in symptom_reports), but it's not horridly off considering
that you're using the rather low default statistics_target. What
happens is that the planner expects that on average only 80 rows of the
inner join will need to be scanned to find a match for a given symptoms.id,
and this makes the nestloop look cheap. However, per your previous
EXPLAIN ANALYZE:
> -> Nested Loop IN Join (cost=149.05..586.26 rows=85 width=0) (actual time=54.517..3441.115 rows=106 loops=1)
> Join Filter: ("outer".id = "inner".symptom_id)
> -> Seq Scan on symptoms (cost=0.00..3.08 rows=108 width=4) (actual time=0.007..0.273 rows=108 loops=1)
> -> Hash IN Join (cost=149.05..603.90 rows=13074 width=4) (actual time=0.078..24.503 rows=3773 loops=108)
the *actual* average number of rows scanned is 3773. I'm not sure why
this should be --- is it possible that the distribution of keys in
symptom_reports is wildly uneven? This could happen if all of the
physically earlier rows in symptom_reports contain the same small set
of symptom_ids, but the stats don't seem to indicate such a skew.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cole.jeff@gmail.com
Subject: Re:
In-Reply-To: <5644.1173199253@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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