Edgewall Software
Modify

Opened 3 years ago

Closed 2 years ago

Last modified 2 years ago

#12967 closed enhancement (fixed)

Reduce joins and subqueries in query module when many custom fields are used

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.0.17
Component: query system Version: 1.0.15
Severity: normal Keywords: join subquery performance
Cc: Branch:
Release Notes:

Reduce number of joins and subqueries in SQL generated by query module.

API Changes:
Internal Changes:

Description

Query module generates costly SQL with joins and/or subqueries when many custom fields are used.

EXPLAIN
SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,
  t.priority AS priority,t.milestone AS milestone,t.component AS component,
  t.time AS time,t.changetime AS changetime,t.owner AS owner,
  priority.value AS priority_value,
  t."col1" AS "col1", t."col2" AS "col2", t."col3" AS "col3", t."col4" AS "col4",
  t."col5" AS "col5", t."col6" AS "col6", t."col7" AS "col7", t."col8" AS "col8",
  t."col9" AS "col9", t."col10" AS "col10", t."col11" AS "col11", t."col12" AS "col12",
  t."col13" AS "col13", t."col14" AS "col14", t."col15" AS "col15", t."col16" AS "col16",
  t."col17" AS "col17", t."col18" AS "col18", t."col19" AS "col19", t."col20" AS "col20",
  t."col21" AS "col21", t."col22" AS "col22", t."col23" AS "col23", t."col24" AS "col24",
  t."col25" AS "col25", t."col26" AS "col26", t."col27" AS "col27", t."col28" AS "col28",
  t."col29" AS "col29", t."col30" AS "col30", t."col31" AS "col31", t."col32" AS "col32",
  t."col33" AS "col33", t."col34" AS "col34", t."col35" AS "col35", t."col36" AS "col36",
  t."col37" AS "col37", t."col38" AS "col38", t."col39" AS "col39", t."col40" AS "col40"
FROM (
  SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,
    t.priority AS priority,t.milestone AS milestone,t.component AS component,
    t.time AS time,t.changetime AS changetime,t.owner AS owner,
    (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='col1') AS "col1",
    (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='col2') AS "col2",
    (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='col3') AS "col3",
    ...
    (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='col40') AS "col40"
  FROM ticket AS t) AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS integer),t.id

 QUERY PLAN
-----------------------------------
 Sort  (cost=334537.54..334540.07 rows=1011 width=98)
   Sort Key: ((COALESCE(priority.value, ''::text) = ''::text)), ((priority.value)::integer), t.id
   ->  Hash Left Join  (cost=11.42..334487.09 rows=1011 width=98)
         Hash Cond: (t.priority = priority.name)
         ->  Seq Scan on ticket t  (cost=0.00..22.11 rows=1011 width=66)
         ->  Hash  (cost=11.38..11.38 rows=3 width=64)
               ->  Bitmap Heap Scan on enum priority  (cost=4.27..11.38 rows=3 width=64)
                     Recheck Cond: (type = 'priority'::text)
                     ->  Bitmap Index Scan on enum_pk  (cost=0.00..4.27 rows=3 width=0)
                           Index Cond: (type = 'priority'::text)
         SubPlan 1
           ->  Index Scan using ticket_custom_pk on ticket_custom c  (cost=0.00..8.27 rows=1 width=32)
                 Index Cond: ((ticket = t.id) AND (name = 'col1'::text))
         SubPlan 2
           ->  Index Scan using ticket_custom_pk on ticket_custom c  (cost=0.00..8.27 rows=1 width=32)
                 Index Cond: ((ticket = t.id) AND (name = 'col2'::text))
...

I noticed we could improve the SQL with SELECT MAX(CASE WHEN name='col1' THEN value END) AS "col1" FROM ticket_custom:

