Rel8 Performance x100 Slower than SQL

[idontgetoutmuch]

I am using https://hackage.haskell.org/package/rel8-1.4.1.0 and have the following query in Haskell. Sadly it takes 180 seconds to run.

sumTest = do
  widgetsPerDay <- each productionWidget
  daysPerMonth <- each daysInMonth
  where_ $ cYear widgetsPerDay ==. Rel8.nullify (getYear daysPerMonth) &&.
           cMonth widgetsPerDay ==. Rel8.nullify (getMonth daysPerMonth)
  let pp = Rel8.groupBy (pPeriod widgetsPerDay)
      t  = Rel8.sum (pWidget widgetsPerDay * (Rel8.fromIntegral daysPerMonth.dDaysInMonth))
  return (pp, t)

When I code up the SQL by hand it takes 3 seconds.

SELECT
    wp.c_year AS production_year,
    wp.c_month AS production_month,
    SUM(wp.p_widgets * dp.d_days_in_month) AS total_widgets_produced
FROM
    production_widget wp
JOIN
    ref_days_in_month dp ON
        CAST(wp.c_year AS INTEGER) = EXTRACT(YEAR FROM dp.d_date) AND
        CAST(wp.c_month AS INTEGER) = EXTRACT(MONTH FROM dp.d_date)
GROUP BY
    wp.c_year, wp.c_month;

Here is what showQuery gives me:

"SELECT"
"CAST(\"result0_6\" AS text) as \"_1\","
"CAST(CAST(\"result1_6\" AS numeric) AS numeric) as \"_2\","
"CAST(CAST(\"result2_6\" AS numeric) AS numeric) as \"_3\""
"FROM (SELECT"
"      \"inner0_6\" as \"result0_6\","
"      SUM(\"inner1_6\") as \"result1_6\","
"      SUM(\"inner2_6\") as \"result2_6\""
"      FROM (SELECT"
"            \"p_period14_1\" as \"inner0_6\","
"            \"p_widgets62_1\" as \"inner1_6\","
"            (\"p_widgets62_1\") * (CAST(\"d_days_in_month1_3\" AS numeric)) as \"inner2_6\","
"            *"
"            FROM (SELECT"
"                  *"
"                  FROM (SELECT"
"                        \"c_months_since_start\" as \"c_months_since_start0_1\","
"                        \"d_days_in_month\" as \"d_days_in_month1_1\","
"                        \"c_quarter\" as \"c_quarter2_1\","
"                        \"c_year\" as \"c_year3_1\","
"                        \"c_month\" as \"c_month4_1\","
"                        ...
"                        \"p_widgets\" as \"p_widgets62_1\","
"                        FROM \"production_widget\" as \"T1\") as \"T1\","
"                       LATERAL"
"                       (SELECT"
"                        \"d_date\" as \"d_date0_3\","
"                        \"d_days_in_month\" as \"d_days_in_month1_3\""
"                        FROM \"ref_days_in_month\" as \"T1\") as \"T2\""
"                  WHERE (((((\"c_year3_1\") IS NULL) AND ((CAST(trunc(CAST(EXTRACT(YEAR FROM(CAST(\"d_date0_3\" AS timestamptz))) AS float8)) AS int2)) IS NULL)) OR (((\"c_year3_1\") = (CAST(trunc(CAST(EXTRACT(YEAR FROM(CAST(\"d_date0_3\" AS timestamptz))) AS float8)) AS int2))) AND (COALESCE((\"c_year3_1\") = (CAST(trunc(CAST(EXTRACT(YEAR FROM(CAST(\"d_date0_3\" AS timestamptz))) AS float8)) AS int2)),FALSE)))) AND ((((\"c_month4_1\") IS NULL) AND ((CAST(trunc(CAST(EXTRACT(MONTH FROM(CAST(\"d_date0_3\" AS timestamptz))) AS float8)) AS int2)) IS NULL)) OR (((\"c_month4_1\") = (CAST(trunc(CAST(EXTRACT(MONTH FROM(CAST(\"d_date0_3\" AS timestamptz))) AS float8)) AS int2))) AND (COALESCE((\"c_month4_1\") = (CAST(trunc(CAST(EXTRACT(MONTH FROM(CAST(\"d_date0_3\" AS timestamptz))) AS float8)) AS int2)),FALSE)))))) as \"T1\") as \"T1\""
"      GROUP BY \"inner0_6\") as \"T1\""

