Hello, Le 26/07/09 7:09, Greg Caulton a écrit : > On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton <caultonpos@xxxxxxxxx > <mailto:caultonpos@xxxxxxxxx>> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
