Re: Major performance problem after upgrade from 8.3 to 8.4

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/