Edgewall Software
Modify

Opened 12 months ago

Closed 5 months ago

Last modified 7 weeks 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:
Release Notes:

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

API 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 Changed 12 months ago by Jun Omae

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

comment:2 Changed 12 months ago by figaro

Keywords: performance added

comment:3 Changed 5 months ago by Ryan J Ollos

Looks like a good change!

comment:4 Changed 5 months ago by Ryan J Ollos

Milestone: 1.0.171.0.18

comment:5 Changed 5 months ago by Jun Omae

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

Sorry for the delay. I push the changes soon.

comment:6 Changed 5 months ago by Jun Omae

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 Changed 5 months ago by Jun Omae

Resolution: fixed
Status: assignedclosed

comment:8 Changed 5 months ago by Ryan J Ollos

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.
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.