From: Tom Lane <tgl@sss.pgh.pa.us>
To: El-Lotso <el.lotso@gmail.com>
Cc: pgsql-performance@postgresql.org
Cc: nikeow@yahoo.com
Subject: Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
Date: Tue, 11 Sep 2007 14:23:20 -0400
Message-ID: <15496.1189535000@sss.pgh.pa.us> (raw)
In-Reply-To: <1189530143.17184.21.camel@neuromancer.home.net>
References: <1189529868.17184.17.camel@neuromancer.home.net>
<1189530143.17184.21.camel@neuromancer.home.net>
El-Lotso <el.lotso@gmail.com> writes:
> sorry.. I sent this as I was about to go to bed and the explain analyse
> of the query w/ 4 tables joined per subquery came out.
It's those factor-of-1000 misestimates of the join sizes that are
killing you, eg this one:
> -> Hash Join (cost=249.61..512.56 rows=1 width=87) (actual time=15.139..32.858 rows=969 loops=1)
> Hash Cond: (((test_db.ts.id)::text = (test_db.d.id)::text) AND (test_db.ts.start_timestamp = test_db.trd.start_timestamp) AND (test_db.ts.ttype = test_db.trd.ttype))
> -> Seq Scan on ts (cost=0.00..226.44 rows=3244 width=40) (actual time=0.135..6.916 rows=3244 loops=1)
> -> Hash (cost=235.00..235.00 rows=835 width=47) (actual time=14.933..14.933 rows=1016 loops=1)
The single-row-result estimate persuades it to use a nestloop at the
next level up, and then when the output is actually 969 rows, that
means 969 executions of the other side of the upper join.
The two input size estimates are reasonably close to reality, so
the problem seems to be in the estimate of selectivity of the
join condition. First off, do you have up-to-date statistics
for all the columns being joined here? It might be that
increasing the statistics targets for those columns would help.
But what I'm a bit worried about is the idea that the join
conditions are correlated or even outright redundant; the
planner will not know that, and will make an unrealistic
estimate of their combined selectivity. If that's the
case, you might need to redesign the table schema to
eliminate the redundancy before you'll get good plans.
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, el.lotso@gmail.com, nikeow@yahoo.com
Subject: Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
In-Reply-To: <15496.1189535000@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