explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y25x

Settings
# exclusive inclusive rows x rows loops node
1. 62.500 414,250.809 ↓ 2,507.0 2,507 1

Unique (cost=33.64..33.67 rows=1 width=174) (actual time=414,165.283..414,250.809 rows=2,507 loops=1)

2. 495.167 414,188.309 ↓ 87,184.0 87,184 1

Sort (cost=33.64..33.65 rows=1 width=174) (actual time=414,165.280..414,188.309 rows=87,184 loops=1)

  • Sort Key: "PC0".day, "PC0".pyissue, "PC0".pygroup, "PC0".pyname, "PC0".pytreatment, "PC0".uri, "PC0".placement, (CASE WHEN ((sum("Day".totaldecisio..nedcount)) IS NULL) THEN '0'::numeric ELSE (sum("Day".totaldecisionedcount)) END), (CASE WHEN ((sum("Day".totalviewcount)) IS NULL) THEN '0'::numeric ELSE ..(sum("Day".totalviewcount)) END), (CASE WHEN ((sum("Day".totalclickcount)) IS NULL) THEN '0'::numeric ELSE (sum("Day".totalclickcount)) END)
  • Sort Method: quicksort Memory: 16,257kB
3. 412,891.065 413,693.142 ↓ 87,184.0 87,184 1

Merge Join (cost=17.09..33.63 rows=1 width=174) (actual time=669.468..413,693.142 rows=87,184 loops=1)

  • Merge Cond: (("Day".day)::text = ("PC0".day)::text)
  • Join Filter: (("PC0".aggregatekey)::text = ("Day".aggregatekey)::text)
  • Rows Removed by Join Filter: 217,088,160
  • -> Index Scan using dayhouraggregatekey on pr_jpmc_mkt_data_aggregates "PC0" (cost=0.59..15.91 rows=25 width=112) (actual time=0.064..27736..2.965 rows=217,172,854 loops=1)
4. 73.795 802.077 ↓ 99.6 2,491 1

GroupAggregate (cost=16.49..17.31 rows=25 width=139) (actual time=669.394..802.077 rows=2,491 loops=1)

  • Group Key: "Day".day, "Day".aggregatekey
  • Index Cond: (((day)::text >= '20190924'::text) AND ((day)::text < CASE WHEN (to_char((timezone('UTC'::text, ('20190924'::cstring)::time..stamp without time zone) + '1 day'::interval), 'YYYYMMDD'::text) IS NULL) THEN to_char(((('now'::cstring)::date - 0))::timestamp with time zone, 'YYYYMMDD'..::text) ELSE to_char((timezone('UTC'::text, ('20190924'::cstring)::timestamp without time zone) + '1 day'::interval), 'YYYYMMDD'::text) END))
  • Filter: ((pxobjclass)::text = 'JPMC-MKT-Data-Aggregates'::text)
5. 728.282 728.282 ↓ 3,487.4 87,184 1

Sort (cost=16.49..16.56 rows=25 width=57) (actual time=669.336..728.282 rows=87,184 loops=1)

  • Sort Key: "Day".day, "Day".aggregatekey
  • Sort Method: quicksort Memory: 14,273kB
  • -> Index Scan using dayhouraggregatekey on pr_jpmc_mkt_data_aggregates "Day" (cost=0.59..15.91 rows=25 width=57) (actual time=0...076..141.076 rows=87,184 loops=1)
  • Index Cond: (((day)::text >= '20190924'::text) AND ((day)::text < CASE WHEN (to_char((timezone('UTC'::text, ('20190924'::cs..tring)::timestamp without time zone) + '1 day'::interval), 'YYYYMMDD'::text) IS NULL) THEN to_char(((('now'::cstring)::date - 0))::timestamp with time zone.., 'YYYYMMDD'::text) ELSE to_char((timezone('UTC'::text, ('20190924'::cstring)::timestamp without time zone) + '1 day'::interval), 'YYYYMMDD'::text) END))
  • Filter: ((pxobjclass)::text = 'JPMC-MKT-Data-Aggregates'::text)