EXPLAIN
SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,
  t.priority AS priority,t.milestone AS milestone,t.component AS component,
  t.time AS time,t.changetime AS changetime,t.owner AS owner,
  priority.value AS priority_value,
  c."col1" AS "col1", c."col2" AS "col2", c."col3" AS "col3", c."col4" AS "col4",
  c."col5" AS "col5", c."col6" AS "col6", c."col7" AS "col7", c."col8" AS "col8",
  c."col9" AS "col9", c."col10" AS "col10", c."col11" AS "col11", c."col12" AS "col12",
  c."col13" AS "col13", c."col14" AS "col14", c."col15" AS "col15", c."col16" AS "col16",
  c."col17" AS "col17", c."col18" AS "col18", c."col19" AS "col19", c."col20" AS "col20",
  c."col21" AS "col21", c."col22" AS "col22", c."col23" AS "col23", c."col24" AS "col24",
  c."col25" AS "col25", c."col26" AS "col26", c."col27" AS "col27", c."col28" AS "col28",
  c."col29" AS "col29", c."col30" AS "col30", c."col31" AS "col31", c."col32" AS "col32",
  c."col33" AS "col33", c."col34" AS "col34", c."col35" AS "col35", c."col36" AS "col36",
  c."col37" AS "col37", c."col38" AS "col38", c."col39" AS "col39", c."col40" AS "col40"
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
  LEFT OUTER JOIN (
    SELECT ticket AS id,
      MAX(CASE WHEN name='col1' THEN value END) AS "col1",
      MAX(CASE WHEN name='col2' THEN value END) AS "col2",
      MAX(CASE WHEN name='col3' THEN value END) AS "col3",
      ...
      MAX(CASE WHEN name='col40' THEN value END) AS "col40"
    FROM ticket_custom AS tc
    WHERE name IN ('col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col10',
                   'col11', 'col12', 'col13', 'col14', 'col15', 'col16', 'col17', 'col18', 'col19', 'col20',
                   'col21', 'col22', 'col23', 'col24', 'col25', 'col26', 'col27', 'col28', 'col29', 'col30',
                   'col31', 'col32', 'col33', 'col34', 'col35', 'col36', 'col37', 'col38', 'col39', 'col40')
    GROUP BY tc.ticket
  ) AS c ON c.id=t.id
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS integer),t.id

 QUERY PLAN
-----------------------------------
 Sort  (cost=800.25..802.78 rows=1011 width=1378)
   Sort Key: ((COALESCE(priority.value, ''::text) = ''::text)), ((priority.value)::integer), t.id
   ->  Hash Left Join  (cost=103.00..140.79 rows=1011 width=1378)
         Hash Cond: (t.id = c.id)
         ->  Hash Left Join  (cost=11.42..37.47 rows=1011 width=98)
               Hash Cond: (t.priority = priority.name)
               ->  Seq Scan on ticket t  (cost=0.00..22.11 rows=1011 width=66)
               ->  Hash  (cost=11.38..11.38 rows=3 width=64)
                     ->  Bitmap Heap Scan on enum priority  (cost=4.27..11.38 rows=3 width=64)
                           Recheck Cond: (type = 'priority'::text)
                           ->  Bitmap Index Scan on enum_pk  (cost=0.00..4.27 rows=3 width=0)
                                 Index Cond: (type = 'priority'::text)
         ->  Hash  (cost=91.12..91.12 rows=37 width=1284)
               ->  Subquery Scan on c  (cost=90.38..91.12 rows=37 width=1284)
                     ->  HashAggregate  (cost=90.38..90.75 rows=37 width=68)
                           ->  Seq Scan on ticket_custom tc  (cost=0.00..59.80 rows=151 width=68)
                                 Filter: (name = ANY ('{col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40}'::text[]))

Attachments (0)

Change History (8)

comment:1 by Jun Omae, 3 years ago

Proposed changes in [1b839b9e8/jomae.git] (jomae.git@t12967).

comment:2 by figaro, 3 years ago

Keywords: performance added

comment:3 by Ryan J Ollos, 2 years ago

Looks like a good change!

comment:4 by Ryan J Ollos, 2 years ago

Milestone: 1.0.171.0.18

comment:5 by Jun Omae, 2 years ago

Milestone: 1.0.181.0.17
Owner: set to Jun Omae
Status: newassigned

Sorry for the delay. I push the changes soon.

comment:6 by Jun Omae, 2 years ago

Release Notes: modified (diff)

Committed in [16705] and merged in [16706-16707].

I noticed tzinfo and locale parameters of Query.get_sql() are no longer used. I think we could mark the parameters of Query.{get_sql,count,execute} as deprecated in trunk.

comment:7 by Jun Omae, 2 years ago

Resolution: fixed
Status: assignedclosed

comment:8 by Ryan J Ollos, 2 years ago

Deprecated parameters in [16711,16714], merged in [16712,16715,16717], [16713,16716,16718]. Sorry for the mess, it seems I'm not awake yet. I'll post removal of parameters on next update of staged changes in #12787.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.