From: | Gerhard Wiesinger <lists(at)wiesinger(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(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 06:32:00 |
Message-ID: | alpine.LFD.2.01.1009150646560.10200@bbs.intern |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tobias Brox | 2010-09-15 10:05:03 | locking issue on simple selects? |
Previous Message | Anssi Kääriäinen | 2010-09-15 06:26:14 | Performance problem with joined aggregate query |