Re: [PERFORM] Nested loop Query performance on PK

2009-07-26 Thread nha 
Hello,

Le 26/07/09 7:09, Greg Caulton a écrit :
 On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton caulton...@gmail.com
 mailto:caulton...@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 (
 5745,
 5760, 
 [...]
 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Nested loop Query performance on PK

2009-07-25 Thread Greg Caulton 
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%';

if I do not use the view the query runs in under 100 ms

select ac.* from application_controls ac, refs r where
ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%XYZ%';


The view is

 SELECT t.action_form_type_ref_id, r1.display AS action_form_type_display,
t.action_order_type_ref_id, r2.display AS action_order_type_display,
t.action_view_ref_id, r3.display AS action_view_display, t.active_ind,
t.application_control_id, t.application_control_name, t.application_view_id,
t.background_color, t.background_processing_ind, t.base_model_type_ref_id,
r4.display AS base_model_type_display, t.base_value_script,
t.class_instantiate_script, t.class_name, t.combo_add_to_list_ind,
t.combo_category_ref_id, r5.display AS combo_category_display,
t.combo_dynamic_search_ind, t.combo_filter_ref_id, r6.display AS
combo_filter_display, t.combo_group_ref_id, r7.display AS
combo_group_display, t.combo_short_display_ind, t.combo_values_term_id,
t.comparison_operator_ref_id, r8.display AS comparison_operator_display,
t.context_ref_id, r9.display AS context_display, t.control_description,
t.control_format, t.control_format_ref_id, r10.display AS
 snip for brevity
t.parameter_ref_id = r30.ref_id AND t.parameter_source_ref_id = r31.ref_id
AND t.record_item_ref_id = r32.ref_id AND t.repeating_section_view_ref_id =
r33.ref_id AND t.report_print_ref_id = r34.ref_id AND
t.right_arrow_action_ref_id = r35.ref_id AND t.right_click_action_ref_id =
r36.ref_id AND t.section_view_ref_id = r37.ref_id AND t.select_action_ref_id
= r38.ref_id AND t.source_ref_id = r39.ref_id AND t.state_field_type_ref_id
= r40.ref_id AND t.table_access_ref_id = r41.ref_id AND t.update_user_ref_id
= r42.ref_id AND t.value_data_type_ref_id = r43.ref_id;

so basically it joins 43 times to the refs table on the primary key

the explain confirms the nested loops


   {NESTLOOP 
   :startup_cost 2660771.70 
   :total_cost 3317979.85 
   :plan_rows 27 
   :plan_width 4708 
   :targetlist (
  {TARGETENTRY 
  :expr 
 {VAR 
 :varno 65001 
 :varattno 29 
 :vartype 20 
 :vartypmod -1 
 :varlevelsup 0 
 :varnoold 5 
snip for brevity
  -  Index Scan using refs_pk on refs r17  (cost=0.00..5.45
rows=1 width=50)
Index Cond: (r17.ref_id = t.detail_record_item_ref_id)
-  Index Scan using refs_pk on refs r1  (cost=0.00..5.45 rows=1
width=50)
  Index Cond: (r1.ref_id = t.action_form_type_ref_id)
  -  Index Scan using refs_pk on refs r  (cost=0.00..5.45 rows=1 width=8)
Index Cond: (r.ref_id = t.custom_controller_ref_id)
Filter: ((r.ref_key)::text ~~ '%ERNEST%'::text)


I did a vacuum analyze and so the primary key (indexes of course) is being
used.  But the above query is still 17s.  If I dont return so many columns
it comes down to around 10 seconds.

select ac.application_control_id from application_controls_view ac, refs r
where ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%ERNEST%';

But in either case this is only 37 rows.  So 1554 lookups on a unique index
on a table of 34000 rows means 6ms per internal join - note that many of
those values are the same.

Does this seem right to you?  Anything I can tune ?



-- 
Gregory Caulton
Principal at PatientOS Inc.
personal email: caulton...@gmail.com
http://www.patientos.com
corporate: (888)-NBR-1EMR || fax  857.241.3022

Re: [PERFORM] Nested loop Query performance on PK

2009-07-25 Thread Greg Caulton 
On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton caulton...@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%';

 if I do not use the view the query runs in under 100 ms

 select ac.* from application_controls ac, refs r where
 ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%XYZ%';


 The view is

  SELECT t.action_form_type_ref_id, r1.display AS action_form_type_display,
 t.action_order_type_ref_id, r2.display AS action_order_type_display,
 t.action_view_ref_id, r3.display AS action_view_display, t.active_ind,
 t.application_control_id, t.application_control_name, t.application_view_id,
 t.background_color, t.background_processing_ind, t.base_model_type_ref_id,
 r4.display AS base_model_type_display, t.base_value_script,
 t.class_instantiate_script, t.class_name, t.combo_add_to_list_ind,
 t.combo_category_ref_id, r5.display AS combo_category_display,
 t.combo_dynamic_search_ind, t.combo_filter_ref_id, r6.display AS
 combo_filter_display, t.combo_group_ref_id, r7.display AS
 combo_group_display, t.combo_short_display_ind, t.combo_values_term_id,
 t.comparison_operator_ref_id, r8.display AS comparison_operator_display,
 t.context_ref_id, r9.display AS context_display, t.control_description,
 t.control_format, t.control_format_ref_id, r10.display AS
  snip for brevity
 t.parameter_ref_id = r30.ref_id AND t.parameter_source_ref_id = r31.ref_id
 AND t.record_item_ref_id = r32.ref_id AND t.repeating_section_view_ref_id =
 r33.ref_id AND t.report_print_ref_id = r34.ref_id AND
 t.right_arrow_action_ref_id = r35.ref_id AND t.right_click_action_ref_id =
 r36.ref_id AND t.section_view_ref_id = r37.ref_id AND t.select_action_ref_id
 = r38.ref_id AND t.source_ref_id = r39.ref_id AND t.state_field_type_ref_id
 = r40.ref_id AND t.table_access_ref_id = r41.ref_id AND t.update_user_ref_id
 = r42.ref_id AND t.value_data_type_ref_id = r43.ref_id;

 so basically it joins 43 times to the refs table on the primary key

 the explain confirms the nested loops


{NESTLOOP 
:startup_cost 2660771.70 
:total_cost 3317979.85 
:plan_rows 27 
:plan_width 4708 
:targetlist (
   {TARGETENTRY 
   :expr 
  {VAR 
  :varno 65001 
  :varattno 29 
  :vartype 20 
  :vartypmod -1 
  :varlevelsup 0 
  :varnoold 5 
 snip for brevity
   -  Index Scan using refs_pk on refs r17  (cost=0.00..5.45
 rows=1 width=50)
 Index Cond: (r17.ref_id =
 t.detail_record_item_ref_id)
 -  Index Scan using refs_pk on refs r1  (cost=0.00..5.45 rows=1
 width=50)
   Index Cond: (r1.ref_id = t.action_form_type_ref_id)
   -  Index Scan using refs_pk on refs r  (cost=0.00..5.45 rows=1
 width=8)
 Index Cond: (r.ref_id = t.custom_controller_ref_id)
 Filter: ((r.ref_key)::text ~~ '%ERNEST%'::text)


 I did a vacuum analyze and so the primary key (indexes of course) is being
 used.  But the above query is still 17s.  If I dont return so many columns
 it comes down to around 10 seconds.

 select ac.application_control_id from application_controls_view ac, refs r
 where ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%ERNEST%';

 But in either case this is only 37 rows.  So 1554 lookups on a unique index
 on a table of 34000 rows means 6ms per internal join - note that many of
 those values are the same.

 Does this seem right to you?  Anything I can tune ?



 --
 Gregory Caulton
 Principal at PatientOS Inc.
 personal email: caulton...@gmail.com
 http://www.patientos.com
 corporate: (888)-NBR-1EMR || fax  857.241.3022


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 (
5745,
5760,
5759,
5758,
5757,
5756,
5753,
5751,
5750,
5749,
5748,
5746,
5744,
50001328,
5752,
5754,
5755,
50002757,
50002756,
50002755,
50002754,
50001168,
50020825,
50021077,
50020821,
50020822,
50020824,
50020823,
50020820,
50020819,
50020809,
50020810,
50020806,
50020807,
50020817,
50021066,
50020808
)



never mind, makes sense now  - its fixed


-- 
Gregory Caulton
Principal at PatientOS Inc.
personal email: caulton...@gmail.com
http://www.patientos.com
corporate: (888)-NBR-1EMR || fax  857.241.3022