pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Gerhard Wiesinger <lists@wiesinger.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Date: Wed, 15 Sep 2010 08:32:00 +0200 (CEST)
Message-ID: <alpine.LFD.2.01.1009150646560.10200@bbs.intern> (raw)
In-Reply-To: <AANLkTim2srP5bSy68iDgrS5HvH9p3Obf-qexmxB2tBUc@mail.gmail.com>
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>

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/



view thread (39+ messages)  latest in thread

Message-ID: <alpine.LFD.2.01.1009150646560.10200@bbs.intern>
Permalink:  ../alpine.LFD.2.01.1009150646560.10200@bbs.intern/
Also on:    postgresql.org/message-id/alpine.LFD.2.01.1009150646560.10200@bbs.intern

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: lists@wiesinger.com, mmoncure@gmail.com, tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, akretschmer@spamfence.net
  Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
  In-Reply-To: <alpine.LFD.2.01.1009150646560.10200@bbs.intern>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox