Date: Thu, 19 Oct 2006 20:34:00 +0200
To: "Merlin Moncure" <mmoncure@gmail.com>
Subject: Re: DB Performance decreases due to often written/accessed table
From: "Jens Schipkowski" <jens.schipkowski@apus.co.at>
Organization: APUS Software GmbH
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <op.thn5e4sj81rjf6@xjens.apus.local>
	<b42b73150610191032x725cbbebg9bf1b1533622c30f@mail.gmail.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.tholuyae81rjf6@xjens.apus.local>
In-Reply-To: <b42b73150610191032x725cbbebg9bf1b1533622c30f@mail.gmail.com>
User-Agent: Opera Mail/9.02 (Linux)

On Thu, 19 Oct 2006 19:32:22 +0200, Merlin Moncure <mmoncure@gmail.com> =
 =

wrote:

> On 10/19/06, Jens Schipkowski <jens.schipkowski@apus.co.at> wrote:
>> // select finds out which one has not an twin
>> // a twin is defined as record with the same attr* values
>> // decreases speed over time until timeout by postgresql
>> SELECT *
>>  FROM tbl_reg reg
>> WHERE register <> loc1 AND
>>         idreg NOT IN
>>                 (
>>                 SELECT reg.idreg
>>                 FROM tbl_reg reg, tbl_reg regtwin
>>                 WHERE regtwin.register =3D 1 AND
>>                         regtwin.type <> 20 AND
>>                         reg.attr1 =3D regtwin.attr1 AND
>>                         reg.attr2 =3D regtwin.attr2 AND
>>                         reg.attr3 =3D regtwin.attr3 AND
>>                         reg.attr4 =3D regtwin.attr4 AND
>>                         reg.attr5 =3D regtwin.attr5 AND
>>                         reg.attr6 =3D regtwin.attr6 AND
>>                         reg.idreg <> regtwin.idreg AND
>>                         reg.register =3D 2
>>                 );
>
> [...]
>
>> We have the problem, that we cannot see any potential to improve SQL
>> statements. Indexing the attr* columns seems not to be an solution,
>> because the data mustn't be unique (twins) and changes really often s=
o
>> reindexing will took too long.
>
> 1. your database design is the real culprit here.  If you want things
> to run really quickly, solve the problem there by normalizing your
> schema. denomalization is the root cause of many, many, problems
> posted here on this list.
Believe it is normalized. We also seperated configuration and runtime  =

data. And this is a runtime table.
This table holds short living data for devices to be registered by a  =

registration server. The INSERTs are triggered by external devices. The =
 =

master data tables are perfectly normalized too. What you are seeing is =
 =

not the real column names. I changed it due to readability. attr* have  =

really different names and meanings. A "twin" (in real, initiator/member=
  =

of the same conferencing group) is defined by these attributes. Due to  =

high flexibility of this system (serverside configuration/ deviceside  =

configuration for runtime) there is no other way to normalize.

> 2. barring that, the above query will run fastest by creating
> multi-column indexes on regtwin (attr*) fields. and reg(attr*).  the
> real solution to problems like this is often proper idnexing,
> especially multi column.  saying indexes take to long to build is like=

> saying: 'i have a problem, so i am going to replace it with a much
> worse problem'.
I will index it. Just prepared the test and will run it tomorrow.
> 3. try where exists/not exists instead of where in/not in
Did try it, before I switched to NOT IN. It was 10 times slower.
>
> merlin