Skip site navigation (1) Skip section navigation (2)

Re: potential performance gain by query planner optimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kneringer, Armin" <Armin(dot)Kneringer(at)fabasoft(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: potential performance gain by query planner optimization
Date: 2010-07-27 18:25:25
Message-ID: 10770.1280255125@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Kneringer, Armin" <Armin(dot)Kneringer(at)fabasoft(dot)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

In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-07-27 18:32:18
Subject: Re: Linux Filesystems again - Ubuntu this time
Previous:From: Whit ArmstrongDate: 2010-07-27 18:20:20
Subject: Re: Linux Filesystems again - Ubuntu this time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group