pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMajor performance problem after upgrade from 8.3 to 8.4
39+ messages / 7 participants
[nested] [flat]
* Major performance problem after upgrade from 8.3 to 8.4
@ 2010-08-30 06:20 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:00 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 2 replies; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 06:20 UTC (permalink / raw)
To: pgsql-performance
Hello,
I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but
I'm having major performance problems with a query with many left joins.
Problem is that costs are now very, very, very high (was ok in 8.3).
Analyze has been done. Indexes are of course there.
-> Merge Left Join
(cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00
rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400
width=16)
Merge Cond: (l.id = d2000903.fk_id)
Details with execution plan can be found at:
http://www.wiesinger.com/tmp/pg_perf_84.txt
I know that the data model is key/value pairs but it worked well in 8.3. I
need this flexibility.
Any ideas?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-08-30 07:00 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:25 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
1 sibling, 1 reply; 39+ messages in thread
From: Scott Marlowe @ 2010-08-30 07:00 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: pgsql-performance
On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
> Hello,
>
> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but I'm
> having major performance problems with a query with many left joins. Problem
> is that costs are now very, very, very high (was ok in 8.3). Analyze has
> been done. Indexes are of course there.
>
> -> Merge Left Join
> (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00
> rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400
> width=16)
> Merge Cond: (l.id = d2000903.fk_id)
Wow! Other than an incredibly high cost AND row estimate, was the
query plan the same on 8.3 or different?
> Details with execution plan can be found at:
> http://www.wiesinger.com/tmp/pg_perf_84.txt
What's up with the "(actual time=.. rows= loops=) " in the explain analyze?
> I know that the data model is key/value pairs but it worked well in 8.3. I
> need this flexibility.
>
> Any ideas?
Not really. I would like an explain analyze from both 8.3 and 8.4.
Are they tuned the same, things like work mem and default stats
target?
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:00 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-08-30 07:25 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 07:25 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
On Mon, 30 Aug 2010, Scott Marlowe wrote:
> On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> Hello,
>>
>> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but I'm
>> having major performance problems with a query with many left joins. Problem
>> is that costs are now very, very, very high (was ok in 8.3). Analyze has
>> been done. Indexes are of course there.
>>
>> -> Merge Left Join
>> (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00
>> rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400
>> width=16)
>> Merge Cond: (l.id = d2000903.fk_id)
>
> Wow! Other than an incredibly high cost AND row estimate, was the
> query plan the same on 8.3 or different?
>
>> Details with execution plan can be found at:
>> http://www.wiesinger.com/tmp/pg_perf_84.txt
>
> What's up with the "(actual time=.. rows= loops=) " in the explain analyze?
What do you mean exactly? missing?
I did it not with psql but with a GUI program.
>
>> I know that the data model is key/value pairs but it worked well in 8.3. I
>> need this flexibility.
>>
>> Any ideas?
>
> Not really. I would like an explain analyze from both 8.3 and 8.4.
> Are they tuned the same, things like work mem and default stats
> target?
I don't have a 8.3 version running anymore. But I'm havin an OLD version
of a nearly exactly query plan (The sort was missing due to performance
issues and it done now in a view, maybe also some more JOINS are added,
but all that doesn't have impacts on the basic principle of the query
plan):
http://www.wiesinger.com/tmp/pg_perf.txt
Tuning: Yes, on same machine with same parameters (manual diff on old
config and added manually the parameters again).
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:00 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:25 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-08-30 07:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:56 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Scott Marlowe @ 2010-08-30 07:34 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: pgsql-performance
On Mon, Aug 30, 2010 at 1:25 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
> On Mon, 30 Aug 2010, Scott Marlowe wrote:
>
>> On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger <lists@wiesinger.com>
>> wrote:
>>>
>>> Hello,
>>>
>>> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but
>>> I'm
>>> having major performance problems with a query with many left joins.
>>> Problem
>>> is that costs are now very, very, very high (was ok in 8.3). Analyze has
>>> been done. Indexes are of course there.
>>>
>>> -> Merge Left Join
>>>
>>> (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00
>>>
>>> rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400
>>> width=16)
>>> Merge Cond: (l.id = d2000903.fk_id)
>>
>> Wow! Other than an incredibly high cost AND row estimate, was the
>> query plan the same on 8.3 or different?
>>
>>> Details with execution plan can be found at:
>>> http://www.wiesinger.com/tmp/pg_perf_84.txt
>>
>> What's up with the "(actual time=.. rows= loops=) " in the explain
>> analyze?
>
> What do you mean exactly? missing?
> I did it not with psql but with a GUI program.
Nevermind, that was an artifact at http://explain.depesz.com/s/KyU not
your fault. Sorry.
>>> I know that the data model is key/value pairs but it worked well in 8.3.
>>> I
>>> need this flexibility.
>>>
>>> Any ideas?
>>
>> Not really. I would like an explain analyze from both 8.3 and 8.4.
>> Are they tuned the same, things like work mem and default stats
>> target?
>
> I don't have a 8.3 version running anymore. But I'm havin an OLD version of
> a nearly exactly query plan (The sort was missing due to performance issues
> and it done now in a view, maybe also some more JOINS are added, but all
> that doesn't have impacts on the basic principle of the query plan):
> http://www.wiesinger.com/tmp/pg_perf.txt
>
> Tuning: Yes, on same machine with same parameters (manual diff on old config
> and added manually the parameters again).
How long does the query take to run in 8.4? Do you have an explain
analyze of that? I'm still thinking that some change in the query
planner might be seeing all those left joins and coming up with some
non-linear value for row estimation. What's default stats target set
to in that db?
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:00 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:25 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-08-30 07:56 ` Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 0 replies; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 07:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
On Mon, 30 Aug 2010, Scott Marlowe wrote:
> On Mon, Aug 30, 2010 at 1:25 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> On Mon, 30 Aug 2010, Scott Marlowe wrote:
>>
>>> On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger <lists@wiesinger.com>
>>> wrote:
>>>>
>>>> Hello,
>>>>
>>>> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but
>>>> I'm
>>>> having major performance problems with a query with many left joins.
>>>> Problem
>>>> is that costs are now very, very, very high (was ok in 8.3). Analyze has
>>>> been done. Indexes are of course there.
>>>>
>>>> -> Merge Left Join
>>>>
>>>> (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00
>>>>
>>>> rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400
>>>> width=16)
>>>> Merge Cond: (l.id = d2000903.fk_id)
>>>
>>> Wow! Other than an incredibly high cost AND row estimate, was the
>>> query plan the same on 8.3 or different?
>>>
>>>> Details with execution plan can be found at:
>>>> http://www.wiesinger.com/tmp/pg_perf_84.txt
>>>
>>> What's up with the "(actual time=.. rows= loops=) " in the explain
>>> analyze?
>>
>> What do you mean exactly? missing?
>> I did it not with psql but with a GUI program.
>
> Nevermind, that was an artifact at http://explain.depesz.com/s/KyU not
> your fault. Sorry.
>
>>>> I know that the data model is key/value pairs but it worked well in 8.3.
>>>> I
>>>> need this flexibility.
>>>>
>>>> Any ideas?
>>>
>>> Not really. I would like an explain analyze from both 8.3 and 8.4.
>>> Are they tuned the same, things like work mem and default stats
>>> target?
>>
>> I don't have a 8.3 version running anymore. But I'm havin an OLD version of
>> a nearly exactly query plan (The sort was missing due to performance issues
>> and it done now in a view, maybe also some more JOINS are added, but all
>> that doesn't have impacts on the basic principle of the query plan):
>> http://www.wiesinger.com/tmp/pg_perf.txt
>>
>> Tuning: Yes, on same machine with same parameters (manual diff on old config
>> and added manually the parameters again).
>
> How long does the query take to run in 8.4? Do you have an explain
> analyze of that? I'm still thinking that some change in the query
> planner might be seeing all those left joins and coming up with some
> non-linear value for row estimation. What's default stats target set
> to in that db?
In config, default values:
#default_statistics_target = 100 # range 1-10000
How can I find that out?
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-08-30 07:22 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:48 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
1 sibling, 2 replies; 39+ messages in thread
From: Andreas Kretschmer @ 2010-08-30 07:22 UTC (permalink / raw)
To: pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> wrote:
> I know that the data model is key/value pairs but it worked well in 8.3.
> I need this flexibility.
>
> Any ideas?
If i understand the query correctly it's a pivot-table, right?
If yes, and if i where you, i would try to rewrite this query, to
something like:
select
timestamp,
sum (case when keyid = 1 then value else 0 end) as Raumsolltemperatur,
...
from
log
group by
timestamp;
Assuming you can read a german text:
http://www.pg-forum.de/h-ufig-gestellte-fragen-faq/4067-faq-zeilen-zu-spalten.html
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
@ 2010-08-30 07:34 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
1 sibling, 1 reply; 39+ messages in thread
From: Pavel Stehule @ 2010-08-30 07:34 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
Hello
2010/8/30 Andreas Kretschmer <akretschmer@spamfence.net>:
> Gerhard Wiesinger <lists@wiesinger.com> wrote:
>
>> I know that the data model is key/value pairs but it worked well in 8.3.
>> I need this flexibility.
>>
>> Any ideas?
>
> If i understand the query correctly it's a pivot-table, right?
>
no - it's just EAV table on very large data :(
Regards
Pavel Stehule
> If yes, and if i where you, i would try to rewrite this query, to
> something like:
>
> select
> timestamp,
> sum (case when keyid = 1 then value else 0 end) as Raumsolltemperatur,
> ...
> from
> log
> group by
> timestamp;
>
>
> Assuming you can read a german text:
> http://www.pg-forum.de/h-ufig-gestellte-fragen-faq/4067-faq-zeilen-zu-spalten.html
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-08-30 07:58 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 07:58 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Mon, 30 Aug 2010, Pavel Stehule wrote:
> Hello
>
> 2010/8/30 Andreas Kretschmer <akretschmer@spamfence.net>:
>> Gerhard Wiesinger <lists@wiesinger.com> wrote:
>>
>>> I know that the data model is key/value pairs but it worked well in 8.3.
>>> I need this flexibility.
>>>
>>> Any ideas?
>>
>> If i understand the query correctly it's a pivot-table, right?
>>
>
> no - it's just EAV table on very large data :(
Yes, it is an EAV table, but with query space comparable low (Max. 1 day
out of years, typically 5mins out of years).
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-08-30 08:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Pavel Stehule @ 2010-08-30 08:17 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
2010/8/30 Gerhard Wiesinger <lists@wiesinger.com>:
> On Mon, 30 Aug 2010, Pavel Stehule wrote:
>
>> Hello
>>
>> 2010/8/30 Andreas Kretschmer <akretschmer@spamfence.net>:
>>>
>>> Gerhard Wiesinger <lists@wiesinger.com> wrote:
>>>
>>>> I know that the data model is key/value pairs but it worked well in 8.3.
>>>> I need this flexibility.
>>>>
>>>> Any ideas?
>>>
>>> If i understand the query correctly it's a pivot-table, right?
>>>
>>
>> no - it's just EAV table on very large data :(
>
> Yes, it is an EAV table, but with query space comparable low (Max. 1 day out
> of years, typically 5mins out of years).
>
it is irelevant - there are repeated seq scans - so you need a
partitioning or classic table - maybe materialized views can help
Pavel
> Thnx.
>
> Ciao,
> Gerhard
>
> --
> http://www.wiesinger.com/
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-08-30 16:11 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 16:11 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Mon, 30 Aug 2010, Pavel Stehule wrote:
> 2010/8/30 Gerhard Wiesinger <lists@wiesinger.com>:
>> On Mon, 30 Aug 2010, Pavel Stehule wrote:
>>
>>> Hello
>>>
>>> 2010/8/30 Andreas Kretschmer <akretschmer@spamfence.net>:
>>>>
>>>> Gerhard Wiesinger <lists@wiesinger.com> wrote:
>>>>
>>>>> I know that the data model is key/value pairs but it worked well in 8.3.
>>>>> I need this flexibility.
>>>>>
>>>>> Any ideas?
>>>>
>>>> If i understand the query correctly it's a pivot-table, right?
>>>>
>>>
>>> no - it's just EAV table on very large data :(
>>
>> Yes, it is an EAV table, but with query space comparable low (Max. 1 day out
>> of years, typically 5mins out of years).
>>
>
> it is irelevant - there are repeated seq scans - so you need a
> partitioning or classic table - maybe materialized views can help
I know the drawbacks of an EAV design but I don't want to discuss that. I
want to discuss the major performance decrease of PostgreSQL 8.3
(performance was ok) to PostgreSQL 8.4 (performance is NOT ok).
Any further ideas how I can track this down?
Can someone explain the difference in query plan from an optimizer point
of view?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-08-30 16:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-08-30 16:22 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> I know the drawbacks of an EAV design but I don't want to discuss that. I
> want to discuss the major performance decrease of PostgreSQL 8.3
> (performance was ok) to PostgreSQL 8.4 (performance is NOT ok).
> Any further ideas how I can track this down?
> Can someone explain the difference in query plan from an optimizer point
> of view?
Since you haven't shown us the 8.3 plan, it's kind of hard to speculate ;-)
One thing that jumped out at me was that 8.4 appears to be expecting
multiple matches in each of the left-joined tables, which is why the
total rowcount estimate balloons so fast. I rather imagine that you are
expecting at most one match in reality, else the query isn't going to
behave nicely. Is this correct? Are you *sure* you analyzed all these
tables? And if that is how the data looks, where is the actual
performance problem? A bad rowcount estimate isn't in itself going
to kill you.
FWIW, in a similar albeit toy example, I don't see any difference
between the 8.3 and 8.4 plans or cost estimates.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-08-30 16:45 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 16:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Mon, 30 Aug 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> I know the drawbacks of an EAV design but I don't want to discuss that. I
>> want to discuss the major performance decrease of PostgreSQL 8.3
>> (performance was ok) to PostgreSQL 8.4 (performance is NOT ok).
>
>> Any further ideas how I can track this down?
>> Can someone explain the difference in query plan from an optimizer point
>> of view?
>
> Since you haven't shown us the 8.3 plan, it's kind of hard to speculate ;-)
>
> One thing that jumped out at me was that 8.4 appears to be expecting
> multiple matches in each of the left-joined tables, which is why the
> total rowcount estimate balloons so fast. I rather imagine that you are
> expecting at most one match in reality, else the query isn't going to
> behave nicely. Is this correct? Are you *sure* you analyzed all these
> tables? And if that is how the data looks, where is the actual
> performance problem? A bad rowcount estimate isn't in itself going
> to kill you.
>
> FWIW, in a similar albeit toy example, I don't see any difference
> between the 8.3 and 8.4 plans or cost estimates.
Yes, I'm expecting only one match in reality and I thing PostgreSQL should
also know that from table definition and constraints. Long answer below.
Query doesn't "end" in PostgreSQL.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-08-30 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-08-30 16:49 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> BTW: I have the old data setup. /var/lib/pgsql-old. Is there a fast setup
> with old version on different TCP port possible to compare query plans?
You'll need to reinstall the old executables. If you put the new
executables in the same directories, it's not going to be easy to
run both in parallel. If you didn't, then you just need to start
the old postmaster using a different port number.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-02 05:53 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-02 05:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Mon, 30 Aug 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> BTW: I have the old data setup. /var/lib/pgsql-old. Is there a fast setup
>> with old version on different TCP port possible to compare query plans?
>
> You'll need to reinstall the old executables. If you put the new
> executables in the same directories, it's not going to be easy to
> run both in parallel. If you didn't, then you just need to start
> the old postmaster using a different port number.
>
I tried to get 8.3.11 ready again:
# Problems with timezone on startup (Default not found)
./configure --with-system-tzdata=/usr/share
cp ./src/backend/postgres /bin/postgres-8.3.11
su -l postgres -c "/bin/postgres-8.3.11 -p 54321 -D
/var/lib/pgsql-old/data &" >> /var/lib/pgsql-old/pgstartup.log 2>&1 < /dev/null
Problem is that PostgreSQL doesn't listen and take much CPU and also disk
I/O. 8.3 was shut down cleanly. 8.4 runs in parallel. Are there any
problems with shared buffer conflicts?
PID USER PR NI VIRT SWAP RES CODE DATA SHR S %CPU %MEM TIME+ COMMAND
6997 postgres 20 0 113m 112m 1236 3688 1064 712 D 38.7 0.0 0:45.43 /bin/postgres-8.3.11 -p 54321 -D /var/lib/pgsql-old/data
Any ideas?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-02 13:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 05:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 2 replies; 39+ messages in thread
From: Tom Lane @ 2010-09-02 13:14 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> Problem is that PostgreSQL doesn't listen and take much CPU and also disk
> I/O. 8.3 was shut down cleanly.
Hm, you sure about that? What's in the postmaster log?
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 05:03 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 05:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Thu, 2 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> Problem is that PostgreSQL doesn't listen and take much CPU and also disk
>> I/O. 8.3 was shut down cleanly.
>
> Hm, you sure about that? What's in the postmaster log?
That's the strange thing: I don't have anything in stdout/stderr log and
in pg_log directory and even in syslog.
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-03 14:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-09-03 14:14 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> On Thu, 2 Sep 2010, Tom Lane wrote:
>> Hm, you sure about that? What's in the postmaster log?
> That's the strange thing: I don't have anything in stdout/stderr log and
> in pg_log directory and even in syslog.
Not even in that pgstartup.log file you sent stderr to?
I have seen cases before where Postgres couldn't log anything because of
SELinux. If this is a Red Hat based system, look in the kernel log for
AVC messages. If you see any, then SELinux is probably blocking things
because of the nonstandard directory locations. Turning it off
temporarily would be the easiest fix, though relabeling the files would
be a better one.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 16:38 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 16:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Fri, 3 Sep 2010, Tom Lane wrote:
> Not even in that pgstartup.log file you sent stderr to?
>
> I have seen cases before where Postgres couldn't log anything because of
> SELinux. If this is a Red Hat based system, look in the kernel log for
> AVC messages. If you see any, then SELinux is probably blocking things
> because of the nonstandard directory locations. Turning it off
> temporarily would be the easiest fix, though relabeling the files would
> be a better one.
SELinux is already disabled.
cat /etc/selinux/config | grep -v "^#"|grep -v "^$"
SELINUXTYPE=targeted
SELINUX=disabled
Yes, also the redirected log file is empty. Also kernel log is empty.
I tried to redirect it to a different, new file, on startup I get nothing,
after killing it I get:
2010-09-03 16:35:39.177 GMT [2149] @/: LOG: could not stat "/usr/share/doc/xalan-j2-manual-2.7.0/apidocs": No such file or directory
Any ideas?
BTW: Shared memory can't be any issue?
Ciao,
Gerhard
-- http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-03 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-09-03 16:47 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> Not even in that pgstartup.log file you sent stderr to?
> Yes, also the redirected log file is empty. Also kernel log is empty.
Huh. Try strace'ing the process to see what it's doing.
> BTW: Shared memory can't be any issue?
If you're not getting any log messages at all, you've got worse problems
than shared memory.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 16:57 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 16:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Fri, 3 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> On Fri, 3 Sep 2010, Tom Lane wrote:
>>> Not even in that pgstartup.log file you sent stderr to?
>
>> Yes, also the redirected log file is empty. Also kernel log is empty.
>
> Huh. Try strace'ing the process to see what it's doing.
It tries to find something in /usr/share/ ...
Ok, I think from the compile time options:
./configure --with-system-tzdata=/usr/share
Previously I had problems with the timezone on startup (Default not
found) so I tried to set the directory.
Maybe the timezone thing (it looks for the Default timezone) is easier to
fix ...
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-03 17:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-09-03 17:16 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> Huh. Try strace'ing the process to see what it's doing.
> It tries to find something in /usr/share/ ...
> Ok, I think from the compile time options:
> ./configure --with-system-tzdata=/usr/share
Doh. I hadn't looked closely at that. Probably you want
/usr/share/zoneinfo --- at least that's what the Red Hat RPMs use.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 17:57 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 17:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Fri, 3 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> On Fri, 3 Sep 2010, Tom Lane wrote:
>>> Huh. Try strace'ing the process to see what it's doing.
>
>> It tries to find something in /usr/share/ ...
>> Ok, I think from the compile time options:
>> ./configure --with-system-tzdata=/usr/share
>
> Doh. I hadn't looked closely at that. Probably you want
> /usr/share/zoneinfo --- at least that's what the Red Hat RPMs use.
>
I tried even before I wrote to the mailinglist without success:
./configure
./configure --with-system-tzdata=/usr/share/pgsql/timezonesets
./configure --with-system-tzdata=/usr/share/pgsql
./configure --with-system-tzdata=/usr/share
Now I tried without success:
./configure --with-system-tzdata=/usr/share/zoneinfo
With last one I also get:
2010-09-03 19:51:29.079 CEST [27916] @/: FATAL: invalid value for parameter "timezone_abbreviations": "Default"
Correct directory would be:
ls -l /usr/share/pgsql/timezonesets/Default
-rw-r--r-- 1 root root 29602 2010-05-17 20:07 /usr/share/pgsql/timezonesets/Default
File looks also good.
Any ideas?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-03 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-09-03 18:09 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> Doh. I hadn't looked closely at that. Probably you want
>> /usr/share/zoneinfo --- at least that's what the Red Hat RPMs use.
> I tried even before I wrote to the mailinglist without success:
> ./configure
I'd definitely suggest leaving out the --with-system-tzdata option
altogether if you're not certain it works.
> With last one I also get:
> 2010-09-03 19:51:29.079 CEST [27916] @/: FATAL: invalid value for parameter "timezone_abbreviations": "Default"
This is a different problem; the --with-system-tzdata option wouldn't
affect that.
I think what may be happening here is that a postgres executable expects
to find itself in a full installation tree, ie if it's in /someplace/bin
then the timezone files are in /someplace/share, etc. Did you do a full
"make install" after building, or did you just copy the postgres
executable?
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 18:27 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 18:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Fri, 3 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> On Fri, 3 Sep 2010, Tom Lane wrote:
>>> Doh. I hadn't looked closely at that. Probably you want
>>> /usr/share/zoneinfo --- at least that's what the Red Hat RPMs use.
>
>> I tried even before I wrote to the mailinglist without success:
>> ./configure
>
> I'd definitely suggest leaving out the --with-system-tzdata option
> altogether if you're not certain it works.
OK.
>
>> With last one I also get:
>> 2010-09-03 19:51:29.079 CEST [27916] @/: FATAL: invalid value for parameter "timezone_abbreviations": "Default"
>
> This is a different problem; the --with-system-tzdata option wouldn't
> affect that.
>
> I think what may be happening here is that a postgres executable expects
> to find itself in a full installation tree, ie if it's in /someplace/bin
> then the timezone files are in /someplace/share, etc. Did you do a full
> "make install" after building, or did you just copy the postgres
> executable?
I just copied it as discussed in the original mail to avoid that make
install kills the 8.4 production RPM version:
cp ./src/backend/postgres /bin/postgres-8.3.11
cd /bin
ln -s /usr/share/pgsql/timezonesets share
cd tarballdir
./configure
make
cp ./src/backend/postgres /bin/postgres-8.3.11
2010-09-03 18:24:53.936 GMT [11753] @/: LOG: could not open directory "/share/timezone": No such file or directory
2010-09-03 18:24:53.936 GMT [11753] @/: LOG: could not open directory "/share/timezone": No such file or directory
2010-09-03 18:24:53.936 GMT [11753] @/: LOG: could not open directory "/share/timezone": No such file or directory
2010-09-03 18:24:53.936 GMT [11753] @/: LOG: could not open directory "/share/timezone": No such file or directory
2010-09-03 18:24:53.936 GMT [11753] @/: LOG: could not open directory "/share/timezone": No such file or directory
2010-09-03 20:24:53.936 CEST [11753] @/: FATAL: invalid value for parameter "timezone_abbreviations": "Default"
I previously made the strace and therefore I added the option to configure
to get the right directory.
Any further idea where I should copy the binary or any option or any file
copy for the time zone files?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-03 18:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-09-03 18:44 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> I think what may be happening here is that a postgres executable expects
>> to find itself in a full installation tree, ie if it's in /someplace/bin
>> then the timezone files are in /someplace/share, etc. Did you do a full
>> "make install" after building, or did you just copy the postgres
>> executable?
> I just copied it as discussed in the original mail to avoid that make
> install kills the 8.4 production RPM version:
> cp ./src/backend/postgres /bin/postgres-8.3.11
Definitely not going to work. Instead, configure with --prefix set
to /someplace/harmless, make, make install, execute from
/someplace/harmless/bin/.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 19:34 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 19:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Fri, 3 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> On Fri, 3 Sep 2010, Tom Lane wrote:
>>> I think what may be happening here is that a postgres executable expects
>>> to find itself in a full installation tree, ie if it's in /someplace/bin
>>> then the timezone files are in /someplace/share, etc. Did you do a full
>>> "make install" after building, or did you just copy the postgres
>>> executable?
>
>> I just copied it as discussed in the original mail to avoid that make
>> install kills the 8.4 production RPM version:
>> cp ./src/backend/postgres /bin/postgres-8.3.11
>
> Definitely not going to work. Instead, configure with --prefix set
> to /someplace/harmless, make, make install, execute from
> /someplace/harmless/bin/.
Thanks tom, your support for PostgreSQL is really very, very good.
Install:
./configure --prefix /opt/postgres-8.3
make
make install
su -l postgres -c "/opt/postgres-8.3/bin/postgres -p 54321 -D /var/lib/pgsql-old/data &" >> /var/lib/pgsql-old/pgstartup.log 2>&1 < /dev/null
Back to the original problem:
8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt
Main difference as I saw:
8.3: -> Nested Loop Left Join (cost=0.00..1195433.19 rows=67 width=16)
8.4: -> Merge Left Join (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00 rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400 width=16)
Any ideas why? How to fix?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-03 21:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2010-09-03 21:10 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Gerhard Wiesinger <lists@wiesinger.com> writes:
> Back to the original problem:
Finally ;-)
> 8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
> 8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt
Hmm. The 8.3 plan is indeed assuming that the number of rows will stay
constant as we bubble up through the join levels, but AFAICS this is
simply wrong:
-> Nested Loop Left Join (cost=0.00..38028.89 rows=67 width=8)
-> Nested Loop Left Join (cost=0.00..25399.46 rows=67 width=8)
-> Nested Loop Left Join (cost=0.00..12770.04 rows=67 width=8)
-> Index Scan using i_log_unique on log l (cost=0.00..140.61 rows=67 width=8)
Index Cond: (datetime >= (now() - '00:01:00'::interval))
-> Index Scan using unique_key_and_id on log_details d7 (cost=0.00..187.39 rows=89 width=8)
Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6))
-> Index Scan using unique_key_and_id on log_details d6 (cost=0.00..187.39 rows=89 width=8)
Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5))
-> Index Scan using unique_key_and_id on log_details d5 (cost=0.00..187.39 rows=89 width=8)
Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4))
If the log_details indexscans are expected to produce 89 rows per
execution, then surely the join size should go up 89x at each level,
because the join steps themselves don't eliminate anything.
In 8.4 the arithmetic is at least self-consistent:
-> Nested Loop Left Join (cost=0.00..505256.95 rows=57630 width=8)
-> Nested Loop Left Join (cost=0.00..294671.96 rows=6059 width=8)
-> Nested Loop Left Join (cost=0.00..272532.55 rows=637 width=8)
-> Index Scan using log_pkey on log l (cost=0.00..270203.92 rows=67 width=8)
Filter: (datetime >= (now() - '00:01:00'::interval))
-> Index Scan using unique_key_and_id on log_details d7 (cost=0.00..34.63 rows=10 width=8)
Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6))
-> Index Scan using unique_key_and_id on log_details d6 (cost=0.00..34.63 rows=10 width=8)
Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5))
-> Index Scan using unique_key_and_id on log_details d5 (cost=0.00..34.63 rows=10 width=8)
Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4))
The rowcount estimates are apparently a shade less than 10, but they get
rounded off in the display.
I believe the reason for this change is that 8.4's join estimation code
was rewritten so that it wasn't completely bogus for outer joins. 8.3
might have been getting the right answer, but it was for the wrong
reasons.
So the real question to be answered here is why doesn't it think that
each of the unique_key_and_id indexscans produce just a single row, as
you indicated was the case. The 8.4 estimate is already a factor of
almost 10 closer to reality than 8.3's, but you need another factor of
10. You might find that increasing the statistics target for the
log_details table helps.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-04 06:58 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-04 06:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Fri, 3 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> 8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
>> 8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt
>
> Hmm. The 8.3 plan is indeed assuming that the number of rows will stay
> constant as we bubble up through the join levels, but AFAICS this is
> simply wrong:
>
> -> Nested Loop Left Join (cost=0.00..38028.89 rows=67 width=8)
> -> Nested Loop Left Join (cost=0.00..25399.46 rows=67 width=8)
> -> Nested Loop Left Join (cost=0.00..12770.04 rows=67 width=8)
> -> Index Scan using i_log_unique on log l (cost=0.00..140.61 rows=67 width=8)
> Index Cond: (datetime >= (now() - '00:01:00'::interval))
> -> Index Scan using unique_key_and_id on log_details d7 (cost=0.00..187.39 rows=89 width=8)
> Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6))
> -> Index Scan using unique_key_and_id on log_details d6 (cost=0.00..187.39 rows=89 width=8)
> Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5))
> -> Index Scan using unique_key_and_id on log_details d5 (cost=0.00..187.39 rows=89 width=8)
> Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4))
>
> If the log_details indexscans are expected to produce 89 rows per
> execution, then surely the join size should go up 89x at each level,
> because the join steps themselves don't eliminate anything.
>
> In 8.4 the arithmetic is at least self-consistent:
>
> -> Nested Loop Left Join (cost=0.00..505256.95 rows=57630 width=8)
> -> Nested Loop Left Join (cost=0.00..294671.96 rows=6059 width=8)
> -> Nested Loop Left Join (cost=0.00..272532.55 rows=637 width=8)
> -> Index Scan using log_pkey on log l (cost=0.00..270203.92 rows=67 width=8)
> Filter: (datetime >= (now() - '00:01:00'::interval))
> -> Index Scan using unique_key_and_id on log_details d7 (cost=0.00..34.63 rows=10 width=8)
> Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6))
> -> Index Scan using unique_key_and_id on log_details d6 (cost=0.00..34.63 rows=10 width=8)
> Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5))
> -> Index Scan using unique_key_and_id on log_details d5 (cost=0.00..34.63 rows=10 width=8)
> Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4))
>
> The rowcount estimates are apparently a shade less than 10, but they get
> rounded off in the display.
>
> I believe the reason for this change is that 8.4's join estimation code
> was rewritten so that it wasn't completely bogus for outer joins. 8.3
> might have been getting the right answer, but it was for the wrong
> reasons.
>
> So the real question to be answered here is why doesn't it think that
> each of the unique_key_and_id indexscans produce just a single row, as
> you indicated was the case. The 8.4 estimate is already a factor of
> almost 10 closer to reality than 8.3's, but you need another factor of
> 10. You might find that increasing the statistics target for the
> log_details table helps.
Ok, Tom, tried different things (more details are below):
1.) Setting statistic target to 1000 and 10000 (without success), still
merge join
2.) Tried to added a Index on description to help the planner for
uniqueness (without success)
3.) Forced the planner to use nested loop joins (SUCCESS):
SET enable_hashjoin=false;SET enable_mergejoin=false;
(BTW: How do use such settings in Java and PHP and Perl, is there a
command available?)
Open questions:
Why does the planner not choose nested loop joins, that should be the
optimal one for that situation?
Does the planner value: a.) UNIQUENESS b.) UNIQUENESS and NOT NULLs?
Any ideas for improvement of the planner?
Details:
-- CREATE UNIQUE INDEX unique_key_and_id ON log_details USING btree (fk_id, fk_keyid)
-- 1000 and 10000 didn't help for better query plan for Nested Loop Left Join, still Merge Left Join
-- Sample with:
-- ALTER TABLE log_details ALTER COLUMN fk_id SET STATISTICS 10000;
-- ALTER TABLE log_details ALTER COLUMN fk_keyid SET STATISTICS 10000;
-- ANALYZE VERBOSE log_details;
-- Still Merge Join:
-- -> Merge Left Join (cost=9102353.88..83786934.25 rows=2726186787 width=16)
-- Merge Cond: (l.id = d2000902.fk_id)
-- -> Merge Left Join (cost=8926835.18..40288402.09 rows=972687282 width=24)
-- Merge Cond: (l.id = d2000904.fk_id)
-- Default values again
ALTER TABLE log_details ALTER COLUMN fk_id SET STATISTICS 100;
ALTER TABLE log_details ALTER COLUMN fk_keyid SET STATISTICS 100;
ANALYZE VERBOSE log_details;
-- Tried to add WITHOUT SUCCESS (that planner could know that description is NOT NULL and UNIQE)
DROP INDEX IF EXISTS i_key_description_desc;
CREATE UNIQUE INDEX i_key_description_desc ON key_description (description);
-- Therefore planner should know: keyid is NOT NULL and UNIQUE and only one result: (SELECT keyid FROM key_description WHERE description = 'Raumsolltemperatur')
-- Therefore from constraint planner should know that fk_id is NOT NULL and UNIQUE: CONSTRAINT unique_key_and_id UNIQUE(fk_id, fk_keyid):
-- LEFT JOIN log_details d1 ON l.id = d1.fk_id AND
-- d1.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Raumsolltemperatur')
-- Does the planner value alls those UNIQUEnesses and NOT NULLs?
-- Again back to 8.3 query plan which is fast (319ms):
SET enable_hashjoin=false;
SET enable_mergejoin=false;
-- -> Nested Loop Left Join (cost=0.00..22820970510.45 rows=2727492136 width=16)
-- -> Nested Loop Left Join (cost=0.00..12810087616.29 rows=973121653 width=24)
-- -> Nested Loop Left Join (cost=0.00..9238379092.22 rows=347192844 width=24)
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-13 06:39 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-13 06:39 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
Hello,
Any news or ideas regarding this issue?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
On Sat, 4 Sep 2010, Gerhard Wiesinger wrote:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>
>> Gerhard Wiesinger <lists@wiesinger.com> writes:
>>> 8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
>>> 8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt
>>
>> Hmm. The 8.3 plan is indeed assuming that the number of rows will stay
>> constant as we bubble up through the join levels, but AFAICS this is
>> simply wrong:
>>
>> -> Nested Loop Left Join (cost=0.00..38028.89 rows=67 width=8)
>> -> Nested Loop Left Join (cost=0.00..25399.46 rows=67 width=8)
>> -> Nested Loop Left Join (cost=0.00..12770.04 rows=67
>> width=8)
>> -> Index Scan using i_log_unique on log l
>> (cost=0.00..140.61 rows=67 width=8)
>> Index Cond: (datetime >= (now() - '00:01:00'::interval))
>> -> Index Scan using unique_key_and_id on log_details d7
>> (cost=0.00..187.39 rows=89 width=8)
>> Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6))
>> -> Index Scan using unique_key_and_id on log_details d6
>> (cost=0.00..187.39 rows=89 width=8)
>> Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5))
>> -> Index Scan using unique_key_and_id on log_details d5
>> (cost=0.00..187.39 rows=89 width=8)
>> Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4))
>>
>> If the log_details indexscans are expected to produce 89 rows per
>> execution, then surely the join size should go up 89x at each level,
>> because the join steps themselves don't eliminate anything.
>>
>> In 8.4 the arithmetic is at least self-consistent:
>>
>> -> Nested Loop Left Join (cost=0.00..505256.95 rows=57630
>> width=8)
>> -> Nested Loop Left Join (cost=0.00..294671.96 rows=6059
>> width=8)
>> -> Nested Loop Left Join (cost=0.00..272532.55 rows=637
>> width=8)
>> -> Index Scan using log_pkey on log l
>> (cost=0.00..270203.92 rows=67 width=8)
>> Filter: (datetime >= (now() - '00:01:00'::interval))
>> -> Index Scan using unique_key_and_id on log_details d7
>> (cost=0.00..34.63 rows=10 width=8)
>> Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6))
>> -> Index Scan using unique_key_and_id on log_details d6
>> (cost=0.00..34.63 rows=10 width=8)
>> Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5))
>> -> Index Scan using unique_key_and_id on log_details d5
>> (cost=0.00..34.63 rows=10 width=8)
>> Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4))
>>
>> The rowcount estimates are apparently a shade less than 10, but they get
>> rounded off in the display.
>>
>> I believe the reason for this change is that 8.4's join estimation code
>> was rewritten so that it wasn't completely bogus for outer joins. 8.3
>> might have been getting the right answer, but it was for the wrong
>> reasons.
>>
>> So the real question to be answered here is why doesn't it think that
>> each of the unique_key_and_id indexscans produce just a single row, as
>> you indicated was the case. The 8.4 estimate is already a factor of
>> almost 10 closer to reality than 8.3's, but you need another factor of
>> 10. You might find that increasing the statistics target for the
>> log_details table helps.
>
>
> Ok, Tom, tried different things (more details are below):
> 1.) Setting statistic target to 1000 and 10000 (without success), still merge
> join
> 2.) Tried to added a Index on description to help the planner for uniqueness
> (without success)
> 3.) Forced the planner to use nested loop joins (SUCCESS):
> SET enable_hashjoin=false;SET enable_mergejoin=false;
> (BTW: How do use such settings in Java and PHP and Perl, is there a command
> available?)
>
> Open questions:
> Why does the planner not choose nested loop joins, that should be the optimal
> one for that situation?
> Does the planner value: a.) UNIQUENESS b.) UNIQUENESS and NOT NULLs?
> Any ideas for improvement of the planner?
>
> Details:
> -- CREATE UNIQUE INDEX unique_key_and_id ON log_details USING btree (fk_id,
> fk_keyid)
> -- 1000 and 10000 didn't help for better query plan for Nested Loop Left
> Join, still Merge Left Join
> -- Sample with:
> -- ALTER TABLE log_details ALTER COLUMN fk_id SET STATISTICS 10000;
> -- ALTER TABLE log_details ALTER COLUMN fk_keyid SET STATISTICS 10000;
> -- ANALYZE VERBOSE log_details;
> -- Still Merge Join:
> -- -> Merge Left Join (cost=9102353.88..83786934.25 rows=2726186787
> width=16)
> -- Merge Cond: (l.id = d2000902.fk_id)
> -- -> Merge Left Join (cost=8926835.18..40288402.09 rows=972687282
> width=24)
> -- Merge Cond: (l.id = d2000904.fk_id)
> -- Default values again
> ALTER TABLE log_details ALTER COLUMN fk_id SET STATISTICS 100;
> ALTER TABLE log_details ALTER COLUMN fk_keyid SET STATISTICS 100;
> ANALYZE VERBOSE log_details;
>
> -- Tried to add WITHOUT SUCCESS (that planner could know that description is
> NOT NULL and UNIQE)
> DROP INDEX IF EXISTS i_key_description_desc;
> CREATE UNIQUE INDEX i_key_description_desc ON key_description (description);
> -- Therefore planner should know: keyid is NOT NULL and UNIQUE and only one
> result: (SELECT keyid FROM key_description WHERE description =
> 'Raumsolltemperatur')
> -- Therefore from constraint planner should know that fk_id is NOT NULL and
> UNIQUE: CONSTRAINT unique_key_and_id UNIQUE(fk_id, fk_keyid):
> -- LEFT JOIN log_details d1 ON l.id = d1.fk_id AND
> -- d1.fk_keyid = (SELECT keyid FROM key_description WHERE description =
> 'Raumsolltemperatur')
> -- Does the planner value alls those UNIQUEnesses and NOT NULLs?
>
> -- Again back to 8.3 query plan which is fast (319ms):
> SET enable_hashjoin=false;
> SET enable_mergejoin=false;
> -- -> Nested Loop Left Join (cost=0.00..22820970510.45 rows=2727492136
> width=16)
> -- -> Nested Loop Left Join (cost=0.00..12810087616.29
> rows=973121653 width=24)
> -- -> Nested Loop Left Join (cost=0.00..9238379092.22
> rows=347192844 width=24)
>
> Thnx.
>
> Ciao,
> Gerhard
>
> --
> http://www.wiesinger.com/
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-13 18:32 ` Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Merlin Moncure @ 2010-09-13 18:32 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
On Mon, Sep 13, 2010 at 2:39 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
> Hello,
>
> Any news or ideas regarding this issue?
hm. is retooling the query an option? specifically, can you try converting
CREATE OR REPLACE VIEW log_entries AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
d1.value AS Raumsolltemperatur,
[...]
FROM
log l
LEFT JOIN log_details d1 ON l.id = d1.fk_id AND
d1.fk_keyid = (SELECT keyid FROM key_description WHERE description =
'Raumsolltemperatur')
[...]
to
CREATE OR REPLACE VIEW log_entries AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
(select value from log_details ld join key_description kd on
ld.fk_keyid = kd.keyid where ld.fk_id = l.id and description =
'Raumsolltemperatur') AS Raumsolltemperatur,
[...]
(I am not 100% sure I have your head around your query, but I think I do)?
This should get you a guaranteed (although not necessarily 'the best'
plan, with each returned view column being treated independently of
the other (is that what you want?). Also, if schema changes are under
consideration, you can play log_details/key_description, using natural
key and cut out one of the joins. I can't speak to some of the more
complex planner issues at play, but your query absolutely screams
optimization at the SQL level.
What I am 100% sure of, is that you can get better performance if you
do a little out of the box thinking here...
merlin
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
@ 2010-09-14 06:07 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-14 06:07 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
Hello Merlin,
Seems to be a feasible approach. On problem which might be that when
multiple rows are returned that they are not ordered in each subselect
correctly. Any idea to solve that?
e.g.
Raumsolltemperatur | Raumisttemperatur
Value from time 1 | Value from time 2
Value from time 2 | Value from time 1
but should be
Raumsolltemperatur | Raumisttemperatur
Value from time 1 | Value from time 1
Value from time 2 | Value from time 2
But that might be solveable by first selecting keys from the log_details
table and then join again.
I will try it in the evening and I have to think about in detail.
But thank you for the new approach and opening the mind :-)
Ciao,
Gerhard
--
http://www.wiesinger.com/
On Mon, 13 Sep 2010, Merlin Moncure wrote:
> On Mon, Sep 13, 2010 at 2:39 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> Hello,
>>
>> Any news or ideas regarding this issue?
>
> hm. is retooling the query an option? specifically, can you try converting
>
> CREATE OR REPLACE VIEW log_entries AS
> SELECT
> l.id AS id,
> l.datetime AS datetime,
> l.tdate AS tdate,
> l.ttime AS ttime,
> d1.value AS Raumsolltemperatur,
> [...]
> FROM
> log l
> LEFT JOIN log_details d1 ON l.id = d1.fk_id AND
> d1.fk_keyid = (SELECT keyid FROM key_description WHERE description =
> 'Raumsolltemperatur')
> [...]
>
> to
>
> CREATE OR REPLACE VIEW log_entries AS
> SELECT
> l.id AS id,
> l.datetime AS datetime,
> l.tdate AS tdate,
> l.ttime AS ttime,
> (select value from log_details ld join key_description kd on
> ld.fk_keyid = kd.keyid where ld.fk_id = l.id and description =
> 'Raumsolltemperatur') AS Raumsolltemperatur,
> [...]
>
> (I am not 100% sure I have your head around your query, but I think I do)?
> This should get you a guaranteed (although not necessarily 'the best'
> plan, with each returned view column being treated independently of
> the other (is that what you want?). Also, if schema changes are under
> consideration, you can play log_details/key_description, using natural
> key and cut out one of the joins. I can't speak to some of the more
> complex planner issues at play, but your query absolutely screams
> optimization at the SQL level.
>
> What I am 100% sure of, is that you can get better performance if you
> do a little out of the box thinking here...
>
> merlin
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-14 12:01 ` Merlin Moncure <mmoncure@gmail.com>
2010-09-14 19:59 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Merlin Moncure @ 2010-09-14 12:01 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
> Hello Merlin,
>
> Seems to be a feasible approach. On problem which might be that when
> multiple rows are returned that they are not ordered in each subselect
> correctly. Any idea to solve that?
>
> e.g.
> Raumsolltemperatur | Raumisttemperatur
> Value from time 1 | Value from time 2
> Value from time 2 | Value from time 1
>
> but should be
> Raumsolltemperatur | Raumisttemperatur
> Value from time 1 | Value from time 1
> Value from time 2 | Value from time 2
>
> But that might be solveable by first selecting keys from the log_details
> table and then join again.
>
> I will try it in the evening and I have to think about in detail.
>
> But thank you for the new approach and opening the mind :-)
Using subquery in that style select (<subquery>), ... is limited to
results that return 1 row, 1 column. I assumed that was the case...if
it isn't in your view, you can always attempt arrays:
CREATE OR REPLACE VIEW log_entries AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
array(select value from log_details ld join key_description kd on
ld.fk_keyid = kd.keyid where ld.fk_id = l.id and description =
'Raumsolltemperatur' order by XYZ) AS Raumsolltemperatur,
[...]
arrays might raise the bar somewhat in terms of dealing with the
returned data, or they might work great. some experimentation is in
order.
XYZ being the ordering condition you want. If that isn't available
inside the join then we need to think about this some more. We could
probably help more if you could describe the schema in a little more
detail. This is solvable.
merlin
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
@ 2010-09-14 19:59 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 06:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-14 19:59 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
On Tue, 14 Sep 2010, Merlin Moncure wrote:
> On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> Hello Merlin,
>>
>> Seems to be a feasible approach. On problem which might be that when
>> multiple rows are returned that they are not ordered in each subselect
>> correctly. Any idea to solve that?
>>
>> e.g.
>> Raumsolltemperatur | Raumisttemperatur
>> Value from time 1 | Value from time 2
>> Value from time 2 | Value from time 1
>>
>> but should be
>> Raumsolltemperatur | Raumisttemperatur
>> Value from time 1 | Value from time 1
>> Value from time 2 | Value from time 2
>>
>> But that might be solveable by first selecting keys from the log_details
>> table and then join again.
>>
>> I will try it in the evening and I have to think about in detail.
>>
>> But thank you for the new approach and opening the mind :-)
>
> Using subquery in that style select (<subquery>), ... is limited to
> results that return 1 row, 1 column. I assumed that was the case...if
> it isn't in your view, you can always attempt arrays:
>
> CREATE OR REPLACE VIEW log_entries AS
> SELECT
> l.id AS id,
> l.datetime AS datetime,
> l.tdate AS tdate,
> l.ttime AS ttime,
> array(select value from log_details ld join key_description kd on
> ld.fk_keyid = kd.keyid where ld.fk_id = l.id and description =
> 'Raumsolltemperatur' order by XYZ) AS Raumsolltemperatur,
> [...]
>
> arrays might raise the bar somewhat in terms of dealing with the
> returned data, or they might work great. some experimentation is in
> order.
>
> XYZ being the ordering condition you want. If that isn't available
> inside the join then we need to think about this some more. We could
> probably help more if you could describe the schema in a little more
> detail. This is solvable.
Of course, subquery is limited to a result set returning 1 row and 1
column. Also order is of course preserved because of the join.
Further, I think I found a perfect query plan for the EAV pattern.
First I tried your suggestion but there were some limitation with O(n^2)
efforts (e.g. nested loops=12586 and also index scans with loop 12586):
CREATE OR REPLACE VIEW log_entries_test AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Raumsolltemperatur') AS Raumsolltemperatur,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Raumtemperatur') AS Raumtemperatur,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Kesselsolltemperatur') AS Kesselsolltemperatur,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Kesseltemperatur') AS Kesseltemperatur,
....
FROM
log l
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() - INTERVAL '10 days' ORDER BY datetime DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using i_log_unique on log l (cost=0.00..140820.12 rows=69 width=32) (actual time=2.848..22812.331 rows=12586 loops=1)
Index Cond: (datetime > (now() - '10 days'::interval))
SubPlan 1
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.007..0.018 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.003..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Raumsolltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 2
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.006..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.003..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Raumtemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 3
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.005..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.002..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Kesselsolltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 4
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.006..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.002..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Kesseltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 5
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.005..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.002..0.014 rows=1 loops=12586)
Filter: ((description)::text = 'Speichersolltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 6
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.006..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.003..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Speichertemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Therefore I optimized the query further which can be done in the
following way with another subquery and IHMO a perfect query plan. Also
the subselect avoid multiple iterations for each of the result rows:
CREATE OR REPLACE VIEW log_entries_test AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Raumsolltemperatur')) AS Raumsolltemperatur,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Raumtemperatur')) AS Raumtemperatur,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Kesselsolltemperatur')) AS Kesselsolltemperatur,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Kesseltemperatur')) AS Kesseltemperatur,
...
FROM
log l
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() - INTERVAL '10 days' ORDER BY datetime DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using i_log_unique on log l (cost=0.00..140603.99 rows=69 width=32) (actual time=2.588..5602.899 rows=12586 loops=1)
Index Cond: (datetime > (now() - '10 days'::interval))
SubPlan 2
-> Index Scan using unique_key_and_id on log_details d (cost=2.38..19.97 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=12586)
Index Cond: (($1 = fk_id) AND (fk_keyid = $0))
InitPlan 1 (returns $0)
-> Seq Scan on key_description (cost=0.00..2.38 rows=1 width=8) (actual time=0.015..0.066 rows=1 loops=1)
Filter: ((description)::text = 'Raumsolltemperatur'::text)
SubPlan 4
-> Index Scan using unique_key_and_id on log_details d (cost=2.38..19.97 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=12586)
Index Cond: (($1 = fk_id) AND (fk_keyid = $2))
InitPlan 3 (returns $2)
-> Seq Scan on key_description (cost=0.00..2.38 rows=1 width=8) (actual time=0.009..0.020 rows=1 loops=1)
Filter: ((description)::text = 'Raumtemperatur'::text)
SubPlan 6
-> Index Scan using unique_key_and_id on log_details d (cost=2.38..19.97 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($1 = fk_id) AND (fk_keyid = $3))
InitPlan 5 (returns $3)
-> Seq Scan on key_description (cost=0.00..2.38 rows=1 width=8) (actual time=0.005..0.017 rows=1 loops=1)
Filter: ((description)::text = 'Kesselsolltemperatur'::text)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BTW: Schemadata is in the links discussed in the thread
Thnx to all for helping me.
Ciao,
Gerhard
--
http://www.wiesinger.com/
From pgsql-performance-owner@postgresql.org Tue Sep 14 18:05:33 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
by mail.postgresql.org (Postfix) with ESMTP id 2C6B01337BA8
for <pgsql-performance-postgresql.org@mail.postgresql.org>; Tue, 14 Sep 2010 18:05:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024)
with ESMTP id 33160-03
for <pgsql-performance-postgresql.org@mail.postgresql.org>;
Tue, 14 Sep 2010 21:05:23 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com [209.85.215.174])
by mail.postgresql.org (Postfix) with ESMTP id 77CB91337BCE
for <pgsql-performance@postgresql.org>; Tue, 14 Sep 2010 18:05:22 -0300 (ADT)
Received: by eyb6 with SMTP id 6so3359037eyb.19
for <pgsql-performance@postgresql.org>; Tue, 14 Sep 2010 14:05:22 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=gamma;
h=domainkey-signature:mime-version:received:received:in-reply-to
:references:date:message-id:subject:from:to:cc:content-type
:content-transfer-encoding;
bh=ph7bnTo2Hx2D6BtOmOSqMxqmAn1RFjTjcqy8rJcQ8wc=;
b=aJ4khtf9+vtlRti6BefsbdwACEVN1kB49+U3AIX14ryyt+BzXBTRVbMLD13xU2ywvS
cZl6FdJH1Am5n0+7YSlJFjcSlPZ6h1xrIL9Rz4aCT6B3tlwICcVcIObwQej1f1nN6f4g
YbhdZl3NfYiAt8gbqnCit3qDBTCaVYw/iOrJA=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=gamma;
h=mime-version:in-reply-to:references:date:message-id:subject:from:to
:cc:content-type:content-transfer-encoding;
b=kL5l2BR3mBVvr5czOS+zOyUVPh10JLA+hOBCL5dc/9UDDfKUXcij1LyDT0MhkevFsq
zo+7zzlyTcvgEFUIJVfzeJMv85CVHZPc7iCclYJj6rbcfU7JFYLWqVn99dSQD0xwlv/b
NkFKrBUUrc55AA4PFooiWRkYaVAMxQq/Ghhro=
MIME-Version: 1.0
Received: by 10.239.135.2 with SMTP id b2mr40462hbb.21.1284498321965; Tue, 14
Sep 2010 14:05:21 -0700 (PDT)
Received: by 10.239.136.142 with HTTP; Tue, 14 Sep 2010 14:05:21 -0700 (PDT)
In-Reply-To: <alpine.LFD.2.01.1009142139290.22995@bbs.intern>
References: <alpine.LFD.2.01.1008300802300.4121@bbs.intern>
<12837.1283186981@sss.pgh.pa.us>
<alpine.LFD.2.01.1009020749380.19625@bbs.intern>
<21281.1283433278@sss.pgh.pa.us>
<alpine.LFD.2.01.1009030702520.12651@bbs.intern>
<22001.1283523290@sss.pgh.pa.us>
<alpine.LFD.2.01.1009031837550.28047@bbs.intern>
<24994.1283532424@sss.pgh.pa.us>
<alpine.LFD.2.01.1009031853400.31084@bbs.intern>
<25560.1283534215@sss.pgh.pa.us>
<alpine.LFD.2.01.1009031952390.8416@bbs.intern>
<26620.1283537362@sss.pgh.pa.us>
<alpine.LFD.2.01.1009032014270.11936@bbs.intern>
<27253.1283539468@sss.pgh.pa.us>
<alpine.LFD.2.01.1009032129590.23544@bbs.intern>
<29799.1283548250@sss.pgh.pa.us>
<alpine.LFD.2.01.1009040850110.26391@bbs.intern>
<alpine.LFD.2.01.1009130838530.12288@bbs.intern>
<AANLkTim5+e9C2qPfPyS-z3qaYGTHhtC3Zo2jEJwSo+K+@mail.gmail.com>
<alpine.LFD.2.01.1009140759160.24945@bbs.intern>
<AANLkTi=DMvV7J5UyYKaGyME1zKmesAZrmTZAkyJv37L_@mail.gmail.com>
<alpine.LFD.2.01.1009142139290.22995@bbs.intern>
Date: Tue, 14 Sep 2010 17:05:21 -0400
Message-ID: <AANLkTim2srP5bSy68iDgrS5HvH9p3Obf-qexmxB2tBUc@mail.gmail.com>
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
From: Merlin Moncure <mmoncure@gmail.com>
To: Gerhard Wiesinger <lists@wiesinger.com>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>,
Pavel Stehule <pavel.stehule@gmail.com>, Andreas Kretschmer <akretschmer@spamfence.net>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level:
X-Archive-Number: 201009/81
X-Sequence-Number: 40284
On Tue, Sep 14, 2010 at 3:59 PM, Gerhard Wiesinger <lists@wiesinger.com> wr=
ote:
> On Tue, 14 Sep 2010, Merlin Moncure wrote:
>
>> On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists@wiesinger.com>
>> wrote:
>>>
>>> Hello Merlin,
>>>
>>> Seems to be a feasible approach. On problem which might be that when
>>> multiple rows are returned that they are not ordered in each subselect
>>> correctly. Any idea to solve that?
>>>
>>> e.g.
>>> Raumsolltemperatur | Raumisttemperatur
>>> Value from time 1 =A0| Value from time 2
>>> Value from time 2 =A0| Value from time 1
>>>
>>> but should be
>>> Raumsolltemperatur | Raumisttemperatur
>>> Value from time 1 =A0| Value from time 1
>>> Value from time 2 =A0| Value from time 2
>>>
>>> But that might be solveable by first selecting keys from the log_detail=
s
>>> table and then join again.
>>>
>>> I will try it in the evening and I have to think about in detail.
>>>
>>> But thank you for the new approach and opening the mind :-)
>>
>> Using subquery in that style select (<subquery>), ... is limited to
>> results that return 1 row, 1 column. =A0I assumed that was the case...if
>> it isn't in your view, you can always attempt arrays:
>>
>> CREATE OR REPLACE VIEW log_entries AS
>> SELECT
>> l.id AS id,
>> l.datetime AS datetime,
>> l.tdate AS tdate,
>> l.ttime AS ttime,
>> array(select value from log_details ld join key_description kd on
>> ld.fk_keyid =3D kd.keyid where ld.fk_id =3D l.id and =A0description =3D
>> 'Raumsolltemperatur' order by XYZ) AS Raumsolltemperatur,
>> [...]
>>
>> arrays might raise the bar somewhat in terms of dealing with the
>> returned data, or they might work great. =A0some experimentation is in
>> order.
>>
>> XYZ being the ordering condition you want. =A0If that isn't available
>> inside the join then we need to think about this some more. =A0We could
>> probably help more if you could describe the schema in a little more
>> detail. =A0This is solvable.
>
> Of course, subquery is limited to a result set returning 1 row and 1 colu=
mn.
> Also order is of course preserved because of the join.
>
> Further, I think I found a perfect query plan for the EAV pattern.
>
> First I tried your suggestion but there were some limitation with O(n^2)
> efforts (e.g. nested loops=3D12586 and also index scans with loop 12586):
>
> CREATE OR REPLACE VIEW log_entries_test AS
> SELECT
> =A0l.id AS id,
> =A0l.datetime AS datetime,
> =A0l.tdate AS tdate,
> =A0l.ttime AS ttime,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Raumsolltemperatu=
r') AS
> Raumsolltemperatur,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Raumtemperatur') =
AS
> Raumtemperatur,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Kesselsolltempera=
tur')
> AS Kesselsolltemperatur,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Kesseltemperatur'=
) AS
> Kesseltemperatur,
> ....
> FROM
> =A0log l
> ;
>
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() -
> INTERVAL '10 days' ORDER BY datetime DESC;
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> Index Scan Backward using i_log_unique on log l =A0(cost=3D0.00..140820.1=
2
> rows=3D69 width=3D32) (actual time=3D2.848..22812.331 rows=3D12586 loops=
=3D1)
> =A0Index Cond: (datetime > (now() - '10 days'::interval))
> =A0SubPlan 1
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.007..0.018 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.003..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raumsoll=
temperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.004..0.004 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 2
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.006..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.003..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raumtemp=
eratur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 3
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.005..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.002..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Kesselso=
lltemperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.003..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 4
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.006..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.002..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Kesselte=
mperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 5
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.005..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.002..0.014 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D
> 'Speichersolltemperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 6
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.006..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.003..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Speicher=
temperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
>
> Therefore I optimized the query further which can be done in the followin=
g
> way with another subquery and IHMO a perfect query plan. Also the subsele=
ct
> avoid multiple iterations for each of the result rows:
>
> CREATE OR REPLACE VIEW log_entries_test AS
> SELECT
> =A0l.id AS id,
> =A0l.datetime AS datetime,
> =A0l.tdate AS tdate,
> =A0l.ttime AS ttime,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D
> 'Raumsolltemperatur')) AS Raumsolltemperatur,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D 'Raumtemperatur'=
)) AS
> Raumtemperatur,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D
> 'Kesselsolltemperatur')) AS Kesselsolltemperatur,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D 'Kesseltemperatu=
r'))
> AS Kesseltemperatur,
> ...
> FROM
> =A0log l
> ;
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() -
> INTERVAL '10 days' ORDER BY datetime DESC;
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> Index Scan Backward using i_log_unique on log l =A0(cost=3D0.00..140603.9=
9
> rows=3D69 width=3D32) (actual time=3D2.588..5602.899 rows=3D12586 loops=
=3D1)
> =A0Index Cond: (datetime > (now() - '10 days'::interval))
> =A0SubPlan 2
> =A0 =A0-> =A0Index Scan using unique_key_and_id on log_details d
> =A0(cost=3D2.38..19.97 rows=3D1 width=3D8) (actual time=3D0.010..0.011 ro=
ws=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0Index Cond: (($1 =3D fk_id) AND (fk_keyid =3D $0))
> =A0 =A0 =A0 =A0 =A0InitPlan 1 (returns $0)
> =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description =A0(cost=3D0.00.=
.2.38 rows=3D1 width=3D8)
> (actual time=3D0.015..0.066 rows=3D1 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raum=
solltemperatur'::text)
> =A0SubPlan 4
> =A0 =A0-> =A0Index Scan using unique_key_and_id on log_details d
> =A0(cost=3D2.38..19.97 rows=3D1 width=3D8) (actual time=3D0.003..0.003 ro=
ws=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0Index Cond: (($1 =3D fk_id) AND (fk_keyid =3D $2))
> =A0 =A0 =A0 =A0 =A0InitPlan 3 (returns $2)
> =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description =A0(cost=3D0.00.=
.2.38 rows=3D1 width=3D8)
> (actual time=3D0.009..0.020 rows=3D1 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raum=
temperatur'::text)
> =A0SubPlan 6
> =A0 =A0-> =A0Index Scan using unique_key_and_id on log_details d
> =A0(cost=3D2.38..19.97 rows=3D1 width=3D8) (actual time=3D0.002..0.003 ro=
ws=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0Index Cond: (($1 =3D fk_id) AND (fk_keyid =3D $3))
> =A0 =A0 =A0 =A0 =A0InitPlan 5 (returns $3)
> =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description =A0(cost=3D0.00.=
.2.38 rows=3D1 width=3D8)
> (actual time=3D0.005..0.017 rows=3D1 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D
> 'Kesselsolltemperatur'::text)
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
>
> BTW: Schemadata is in the links discussed in the thread
>
> Thnx to all for helping me.
np -- this felt particularly satisfying for some reason. btw, I think
you have some more low hanging optimization fruit. I think (although
it would certainly have to be tested) hiding your attribute
description under keyid is buying you nothing but headaches. If you
used natural key style, making description primary key of
key_description (or unique), and had log_details have a description
column that directly referenced that column, your subquery:
(
SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid =3D
(
SELECT keyid FROM key_description WHERE description =3D 'Kesselsolltemp=
eratur'
)
) AS Kesselsolltemperatur,
would look like this:
(
SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND
d.description =3D 'Kesselsolltemperatur'
) AS Kesselsolltemperatur,
your index on log_details(fk_id, description) is of course fatter, but
quite precise...does require rebuilding your entire dataset however.
food for thought.
merlin
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 19:59 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-15 06:32 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 11:52 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-15 06:32 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
On Tue, 14 Sep 2010, Merlin Moncure wrote:
> np -- this felt particularly satisfying for some reason. btw, I think
> you have some more low hanging optimization fruit. I think (although
> it would certainly have to be tested) hiding your attribute
> description under keyid is buying you nothing but headaches. If you
> used natural key style, making description primary key of
> key_description (or unique), and had log_details have a description
> column that directly referenced that column, your subquery:
>
> (
> SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid =
> (
> SELECT keyid FROM key_description WHERE description = 'Kesselsolltemperatur'
> )
> ) AS Kesselsolltemperatur,
>
> would look like this:
> (
> SELECT value FROM log_details d WHERE l.id = d.fk_id AND
> d.description = 'Kesselsolltemperatur'
> ) AS Kesselsolltemperatur,
>
> your index on log_details(fk_id, description) is of course fatter, but
> quite precise...does require rebuilding your entire dataset however.
> food for thought.
I think your suggestion might be slower because the WHERE clause and
possible JOINS with BIGINT is much faster (especially when a lot of data
is queried) than with a VARCHAR. With the latest query plan
key_description is only queried once per subselect which is perfect. I've
also chosen that indirection that I can change description without
changing too much in data model and all data rows on refactoring.
@Tom: Do you think of planner enhancements regarding such situations where
JOINS are "converted" to subselects?
BTW: I had a small bug in the queries and in the code that one description
was wrong (one space too much: 'Meldung F4 2. Zeile' => 'Meldung F4 2. Zeile').
With this indirect data model this is very easy to change: Change
the view and change one code line. With your suggested data model I would
have to update millions of rows ...
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 19:59 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 06:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2010-09-15 11:52 ` Merlin Moncure <mmoncure@gmail.com>
2010-09-15 18:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 1 reply; 39+ messages in thread
From: Merlin Moncure @ 2010-09-15 11:52 UTC (permalink / raw)
To: Gerhard Wiesinger <lists@wiesinger.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
> On Tue, 14 Sep 2010, Merlin Moncure wrote:
>>
>> np -- this felt particularly satisfying for some reason. btw, I think
>> you have some more low hanging optimization fruit. I think (although
>> it would certainly have to be tested) hiding your attribute
>> description under keyid is buying you nothing but headaches. If you
>> used natural key style, making description primary key of
>> key_description (or unique), and had log_details have a description
>> column that directly referenced that column, your subquery:
>>
>> (
>> SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid =
>> (
>> SELECT keyid FROM key_description WHERE description =
>> 'Kesselsolltemperatur'
>> )
>> ) AS Kesselsolltemperatur,
>>
>> would look like this:
>> (
>> SELECT value FROM log_details d WHERE l.id = d.fk_id AND
>> d.description = 'Kesselsolltemperatur'
>> ) AS Kesselsolltemperatur,
>>
>> your index on log_details(fk_id, description) is of course fatter, but
>> quite precise...does require rebuilding your entire dataset however.
>> food for thought.
>
> I think your suggestion might be slower because the WHERE clause and
> possible JOINS with BIGINT is much faster (especially when a lot of data is
> queried) than with a VARCHAR. With the latest query plan key_description is
> only queried once per subselect which is perfect. I've also chosen that
> indirection that I can change description without changing too much in data
> model and all data rows on refactoring.
You're not joining -- you're filtering (and your assumption that
bigint is always going to be faster is quite debatable depending on
circumstances). The join is skipped because of the key (yes, it's
cheap lookup, but w/50 columns each doing it, nothing is cheap).
merlin
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 19:59 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 06:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 11:52 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
@ 2010-09-15 18:39 ` Gerhard Wiesinger <lists@wiesinger.com>
2011-01-05 20:06 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Marc Antonio <marc.queiroz@gmail.com>
0 siblings, 1 reply; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-15 18:39 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>
On Wed, 15 Sep 2010, Merlin Moncure wrote:
> On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> On Tue, 14 Sep 2010, Merlin Moncure wrote:
>>>
>>> np -- this felt particularly satisfying for some reason. btw, I think
>>> you have some more low hanging optimization fruit. I think (although
>>> it would certainly have to be tested) hiding your attribute
>>> description under keyid is buying you nothing but headaches. If you
>>> used natural key style, making description primary key of
>>> key_description (or unique), and had log_details have a description
>>> column that directly referenced that column, your subquery:
>>>
>>> (
>>> SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid =
>>> (
>>> SELECT keyid FROM key_description WHERE description =
>>> 'Kesselsolltemperatur'
>>> )
>>> ) AS Kesselsolltemperatur,
>>>
>>> would look like this:
>>> (
>>> SELECT value FROM log_details d WHERE l.id = d.fk_id AND
>>> d.description = 'Kesselsolltemperatur'
>>> ) AS Kesselsolltemperatur,
>>>
>>> your index on log_details(fk_id, description) is of course fatter, but
>>> quite precise...does require rebuilding your entire dataset however.
>>> food for thought.
>>
>> I think your suggestion might be slower because the WHERE clause and
>> possible JOINS with BIGINT is much faster (especially when a lot of data is
>> queried) than with a VARCHAR. With the latest query plan key_description is
>> only queried once per subselect which is perfect. I've also chosen that
>> indirection that I can change description without changing too much in data
>> model and all data rows on refactoring.
>
> You're not joining -- you're filtering (and your assumption that
> bigint is always going to be faster is quite debatable depending on
> circumstances). The join is skipped because of the key (yes, it's
> cheap lookup, but w/50 columns each doing it, nothing is cheap).
I know that I'm not JOINing in that case - as discussed I ment possible
JOINs in other query scenarios.
BTW: Latest query plan is also optimal that only the
used columns from the view are evaluated. With the full joined version
all columns where used even when dropped in the result-set, e.g.:
SELECT col1, col2 FROM view1; -- Equivalent to SELECT * FROM view1; as col1, col2 are all colums in that view
SELECT col1 FROM view1; -- less effort with subselects when less columns are needed, joins have same "full view" effort here
Ciao,
Gerhard
--
http://www.wiesinger.com/
From pgsql-performance-owner@postgresql.org Wed Sep 15 15:48:33 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
by mail.postgresql.org (Postfix) with ESMTP id 7F05F634580
for <pgsql-performance-postgresql.org@mail.postgresql.org>; Mon, 13 Sep 2010 13:32:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
with ESMTP id 29820-03
for <pgsql-performance-postgresql.org@mail.postgresql.org>;
Mon, 13 Sep 2010 16:32:09 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46])
by mail.postgresql.org (Postfix) with ESMTP id 1253A634253
for <pgsql-performance@postgresql.org>; Mon, 13 Sep 2010 13:32:08 -0300 (ADT)
Received: by bwz11 with SMTP id 11so4631169bwz.19
for <pgsql-performance@postgresql.org>; Mon, 13 Sep 2010 09:32:07 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=gamma;
h=domainkey-signature:received:mime-version:received:in-reply-to
:references:from:date:message-id:subject:to:cc:content-type;
bh=SrL7EJgmKb4ZXmks4pzWfl5U/NaF4iQL9b5uuzAI9zY=;
b=MbYVt2cy4SheQa3rY10fGMj2aAUSIM0QM0N+gDp9ubgBdqeIi0osny60Aq6y+9X3Pu
2LqxUkr0JFlvs4EdiqKoropSOzCQnlTmFovGKxeiShFz5xvzeIEaXZmb6D3rSzolNqYT
xqwPAfUsSukZ4w9iznPM1qjrX+2eFJjTUtoFA=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=gamma;
h=mime-version:in-reply-to:references:from:date:message-id:subject:to
:cc:content-type;
b=h8xCcEXv5qjPA38c6d7fceJbo1LpCSclqkEpZfI9pMxwEJTBHRCZPKiFTb8E7UTfto
ImSZoiaTFZBF3caY23NEQPZh66+GbxF2nCmJW9SYS+jsWiZkZPGgIdBrEke+V/jiepn2
/l5VMbeqC85lFQCswN0ciUiU78radGvPsbvUc=
Received: by 10.204.60.145 with SMTP id p17mr3407724bkh.56.1284395527479; Mon,
13 Sep 2010 09:32:07 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.204.10.11 with HTTP; Mon, 13 Sep 2010 09:31:47 -0700 (PDT)
In-Reply-To: <20100911020337.GH26232@tamriel.snowman.net>
References: <AANLkTinQu1vhokjuAZDd3d2b6oeSqERF4biNGP-k-6UV@mail.gmail.com>
<5323.1284170438@sss.pgh.pa.us> <20100911020337.GH26232@tamriel.snowman.net>
From: Mason Harding <mason.harding@gmail.com>
Date: Mon, 13 Sep 2010 09:31:47 -0700
Message-ID: <AANLkTikweUAnRDw_kTfFSc26-cTDtCA5WbFJBGq82Xr8@mail.gmail.com>
Subject: Re: Slow SQL lookup due to every field being listed in SORT KEY
To: Stephen Frost <sfrost@snowman.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001636c5b4562aa80e049026a395
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level:
X-Archive-Number: 201009/89
X-Sequence-Number: 40292
--001636c5b4562aa80e049026a395
Content-Type: text/plain; charset=ISO-8859-1
Thanks all for your help. I didn't really understand why it was sorting on
every field, but it now makes sense. What I ended up doing was replacing
the
SELECT DISTINCT * FROM .... JOIN ... WHERE ... ORDER BY... LIMIT ...
with
SELECT * FROM ... WHERE id in (SELECT DISTINCT id FROM .... JOIN ... WHERE
... ) ORDER BY... LIMIT ...
This reduced the lookup time down to 19 ms, which is much faster than just
upping the work_mem, as that still took 800ms
Thanks all,
Mason
On Fri, Sep 10, 2010 at 7:03 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > The reason it's sorting by all the columns is the DISTINCT
>
> You might also verify that you actually need/*should* have the DISTINCT,
> if it's included today.. Often developers put that in without
> understanding why they're getting dups (which can often be due to
> missing pieces from the JOIN clause or misunderstanding of the database
> schema...).
>
> Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkyK43kACgkQrzgMPqB3kihX4ACfVboO4jRzFO3hkckdHfrSeAgF
> sysAnjmeoV7BA7uClEY8gXT4nEYhSx0u
> =y556
> -----END PGP SIGNATURE-----
>
>
--001636c5b4562aa80e049026a395
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Thanks all for your help.=A0 I didn't really understand why it was sort=
ing on every field, but it now makes sense.=A0 What I ended up doing was re=
placing the <br><br>SELECT DISTINCT * FROM .... JOIN ... WHERE ... ORDER BY=
... LIMIT ...<br>
with<br><br>SELECT * FROM ... WHERE id in (SELECT DISTINCT id FROM .... JOI=
N ... WHERE ... ) ORDER BY... LIMIT ...<br>This reduced the lookup time dow=
n to 19 ms, which is much faster than just upping the work_mem, as that sti=
ll took 800ms<br>
<br>Thanks all,<br>Mason<br><br><div class=3D"gmail_quote">On Fri, Sep 10, =
2010 at 7:03 PM, Stephen Frost <span dir=3D"ltr"><<a href=3D"mailto:sfro=
st@snowman.net">sfrost@snowman.net</a>></span> wrote:<br><blockquote cla=
ss=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; border-left: 1px sol=
id rgb(204, 204, 204); padding-left: 1ex;">
<div class=3D"im">* Tom Lane (<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.=
pgh.pa.us</a>) wrote:<br>
> The reason it's sorting by all the columns is the DISTINCT<br>
<br>
</div>You might also verify that you actually need/*should* have the DISTIN=
CT,<br>
if it's included today.. =A0Often developers put that in without<br>
understanding why they're getting dups (which can often be due to<br>
missing pieces from the JOIN clause or misunderstanding of the database<br>
schema...).<br>
<font color=3D"#888888"><br>
=A0 =A0 =A0 =A0Stephen<br>
</font><br>-----BEGIN PGP SIGNATURE-----<br>
Version: GnuPG v1.4.9 (GNU/Linux)<br>
<br>
iEYEARECAAYFAkyK43kACgkQrzgMPqB3kihX4ACfVboO4jRzFO3hkckdHfrSeAgF<br>
sysAnjmeoV7BA7uClEY8gXT4nEYhSx0u<br>
=3Dy556<br>
-----END PGP SIGNATURE-----<br>
<br></blockquote></div><br>
--001636c5b4562aa80e049026a395--
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-14 19:59 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 06:32 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 11:52 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Merlin Moncure <mmoncure@gmail.com>
2010-09-15 18:39 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
@ 2011-01-05 20:06 ` Marc Antonio <marc.queiroz@gmail.com>
0 siblings, 0 replies; 39+ messages in thread
From: Marc Antonio @ 2011-01-05 20:06 UTC (permalink / raw)
To: pgsql-performance
Hi,
I had a similar problem with many left join, reading about planning
optimization i tried to edit postgresql.conf and uncommented the line
join_collapse_limit = 8 and set it to 1, disables collapsing of explicit .
My query its taking 2000s in 8.4 and the same query 2ms in 8.3. Now its
working fast in 8.4.
Best regards,
Marc
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Major-performance-problem-after-upgrade-from-8-3-to-8-4-tp27...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-03 05:07 ` Gerhard Wiesinger <lists@wiesinger.com>
1 sibling, 0 replies; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-09-03 05:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Thu, 2 Sep 2010, Tom Lane wrote:
> Gerhard Wiesinger <lists@wiesinger.com> writes:
>> Problem is that PostgreSQL doesn't listen and take much CPU and also disk
>> I/O. 8.3 was shut down cleanly.
>
> Hm, you sure about that? What's in the postmaster log?
BTW: Do I need other postgres user with a different home directory?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Major performance problem after upgrade from 8.3 to 8.4
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Re: Major performance problem after upgrade from 8.3 to 8.4 Andreas Kretschmer <akretschmer@spamfence.net>
@ 2010-08-30 07:48 ` Gerhard Wiesinger <lists@wiesinger.com>
1 sibling, 0 replies; 39+ messages in thread
From: Gerhard Wiesinger @ 2010-08-30 07:48 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
On Mon, 30 Aug 2010, Andreas Kretschmer wrote:
> Gerhard Wiesinger <lists@wiesinger.com> wrote:
>
>> I know that the data model is key/value pairs but it worked well in 8.3.
>> I need this flexibility.
>>
>> Any ideas?
>
> If i understand the query correctly it's a pivot-table, right?
>
The view flattens the key/value structure (for enchanceable technical
measure values) to a flat structure with many columns. The view is that I
have a flat structure and then have easy to write queries for aggregation.
The query itself is just an aggregating query over that flat structure.
Any ideas for better optimizations?
> If yes, and if i where you, i would try to rewrite this query, to
> something like:
>
> select
> timestamp,
> sum (case when keyid = 1 then value else 0 end) as Raumsolltemperatur,
> ...
> from
> log
> group by
> timestamp;
>
I will try that. But what I don't understand: query was really fast in
8.3 even with 24 hours timeframe.
>
> Assuming you can read a german text:
> http://www.pg-forum.de/h-ufig-gestellte-fragen-faq/4067-faq-zeilen-zu-spalten.html
Hopefully yes :-) after that Fedora/Postgresql nightmare update night
(Parallel Fedora upgrade stalled whole machine since that query from
Nagios was executed I guess hundreds of times since it got very slow in
8.4). Machine was pingable but nothing else even on console :-( RAID
rebuild and all other stuff :-(
I planned the upgrade but I didn't expect problems with the query plan
instability :-(
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
^ permalink raw reply [nested|flat] 39+ messages in thread
end of thread, other threads:[~2011-01-05 20:06 UTC | newest]
Thread overview: 39+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-30 06:20 Major performance problem after upgrade from 8.3 to 8.4 Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:00 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:25 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-30 07:56 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:22 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-30 07:34 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 07:58 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 08:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 16:11 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-30 16:45 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-02 05:53 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-02 13:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 05:03 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 14:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:38 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 16:57 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 17:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 17:57 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 18:27 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 18:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 19:34 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-03 21:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-04 06:58 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 06:39 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-13 18:32 ` Merlin Moncure <mmoncure@gmail.com>
2010-09-14 06:07 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-14 12:01 ` Merlin Moncure <mmoncure@gmail.com>
2010-09-14 19:59 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 06:32 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-09-15 11:52 ` Merlin Moncure <mmoncure@gmail.com>
2010-09-15 18:39 ` Gerhard Wiesinger <lists@wiesinger.com>
2011-01-05 20:06 ` Marc Antonio <marc.queiroz@gmail.com>
2010-09-03 05:07 ` Gerhard Wiesinger <lists@wiesinger.com>
2010-08-30 07:48 ` Gerhard Wiesinger <lists@wiesinger.com>
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