Re: DB Performance decreases due to often written/accessed table

On Thu, 19 Oct 2006 19:32:22 +0200, Merlin Moncure <mmoncure(at)gmail(dot)com>
wrote:

> On 10/19/06, Jens Schipkowski <jens(dot)schipkowski(at)apus(dot)co(dot)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 = 1 AND
>> regtwin.type <> 20 AND
>> reg.attr1 = regtwin.attr1 AND
>> reg.attr2 = regtwin.attr2 AND
>> reg.attr3 = regtwin.attr3 AND
>> reg.attr4 = regtwin.attr4 AND
>> reg.attr5 = regtwin.attr5 AND
>> reg.attr6 = regtwin.attr6 AND
>> reg.idreg <> regtwin.idreg AND
>> reg.register = 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 so
>> 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