On Wed, 15 Sep 2010, Merlin Moncure wrote:
On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger <lists@xxxxxxxxxxxxx> 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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
