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

[Jun Omae]

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[]))

[Jun Omae]

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

[Ryan J Ollos]

Looks like a good change!

[Jun Omae]

Sorry for the delay. I push the changes soon.

[Jun Omae]

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.

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