From: Mikkel Lauritsen <renard@tala.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Reasons for choosing one execution plan over another?
Date: Thu, 12 Sep 2013 15:29:28 +0200
Message-ID: <0668ee7d10f57c03111024f772e12083@localhost> (raw)
In-Reply-To: <bf5b44d979fb06ba3eda79042d19dbea@localhost>
References: <bf5b44d979fb06ba3eda79042d19dbea@localhost>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I wrote:
--- snip ---
> So - does anybody with enough insight in the planner know if it sounds
> likely that it would choose the given plans in these two cases, or if
> it's more likely that I have a tuning problem that leads to bad
> planning?
Duh. It suddenly dawned on me that I need to look closer at the plans...
The big difference in the estimated and actual row count in lines like
-> Nested Loop (cost=0.00..250.78 rows=338 width=47) (actual
time=0.100..189.676 rows=187012 loops=1)
indicates that the planner is somehow mislead by the statistics on (at
least) one of the tables, right? Any suggestions as to how I go about
investigating that further?
One thing here that is slightly confusing is the relationship between
the estimated row count of 169 in the outer loop and 6059 in the last
index scan in the partial plan below. How do they relate to each other?
-> Nested Loop (cost=0.00..452.10 rows=169 width=47) (actual
time=0.088..41.244 rows=32863 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=39) (actual
time=0.031..0.035 rows=1 loops=1)
-> Index Scan using i_c_id on i (cost=0.00..8.27 rows=1
width=39) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (c = 'bar'::text)
-> Index Scan using a_i_id_idx on a (cost=0.00..8.27 rows=1
width=78) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (i_id = i.id)
-> Index Scan using x_a_id_idx on x (cost=0.00..374.95 rows=6059
width=86) (actual time=0.055..27.219 rows=32863 loops=1)
Index Cond: (a_id = a.id)
Best regards & thanks,
Mikkel Lauritsen
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: renard@tala.dk
Subject: Re: Reasons for choosing one execution plan over another?
In-Reply-To: <0668ee7d10f57c03111024f772e12083@localhost>
* 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