It looks very slow because there’s a query inside a query inside another query: 5 sub queries? But I am an SQL noob. Maybe I mis-formulated the Haskell?

[ocharles]

The first thing you should do is run both of these queries with EXPLAIN ANALYZE, that will at least remove the guess work as to where something is slow. Can you do that and post both results here?

[idontgetoutmuch]

          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=349100.71..349673.98 rows=780 width=36) (actual time=1547.967..1695.759 rows=822 loops=1)
   Group Key: wp.c_year, wp.c_month
   ->  Gather Merge  (cost=349100.71..349648.63 rows=1560 width=36) (actual time=1547.963..1695.119 rows=2406 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=348100.69..348468.55 rows=780 width=36) (actual time=1526.307..1671.224 rows=802 loops=3)
               Group Key: wp.c_year, wp.c_month
               ->  Sort  (cost=348100.69..348160.37 rows=23874 width=12) (actual time=1526.291..1565.306 rows=829621 loops=3)
                     Sort Key: wp.c_year, wp.c_month
                     Sort Method: external merge  Disk: 19128kB
                     Worker 0:  Sort Method: external merge  Disk: 18432kB
                     Worker 1:  Sort Method: external merge  Disk: 18848kB
                     ->  Merge Join  (cost=317412.33..346364.67 rows=23874 width=12) (actual time=1215.947..1423.656 rows=829621 loops=3)
                           Merge Cond: (((((wp.c_year)::integer)::double precision) = (date_part('year'::text, (dp.d_date)::timestamp without time zone))) AND ((((wp.c_month)::integer)::double precision) = (date_part('month'::text, (dp.d_date)::timestamp without time zone))))
                           ->  Sort  (cost=317347.78..319898.42 rows=1020257 width=10) (actual time=1215.691..1306.276 rows=829621 loops=3)
                                 Sort Key: (((wp.c_year)::integer)::double precision), (((wp.c_month)::integer)::double precision)
                                 Sort Method: external merge  Disk: 33456kB
                                 Worker 0:  Sort Method: external merge  Disk: 32280kB
                                 Worker 1:  Sort Method: external merge  Disk: 33016kB
                                 ->  Parallel Seq Scan on production_widget wp  (cost=0.00..198086.57 rows=1020257 width=10) (actual time=0.926..931.584 rows=829621 loops=3)
                           ->  Sort  (cost=64.55..66.89 rows=936 width=6) (actual time=0.249..16.810 rows=829701 loops=3)
                                 Sort Key: (date_part('year'::text, (dp.d_date)::timestamp without time zone)), (date_part('month'::text, (dp.d_date)::timestamp without time zone))
                                 Sort Method: quicksort  Memory: 98kB
                                 Worker 0:  Sort Method: quicksort  Memory: 98kB
                                 Worker 1:  Sort Method: quicksort  Memory: 98kB
                                 ->  Seq Scan on ref_days_in_month dp  (cost=0.00..18.36 rows=936 width=6) (actual time=0.045..0.185 rows=936 loops=3)
 Planning Time: 0.672 ms
 Execution Time: 1700.888 ms
