Skip site navigation (1) Skip section navigation (2)

Re: Nested loop Query performance on PK

From: Greg Caulton <caultonpos(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Nested loop Query performance on PK
Date: 2009-07-26 05:09:32
Message-ID: e44fb6470907252209i7d10dc6au691829de591df27c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton <caultonpos(at)gmail(dot)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: caultonpos(at)gmail(dot)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 (
50000745,
50000760,
50000759,
50000758,
50000757,
50000756,
50000753,
50000751,
50000750,
50000749,
50000748,
50000746,
50000744,
50001328,
50000752,
50000754,
50000755,
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: caultonpos(at)gmail(dot)com
http://www.patientos.com
corporate: (888)-NBR-1EMR || fax  857.241.3022

In response to

Responses

pgsql-performance by date

Next:From: nhaDate: 2009-07-26 16:28:36
Subject: Re: Nested loop Query performance on PK
Previous:From: Greg CaultonDate: 2009-07-26 05:02:42
Subject: Nested loop Query performance on PK

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group