Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1848765838.1231859100111.JavaMail.jira@brutus>
Date: Tue, 13 Jan 2009 07:05:00 -0800 (PST)
From: "Mikkel Kamstrup Erlandsen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4007) Optimization of IN with nested SELECT
In-Reply-To: <788305512.1231429499915.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-4007?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mikkel Kamstrup Erlandsen updated DERBY-4007:
---------------------------------------------

    Attachment: dblook_p_index.log

The dblook dump for the database with the PC ((parentId,childId) on summa_relations) index dropped and replaced by a non-unique index P om parentId only.

In this setup the query still takes about 20-30s to complete.

> Optimization of IN with nested SELECT
> -------------------------------------
>
>                 Key: DERBY-4007
>                 URL: https://issues.apache.org/jira/browse/DERBY-4007
>             Project: Derby
>          Issue Type: Bug
>          Components: Performance
>    Affects Versions: 10.4.2.0
>         Environment: Linux
>            Reporter: Mikkel Kamstrup Erlandsen
>            Priority: Minor
>         Attachments: dblook.log, dblook_p_index.log, derby.log, derby_p_index.log
>
>
> The problem is with the following query:
> UPDATE summa_records SET base='foobar' WHERE id IN ( SELECT parentId FROM summa_relations WHERE childId='horizon_2615441');
> It takes in the order of 30s to run when we expect something in the order of 1-2ms.
> We have a setup with two tables
> summa_records:  1,5M rows
> summa_relations: ~350000 rows
> summa_records have and 'id' column that is also indexed and is the primary key. The summa_relations table holds mappings between different ids.
> In our case the nested SELECT produces 2 hits, say, 'foo' and 'bar'. So the UPDATE on these two hits should be quite snappy. If we run the SELECT alone it runs in an instant, and also if we run with hardcoded ids for the IN clause:
> UPDATE summa_records SET base='foobar' WHERE id IN ('foo', 'bar');
> We have instant execution. I'll attach a query plan in a sec.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.