From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kneringer, Armin <Armin.Kneringer@fabasoft.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: potential performance gain by query planner optimization
Date: Tue, 27 Jul 2010 14:25:25 -0400
Message-ID: <10770.1280255125@sss.pgh.pa.us> (raw)
In-Reply-To: <12F45C8182386842930DCE2051B2AA601AD60AC1@fabamailserver.fabagl.fabasoft.com>
References: <12F45C8182386842930DCE2051B2AA601AD60AC1@fabamailserver.fabagl.fabasoft.com>
"Kneringer, Armin" <Armin.Kneringer@fabasoft.com> writes:
> I think I found a potential performance gain if the query planner would be optimized. All Tests has been performed with 8.4.1 (and earlier versions) on CentOS 5.3 (x64)
> The following query will run on my database (~250 GB) for ca. 1600 seconds and the sort will result in a disk merge deploying ca. 200 GB of data to the local disk (ca. 180.000 tmp-files)
What have you got work_mem set to? It looks like you must be using an
unreasonably large value, else the planner wouldn't have tried to use a
hash join here:
> -> Hash (cost=11917516.57..11917516.57 rows=55006045159 width=16)
> -> Nested Loop (cost=0.00..11917516.57 rows=55006045159 width=16)
> -> Seq Scan on atdateval t5 (cost=0.00...294152.40 rows=1859934 width=12)
> Filter: (attrid = 281479288456447::bigint)
> -> Index Scan using ind_ataggval on ataggval q1_1 (cost=0.00..6.20 rows=4 width=12)
> Index Cond: ((q1_1.attrid = 281479288456451::bigint) AND (q1_1.aggval = t5.aggrid))
> Filter: (q1_1.aggrid = 0)
Also, please try something newer than 8.4.1 --- this might be some
already-fixed bug.
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, Armin.Kneringer@fabasoft.com
Subject: Re: potential performance gain by query planner optimization
In-Reply-To: <10770.1280255125@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