explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hS4k

Settings
# exclusive inclusive rows x rows loops node
1. 29.525 14,566.756 ↑ 1.0 2,801 1

Sort (cost=253,044.67..253,046.11 rows=2,869 width=3,569) (actual time=14,566.200..14,566.756 rows=2,801 loops=1)

  • Sort Key: a.id DESC, b.revenue DESC
  • Sort Method: quicksort Memory: 1318kB
2.          

CTE spend_data

3. 13.914 5,633.706 ↑ 4.1 3,139 1

Finalize GroupAggregate (cost=170,815.04..171,765.13 rows=12,958 width=92) (actual time=5,471.045..5,633.706 rows=3,139 loops=1)

  • Group Key: a_1.campaign_id, a_1.publisher_id, (COALESCE(a_1.medias_id, '-1'::bigint))
4. 0.000 5,619.792 ↑ 1.6 4,748 1

Gather Merge (cost=170,815.04..171,680.14 rows=7,622 width=68) (actual time=5,471.010..5,619.792 rows=4,748 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 26.296 10,825.712 ↑ 3.2 2,374 2

Partial GroupAggregate (cost=169,815.04..169,868.39 rows=7,622 width=68) (actual time=5,398.910..5,412.856 rows=2,374 loops=2)

  • Group Key: a_1.campaign_id, a_1.publisher_id, (COALESCE(a_1.medias_id, '-1'::bigint))
6. 24.472 10,799.416 ↑ 1.9 4,094 2

Sort (cost=169,815.04..169,818.85 rows=7,622 width=44) (actual time=5,398.885..5,399.708 rows=4,094 loops=2)

  • Sort Key: a_1.campaign_id, a_1.publisher_id, (COALESCE(a_1.medias_id, '-1'::bigint))
  • Sort Method: quicksort Memory: 524kB
7. 65.750 10,774.944 ↑ 1.9 4,094 2

Hash Join (cost=10,395.73..169,716.75 rows=7,622 width=44) (actual time=2,272.252..5,387.472 rows=4,094 loops=2)

  • Hash Cond: (a_1.publisher_id = c.publisher_id)
8. 8,757.612 10,708.352 ↓ 1.0 32,214 2

Parallel Bitmap Heap Scan on rollup_table a_1 (cost=10,385.31..169,689.97 rows=31,092 width=44) (actual time=2,271.719..5,354.176 rows=32,214 loops=2)

  • Recheck Cond: (medias_id IS NOT NULL)
  • Filter: ((campaign_id IS NOT NULL) AND (publisher_id IS NOT NULL) AND (country IS NULL) AND (platform IS NULL) AND (ds >= '2019-07-01'::date) AND (ds <= '2019-08-02'::date) AND (metric_name = 'revenue'::text))
  • Rows Removed by Filter: 704749
  • Heap Blocks: exact=62060
9. 1,950.740 1,950.740 ↓ 1.0 1,489,299 1

Bitmap Index Scan on rollup_table_medias_id (cost=0.00..10,382.66 rows=1,480,384 width=0) (actual time=1,950.740..1,950.740 rows=1,489,299 loops=1)

  • Index Cond: (medias_id IS NOT NULL)
10. 0.366 0.842 ↓ 1.0 692 2

Hash (cost=8.04..8.04 rows=680 width=4) (actual time=0.421..0.421 rows=692 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
11. 0.476 0.476 ↓ 1.0 692 2

Seq Scan on employees c (cost=0.00..8.04 rows=680 width=4) (actual time=0.031..0.238 rows=692 loops=2)

12.          

CTE clicks

13. 476.130 7,554.109 ↓ 2.3 15,766 1

GroupAggregate (cost=77,791.96..77,826.29 rows=6,855 width=24) (actual time=7,029.751..7,554.109 rows=15,766 loops=1)

  • Group Key: rollup_table.publisher_id, rollup_table.medias_id
14. 772.689 7,077.979 ↓ 14.2 97,904 1

Sort (cost=77,791.96..77,795.40 rows=6,885 width=24) (actual time=7,029.733..7,077.979 rows=97,904 loops=1)

  • Sort Key: rollup_table.publisher_id, rollup_table.medias_id
  • Sort Method: quicksort Memory: 10721kB
15. 2,166.640 6,305.290 ↓ 14.2 97,904 1

Bitmap Heap Scan on rollup_table (cost=37,421.62..77,704.18 rows=6,885 width=24) (actual time=4,259.903..6,305.290 rows=97,904 loops=1)

  • Recheck Cond: ((medias_id IS NOT NULL) AND (ds >= '2019-07-01'::date) AND (ds <= '2019-08-02'::date) AND (metric_name = 'app_store_clicks'::text))
  • Filter: ((publisher_id IS NOT NULL) AND (campaign_id IS NOT NULL) AND (platform IS NULL) AND (country IS NULL))
  • Heap Blocks: exact=25370
16. 120.039 4,138.650 ↓ 0.0 0 1

BitmapAnd (cost=37,421.62..37,421.62 rows=27,345 width=0) (actual time=4,138.650..4,138.650 rows=0 loops=1)

17. 1,520.850 1,520.850 ↓ 1.0 1,489,299 1

Bitmap Index Scan on rollup_table_medias_id (cost=0.00..10,382.66 rows=1,480,384 width=0) (actual time=1,520.849..1,520.850 rows=1,489,299 loops=1)

  • Index Cond: (medias_id IS NOT NULL)
18. 2,497.761 2,497.761 ↑ 1.2 111,878 1

Bitmap Index Scan on rollup_table_ds_metric_name (cost=0.00..27,038.22 rows=133,152 width=0) (actual time=2,497.761..2,497.761 rows=111,878 loops=1)

  • Index Cond: ((ds >= '2019-07-01'::date) AND (ds <= '2019-08-02'::date) AND (metric_name = 'app_store_clicks'::text))
19.          

CTE test_data_rollup

20. 56.571 8,648.520 ↑ 1.0 2,801 1

Sort (cost=3,013.40..3,014.83 rows=2,869 width=249) (actual time=8,623.954..8,648.520 rows=2,801 loops=1)

  • Sort Key: a_2.created_at DESC
  • Sort Method: quicksort Memory: 846kB
21. 54.294 8,591.949 ↑ 1.0 2,801 1

Hash Left Join (cost=2,945.54..2,980.44 rows=2,869 width=249) (actual time=8,476.228..8,591.949 rows=2,801 loops=1)

  • Hash Cond: (a_2.publisher_id = e.test_publisher_id)
22. 12.904 8,537.119 ↓ 1.0 2,801 1

Merge Left Join (cost=2,918.07..2,932.47 rows=2,733 width=192) (actual time=8,475.663..8,537.119 rows=2,801 loops=1)

  • Merge Cond: ((a_2.publisher_id = f.publisher_id) AND (a_2.medias_id = f.medias_id))
23. 42.714 670.714 ↓ 1.0 2,801 1

Sort (cost=2,789.59..2,790.96 rows=2,733 width=184) (actual time=670.178..670.714 rows=2,801 loops=1)

  • Sort Key: a_2.publisher_id, a_2.medias_id
  • Sort Method: quicksort Memory: 841kB
24. 1.867 628.000 ↓ 1.0 2,801 1

Hash Left Join (cost=2,344.67..2,758.39 rows=2,733 width=184) (actual time=293.354..628.000 rows=2,801 loops=1)

  • Hash Cond: (a_2.medias_id = d.id)
25. 21.317 521.250 ↓ 1.0 2,801 1

Hash Left Join (cost=2,014.36..2,426.64 rows=2,733 width=170) (actual time=188.422..521.250 rows=2,801 loops=1)

  • Hash Cond: (a_2.owner = c_1.id)
26. 24.341 312.148 ↓ 1.0 2,801 1

Hash Join (cost=404.39..815.24 rows=2,733 width=164) (actual time=0.501..312.148 rows=2,801 loops=1)

  • Hash Cond: (a_2.campaign_id = b_1.id)
27. 287.457 287.457 ↓ 1.0 2,801 1

Seq Scan on test_data a_2 (cost=0.00..409.36 rows=2,733 width=151) (actual time=0.140..287.457 rows=2,801 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((account IS NULL) OR ((account)::text ~~* '%%'::text)) AND (created_at >= '2019-07-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-08-02 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 12504
28. 0.066 0.350 ↑ 1.0 171 1

Hash (cost=403.80..403.80 rows=171 width=17) (actual time=0.350..0.350 rows=171 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
29. 0.284 0.284 ↑ 1.0 171 1

Index Scan using campaigns_pkey on campaigns b_1 (cost=0.05..403.80 rows=171 width=17) (actual time=0.009..0.284 rows=171 loops=1)

30. 6.164 187.785 ↑ 2.2 16,379 1

Hash (cost=1,482.91..1,482.91 rows=36,302 width=10) (actual time=187.785..187.785 rows=16,379 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1214kB
31. 181.621 181.621 ↑ 2.2 16,379 1

Seq Scan on publishers c_1 (cost=0.00..1,482.91 rows=36,302 width=10) (actual time=0.011..181.621 rows=16,379 loops=1)

32. 24.121 104.883 ↓ 1.0 9,947 1

Hash (cost=295.68..295.68 rows=9,894 width=18) (actual time=104.882..104.883 rows=9,947 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
33. 80.762 80.762 ↓ 1.0 9,947 1

Seq Scan on medias d (cost=0.00..295.68 rows=9,894 width=18) (actual time=0.014..80.762 rows=9,947 loops=1)

34. 151.613 7,853.501 ↓ 2.3 15,709 1

Sort (cost=128.48..131.91 rows=6,855 width=24) (actual time=7,803.841..7,853.501 rows=15,709 loops=1)

  • Sort Key: f.publisher_id, f.medias_id
  • Sort Method: quicksort Memory: 1616kB
35. 7,701.888 7,701.888 ↓ 2.3 15,766 1

CTE Scan on clicks f (cost=0.00..41.13 rows=6,855 width=24) (actual time=7,029.755..7,701.888 rows=15,766 loops=1)

36. 0.208 0.536 ↑ 1.0 688 1

Hash (cost=25.06..25.06 rows=688 width=24) (actual time=0.536..0.536 rows=688 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
37. 0.328 0.328 ↑ 1.0 688 1

Seq Scan on test_publishers e (cost=0.00..25.06 rows=688 width=24) (actual time=0.015..0.328 rows=688 loops=1)

38. 147.149 14,537.231 ↑ 1.0 2,801 1

Merge Left Join (cost=304.94..405.47 rows=2,869 width=3,569) (actual time=14,389.096..14,537.231 rows=2,801 loops=1)

  • Merge Cond: ((a.publisher_id = b.publisher_id) AND (a.medias_id = b.medias_id) AND (a.campaign_id = b.campaign_id))
39. 80.721 8,732.087 ↑ 1.0 2,801 1

Sort (cost=50.17..51.60 rows=2,869 width=3,333) (actual time=8,731.496..8,732.087 rows=2,801 loops=1)

  • Sort Key: a.publisher_id, a.medias_id, a.campaign_id
  • Sort Method: quicksort Memory: 842kB
40. 8,651.366 8,651.366 ↑ 1.0 2,801 1

CTE Scan on test_data_rollup a (cost=0.00..17.21 rows=2,869 width=3,333) (actual time=8,623.960..8,651.366 rows=2,801 loops=1)

41. 5.381 5,657.995 ↑ 4.1 3,144 1

Sort (cost=254.77..261.25 rows=12,958 width=92) (actual time=5,657.573..5,657.995 rows=3,144 loops=1)

  • Sort Key: b.publisher_id, b.medias_id, b.campaign_id
  • Sort Method: quicksort Memory: 342kB
42. 5,652.614 5,652.614 ↑ 4.1 3,139 1

CTE Scan on spend_data b (cost=0.00..77.75 rows=12,958 width=92) (actual time=5,471.050..5,652.614 rows=3,139 loops=1)

Planning time : 9.644 ms
Execution time : 14,648.821 ms