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

Re: Major performance problem after upgrade from 8.3 to 8.4

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Date: 2010-09-15 11:52:32
Message-ID: AANLkTi=18cbt7UqUy49_=sSkC6fxxGtynV=_9vviMu6Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger <lists(at)wiesinger(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-09-15 13:38:07
Subject: Re: POSTGRES error
Previous:From: Timothy.NoonanDate: 2010-09-15 11:07:35
Subject: POSTGRES error

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