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

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: caultonpos@gmail.com
http://www.patientos.com
corporate: (888)-NBR-1EMR || fax  857.241.3022