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/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