(28 rows)

       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on t1  (cost=95328223.56..95328521.06 rows=703 width=96) (actual time=191257.543..191459.605 rows=822 loops=1)
   ->  Finalize GroupAggregate  (cost=95328223.56..95328514.03 rows=703 width=72) (actual time=191257.538..191459.545 rows=822 loops=1)
         Group Key: t1_1.p_period
         ->  Gather Merge  (cost=95328223.56..95328485.91 rows=1406 width=72) (actual time=191257.520..191458.402 rows=2420 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial GroupAggregate  (cost=95327223.53..95327323.60 rows=703 width=72) (actual time=191249.306..191446.884 rows=807 loops=3)
                     Group Key: t1_1.p_period
                     ->  Sort  (cost=95327223.53..95327238.45 rows=5968 width=16) (actual time=191249.280..191318.064 rows=829621 loops=3)
                           Sort Key: t1_1.p_period
                           Sort Method: external merge  Disk: 22048kB
                           Worker 0:  Sort Method: external merge  Disk: 22072kB
                           Worker 1:  Sort Method: external merge  Disk: 22056kB
                           ->  Nested Loop  (cost=0.00..95326849.25 rows=5968 width=16) (actual time=0.791..191001.585 rows=829621 loops=3)
                                 Join Filter: ((((t1_1.c_year IS NULL) AND ((trunc(date_part('year'::text, (t1_2.d_date)::timestamp with time zone)))::smallint IS NULL)) OR ((t1_1.c_year = (trunc(date_part('year'::text, (t1_2.d_date)::timestamp with time zone)))::smallint) AND COALESCE((t1_1.c_year = (trunc(date_part('year'::text, (t1_2.d_date)::timestamp with time zone)))::smallint), false))) AND (((t1_1.c_month IS NULL) AND ((trunc(date_part('month'::text, (t1_2.d_date)::timestamp with time zone)))::smallint IS NULL)) OR ((t1_1.c_month = (trunc(date_part('month'::text, (t1_2.d_date)::timestamp with time zone)))::smallint) AND COALESCE((t1_1.c_month = (trunc(date_part('month'::text, (t1_2.d_date)::timestamp with time zone)))::smallint), false))))
                                 Rows Removed by Join Filter: 775695635
                                 ->  Parallel Seq Scan on production_widget t1_1  (cost=0.00..198086.57 rows=1020257 width=18) (actual time=0.235..927.992 rows=829621 loops=3)
                                 ->  Seq Scan on ref_days_in_month t1_2  (cost=0.00..18.36 rows=936 width=6) (actual time=0.002..0.028 rows=936 loops=2488863)
 Planning Time: 0.808 ms
 Execution Time: 191461.269 ms
(20 rows)

[idontgetoutmuch]

I've just noticed that they are not quite equivalent:

GROUP BY
    wp.c_year, wp.c_month;

whereas

Rel8.groupBy (pPeriod widgetsPerDay)

[idontgetoutmuch]

@ocharles what do I say in Rel8 to get the equivalent of GROUP BY wp.c_year, wp.c_month? I can see how to group by 1 variable but how about 2 or more variables?

[ocharles]

You can just pass a tuple of Exprs to groupBy. Perhaps:

groupBy (pPeriodYear widgetsPerDay, pPeriodMonth widgetsPerDay)

or maybe:

groupBy (year (pPeriod widgetsPerDay), month (pPeriod widgetsPerDay))

(but I don't know what pPeriod is)

[idontgetoutmuch]

This gives

 Finalize GroupAggregate  (cost=95328223.56..95328528.24 rows=780 width=36) (actual time=188518.370..188686.717 rows=822 loops=1)
   Group Key: t1.c_year, t1.c_month
   ->  Gather Merge  (cost=95328223.56..95328502.89 rows=1560 width=36) (actual time=188518.363..188686.023 rows=2419 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=95327223.53..95327322.80 rows=780 width=36) (actual time=188511.896..188678.505 rows=806 loops=3)
               Group Key: t1.c_year, t1.c_month
               ->  Sort  (cost=95327223.53..95327238.45 rows=5968 width=12) (actual time=188511.880..188572.827 rows=829621 loops=3)
                     Sort Key: t1.c_year, t1.c_month
                     Sort Method: external merge  Disk: 18816kB
                     Worker 0:  Sort Method: external merge  Disk: 18792kB
                     Worker 1:  Sort Method: external merge  Disk: 18800kB
                     ->  Nested Loop  (cost=0.00..95326849.25 rows=5968 width=12) (actual time=4.141..188355.572 rows=829621 loops=3)
                           Join Filter: ((((t1.c_year IS NULL) AND ((trunc(date_part('year'::text, (t1_1.d_date)::timestamp with time zone)))::smallint IS NULL)) OR
			                 ((t1.c_year = (trunc(date_part('year'::text, (t1_1.d_date)::timestamp with time zone)))::smallint) AND
					  COALESCE((t1.c_year = (trunc(date_part('year'::text, (t1_1.d_date)::timestamp with time zone)))::smallint), false))) AND
			                (((t1.c_month IS NULL) AND ((trunc(date_part('month'::text, (t1_1.d_date)::timestamp with time zone)))::smallint IS NULL)) OR
					 ((t1.c_month = (trunc(date_part('month'::text, (t1_1.d_date)::timestamp with time zone)))::smallint) AND
					  COALESCE((t1.c_month = (trunc(date_part('month'::text, (t1_1.d_date)::timestamp with time zone)))::smallint), false))))
                           Rows Removed by Join Filter: 775695635
                           ->  Parallel Seq Scan on production_brazil t1  (cost=0.00..198086.57 rows=1020257 width=10) (actual time=0.623..571.236 rows=829621 loops=3)
                           ->  Seq Scan on ref_days_in_month t1_1  (cost=0.00..18.36 rows=936 width=6) (actual time=0.002..0.028 rows=936 loops=2488863)
 Planning Time: 0.985 ms
 Execution Time: 188687.476 ms
(19 rows)

versus the hand-written

 Finalize GroupAggregate  (cost=349100.71..349673.98 rows=780 width=36) (actual time=1547.967..1695.759 rows=822 loops=1)
   Group Key: wp.c_year, wp.c_month
   ->  Gather Merge  (cost=349100.71..349648.63 rows=1560 width=36) (actual time=1547.963..1695.119 rows=2406 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=348100.69..348468.55 rows=780 width=36) (actual time=1526.307..1671.224 rows=802 loops=3)
               Group Key: wp.c_year, wp.c_month
               ->  Sort  (cost=348100.69..348160.37 rows=23874 width=12) (actual time=1526.291..1565.306 rows=829621 loops=3)
                     Sort Key: wp.c_year, wp.c_month
                     Sort Method: external merge  Disk: 19128kB
                     Worker 0:  Sort Method: external merge  Disk: 18432kB
                     Worker 1:  Sort Method: external merge  Disk: 18848kB
                     ->  Merge Join  (cost=317412.33..346364.67 rows=23874 width=12) (actual time=1215.947..1423.656 rows=829621 loops=3)
                           Merge Cond: (((((wp.c_year)::integer)::double precision) = (date_part('year'::text, (dp.d_date)::timestamp without time zone))) AND
			                ((((wp.c_month)::integer)::double precision) = (date_part('month'::text, (dp.d_date)::timestamp without time zone))))
                           ->  Sort  (cost=317347.78..319898.42 rows=1020257 width=10) (actual time=1215.691..1306.276 rows=829621 loops=3)
                                 Sort Key: (((wp.c_year)::integer)::double precision), (((wp.c_month)::integer)::double precision)
                                 Sort Method: external merge  Disk: 33456kB
                                 Worker 0:  Sort Method: external merge  Disk: 32280kB
                                 Worker 1:  Sort Method: external merge  Disk: 33016kB
                                 ->  Parallel Seq Scan on production_widget wp  (cost=0.00..198086.57 rows=1020257 width=10) (actual time=0.926..931.584 rows=829621 loops=3)
                           ->  Sort  (cost=64.55..66.89 rows=936 width=6) (actual time=0.249..16.810 rows=829701 loops=3)
                                 Sort Key: (date_part('year'::text, (dp.d_date)::timestamp without time zone)), (date_part('month'::text, (dp.d_date)::timestamp without time zone))
                                 Sort Method: quicksort  Memory: 98kB
                                 Worker 0:  Sort Method: quicksort  Memory: 98kB
                                 Worker 1:  Sort Method: quicksort  Memory: 98kB
                                 ->  Seq Scan on ref_days_in_month dp  (cost=0.00..18.36 rows=936 width=6) (actual time=0.045..0.185 rows=936 loops=3)
 Planning Time: 0.672 ms
 Execution Time: 1700.888 ms
(28 rows)

[idontgetoutmuch]

Let me know if you need anything else. I am assuming that this is generated by Opaleye?

[ocharles]

I would imagine it's the join condition that's causing the problem. Your Rel8 one has a lot more going on with null checks. If you can get the join condition to match your hand written SQL, you may well get the same performance.