From: Greg Stark <gsstark@mit.edu>
To: Dirk Lutzebaeck <lutzeb@aeccom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query produces 1 GB temp file
Date: 05 Feb 2005 15:22:52 -0500
Message-ID: <87y8e2kb8j.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20050205182117.0125074@mail2.aeccom.com>
References: <20050205182117.0125074@mail2.aeccom.com>
Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
> Below is the query and results for EXPLAIN and EXPLAIN ANALYZE. All
> tables have been analyzed before.
Really? A lot of the estimates are very far off. If you really just analyzed
these tables immediately prior to the query then perhaps you should try
raising the statistics target on spec and co. Or is the problem that there's a
correlation between those two columns?
> -> Nested Loop (cost=0.00..8346.73 rows=3 width=1361) (actual time=34.104..18016.005 rows=703677 loops=1)
> -> Nested Loop (cost=0.00..5757.17 rows=17 width=51) (actual time=0.467..3216.342 rows=48563 loops=1)
> -> Nested Loop (cost=0.00..5606.39 rows=30 width=42) (actual time=0.381..1677.014 rows=48563 loops=1)
> -> Index Scan using es_sc_index on es (cost=0.00..847.71 rows=301 width=8) (actual time=0.184..46.519 rows=5863 loops=1)
> Index Cond: ((spec = 122293729) AND (co = 117305223::oid))
The root of your problem,. The optimizer is off by a factor of 20. It thinks
these two columns are much more selective than they are.
> -> Index Scan using bi_env_index on bi (cost=0.00..15.80 rows=1 width=42) (actual time=0.052..0.218 rows=8 loops=5863)
> Index Cond: ("outer".en = bi.en)
> Filter: ((rc = 130170467::oid) AND (co = 117305223::oid) AND (hide = false))
It also thinks these three columns are much more selective than they are.
How accurate are its estimates if you just do these?
explain analyze select * from es where spec = 122293729
explain analyze select * from es where co = 117305223::oid
explain analyze select * from bi where rc = 130170467::oid
explain analyze select * from bi where co = 117305223
explain analyze select * from bi where hide = false
If they're individually accurate then you've run into the familiar problem of
needing cross-column statistics. If they're individually inaccurate then you
should try raising the targets on those columns with:
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column SET STATISTICS integer
and reanalyzing.
Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
> Can some please explain why the temp file is so huge? I understand
> there are a lot of rows.
Well that I can't explain. 22k rows of width 1361 doesn't sound so big to me
either. The temporary table does need to store three copies of the records at
a given time, but still it sounds like an awful lot.
--
greg
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: gsstark@mit.edu, lutzeb@aeccom.com
Subject: Re: query produces 1 GB temp file
In-Reply-To: <87y8e2kb8j.fsf@stark.xeocode.com>
* 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