Date: Wed, 15 Sep 2010 08:32:00 +0200 (CEST)
From: Gerhard Wiesinger <lists@wiesinger.com>
To: Merlin Moncure <mmoncure@gmail.com>
cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	Andreas Kretschmer <akretschmer@spamfence.net>
Subject: Re: Major performance problem after upgrade from 8.3 to
 8.4
In-Reply-To: <AANLkTim2srP5bSy68iDgrS5HvH9p3Obf-qexmxB2tBUc@mail.gmail.com>
Message-ID: <alpine.LFD.2.01.1009150646560.10200@bbs.intern>
References: <alpine.LFD.2.01.1008300802300.4121@bbs.intern>
	<21281.1283433278@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009030702520.12651@bbs.intern>
	<22001.1283523290@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009031837550.28047@bbs.intern>
	<24994.1283532424@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009031853400.31084@bbs.intern>
	<25560.1283534215@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009031952390.8416@bbs.intern>
	<26620.1283537362@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009032014270.11936@bbs.intern>
	<27253.1283539468@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009032129590.23544@bbs.intern>
	<29799.1283548250@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009040850110.26391@bbs.intern>
	<alpine.LFD.2.01.1009130838530.12288@bbs.intern>
	<AANLkTim5+e9C2qPfPyS-z3qaYGTHhtC3Zo2jEJwSo+K+@mail.gmail.com>
	<alpine.LFD.2.01.1009140759160.24945@bbs.intern>
	<AANLkTi=DMvV7J5UyYKaGyME1zKmesAZrmTZAkyJv37L_@mail.gmail.com>
	<alpine.LFD.2.01.1009142139290.22995@bbs.intern>
	<AANLkTim2srP5bSy68iDgrS5HvH9p3Obf-qexmxB2tBUc@mail.gmail.com>
User-Agent: Alpine 2.01 (LFD 1266 2009-07-14)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

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/