explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CKf5

Settings
# exclusive inclusive rows x rows loops node
1. 63.846 2,068.425 ↑ 4.1 2,507 1

Unique (cost=118,324.07..118,605.14 rows=10,221 width=174) (actual time=1,979.968..2,068.425 rows=2,507 loops=1)

2. 424.972 2,004.579 ↓ 8.5 87,184 1

Sort (cost=118,324.07..118,349.62 rows=10,221 width=174) (actual time=1,979.966..2,004.579 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. 63.698 1,579.607 ↓ 8.5 87,184 1

Merge Join (cost=112,806.62..117,643.39 rows=10,221 width=174) (actual time=1,402.580..1,579.607 rows=87,184 loops=1)

  • Merge Cond: ((("Day".day)::text = ("PC0".day)::text) AND (("Day".aggregatekey)::text = ("PC0".aggregatekey)::text))
4. 52.770 743.385 ↑ 33.8 2,491 1

GroupAggregate (cost=56,403.31..59,209.12 rows=84,134 width=139) (actual time=661.256..743.385 rows=2,491 loops=1)

  • Group Key: "Day".day, "Day".aggregatekey
5. 690.615 690.615 ↑ 1.0 87,184 1

Sort (cost=56,403.31..56,625.55 rows=88,898 width=57) (actual time=661.199..690.615 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..49095.95 rows=88,898 width=57) (actual ..time=0.036..135.187 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)
6. 772.524 772.524 ↑ 1.0 87,184 1

Sort (cost=56,403.31..56,625.55 rows=88,898 width=112) (actual time=741.315..772.524 rows=87,184 loops=1)

  • Sort Key: "PC0".day, "PC0".aggregatekey
  • Sort Method: quicksort Memory: 19,808kB
  • -> Index Scan using dayhouraggregatekey on pr_jpmc_mkt_data_aggregates "PC0" (cost=0.59..49095.95 rows=88,898 width=112) (actual time=..0.057..138.846 rows=87,184 loops=1)
  • Index Cond: (((day)::text >= '20190924'::text) AND ((day)::text < CASE WHEN (to_char((timezone('UTC'::text, ('20190924'::cstring)..::timestamp without time zone) + '1 day'::interval), 'YYYYMMDD'::text) IS NULL) THEN to_char(((('now'::cstring)::date - 0))::timestamp with time zone, 'YYY..YMMDD'::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)