Re: Nested loop Query performance on PK - Mailing list pgsql-performance

From nha
Subject Re: Nested loop Query performance on PK
Date
Msg-id 4A6C8434.7040100@free.fr
Whole thread Raw
In response to Re: Nested loop Query performance on PK  (Greg Caulton <caultonpos@gmail.com>)
List pgsql-performance
Hello,

Le 26/07/09 7:09, Greg Caulton a écrit :
> On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton <caultonpos@gmail.com
> <mailto:caultonpos@gmail.com>> wrote:
>
>     Hello,
>
>     It seems to me that the following query should be a lot faster.
>     This runs in 17 seconds (regardless how many times I run it)
>
>     select ac.* from application_controls_view ac, refs r where
>     ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%XYZ%';
> [...]
>     Does this seem right to you?  Anything I can tune ?
> [...]
>
> Oh it seems to be the join that is throwing it off, because this runs in
> 600 ms
>
> select ac.* from application_controls_view ac
> where ac.application_control_id in (
> 50000745,
> 50000760,
> [...]
> 50021066,
> 50020808
> )
>
> never mind, makes sense now  - its fixed
> [...]

The following rewritten query may be satisfiable for the generic case of
using arbitrary LIKE pattern for refs.ref_key and performing in a short
acceptable time as well:

SELECT ac.*
FROM application_controls_view AS ac
INNER JOIN (
    SELECT ref_id
    FROM refs
    WHERE ref_key LIKE '%XYZ%'
) AS r
ON ac.custom_controller_ref_id = r.ref_id;

The hint is to build a subquery, from refs table, and to move in the
WHERE clause that only refers to refs column (ref_key here). This
subquery results in a shorter table than the original (refs here),
thence reducing the number of joins to perform with ac (no matter
working with view or original table).

Regards.

--
nha / Lyon / France.

pgsql-performance by date:

Previous
From: Greg Caulton
Date:
Subject: Re: Nested loop Query performance on PK
Next
From: Greg Smith
Date:
Subject: Re: hyperthreaded cpu still an issue in 8.4?