explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gojvF

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 283,694.607 ↑ 5.0 5 1

Append (cost=1,553,755.65..1,799,730.96 rows=25 width=64) (actual time=282,441.418..283,694.607 rows=5 loops=1)

2. 0.014 283,641.180 ↑ 1.8 5 1

Result (cost=1,553,755.65..1,777,195.91 rows=9 width=64) (actual time=282,441.418..283,641.180 rows=5 loops=1)

3. 0.003 283,641.166 ↑ 1.8 5 1

Append (cost=1,553,755.65..1,777,195.79 rows=9 width=40) (actual time=282,441.412..283,641.166 rows=5 loops=1)

4. 0.000 282,459.837 ↑ 2.0 4 1

Finalize GroupAggregate (cost=1,553,755.65..1,553,865.01 rows=8 width=17) (actual time=282,441.412..282,459.837 rows=4 loops=1)

  • Group Key: o."OutcomeType
5. 0.000 282,459.881 ↑ 1.0 8 1

Gather Merge (cost=1,553,755.65..1,553,864.89 rows=8 width=17) (actual time=282,441.151..282,459.881 rows=8 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
6. 49.362 564,858.812 ↑ 2.0 4 2

Partial GroupAggregate (cost=1,552,755.64..1,552,863.98 rows=8 width=17) (actual time=282,411.685..282,429.406 rows=4 loops=2)

  • Group Key: o."OutcomeType
7. 186.396 564,809.450 ↓ 7.9 113,497 2

Sort (cost=1,552,755.64..1,552,791.73 rows=14,435 width=13) (actual time=282,398.225..282,404.725 rows=113,497 loops=2)

  • Sort Key: o."OutcomeType
  • Sort Method: quicksort Memory: 8506kB
8. 853.896 564,623.054 ↓ 7.9 113,497 2

Hash Join (cost=290,478.08..1,551,758.38 rows=14,435 width=13) (actual time=366.371..282,311.527 rows=113,497 loops=2)

  • Hash Cond: (o."OnboardingActionId" = a."OnboardingActionId")
9. 563,330.322 563,330.322 ↓ 1.0 959,231 2

Parallel Seq Scan on "Outcomes" o (cost=0.00..1,258,787.49 rows=949,642 width=17) (actual time=145.961..281,665.161 rows=959,231 loops=2)

  • Filter: (("BankId" >= 1) AND ("CreationDate" >= '2019-03-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone) AND ("OnboardingTemplateId" = 20) AND (("ChannelType")::text = 'Email'::text))
  • Rows Removed by Filter: 2768323
10. 48.710 438.836 ↓ 1.0 115,207 2

Hash (cost=289,083.29..289,083.29 rows=111,583 width=4) (actual time=219.418..219.418 rows=115,207 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 5075kB
11. 365.812 390.126 ↓ 1.0 115,207 2

Bitmap Heap Scan on "OnboardingActions" a (cost=2,113.20..289,083.29 rows=111,583 width=4) (actual time=14.197..195.063 rows=115,207 loops=2)

  • Recheck Cond: ("CampaignId" = 137)
  • Heap Blocks: exact=11011
12. 24.314 24.314 ↓ 1.0 115,207 2

Bitmap Index Scan on ix_onboardingactions_campaignid (cost=0.00..2,085.30 rows=111,583 width=0) (actual time=12.157..12.157 rows=115,207 loops=2)

  • Index Cond: ("CampaignId" = 137)
13. 0.000 1,181.326 ↑ 1.0 1 1

Aggregate (cost=223,330.68..223,330.69 rows=1 width=40) (actual time=1,181.326..1,181.326 rows=1 loops=1)

14. 1.200 1,181.335 ↑ 46.6 5 1

Gather (cost=64,764.95..223,330.10 rows=233 width=4) (actual time=1,116.897..1,181.335 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 0
15. 60.220 1,180.135 ↑ 27.4 5 1

Nested Loop (cost=63,764.95..222,306.80 rows=137 width=4) (actual time=1,115.842..1,180.135 rows=5 loops=1)

16. 47.669 1,118.861 ↑ 155.6 58 1

Parallel Bitmap Heap Scan on "Outcomes" o_1 (cost=63,764.52..156,385.08 rows=9,022 width=4) (actual time=1,073.525..1,118.861 rows=58 loops=1)

  • Recheck Cond: (("CreationDate" >= '2019-03-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone) AND (("OutcomeType")::text = 'open'::text) AND ("OnboardingTemplateId" = 20))
  • Rows Removed by Index Recheck: 164
  • Filter: (("BankId" >= 1) AND (("ChannelType")::text = 'Email'::text))
  • Heap Blocks: lossy=43
17. 20.305 1,071.192 ↓ 0.0 0 1

BitmapAnd (cost=63,764.52..63,764.52 rows=26,266 width=0) (actual time=1,071.192..1,071.192 rows=0 loops=1)

18. 36.818 36.818 ↓ 1.0 6,184,960 1

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..378.56 rows=6,168,247 width=0) (actual time=36.818..36.818 rows=6,184,960 loops=1)

  • Index Cond: (("CreationDate" >= '2019-03-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone))
19. 26.714 26.714 ↓ 1.2 81,703 1

Bitmap Index Scan on ix_outcomes_outcometype (cost=0.00..1,618.44 rows=70,668 width=0) (actual time=26.714..26.714 rows=81,703 loops=1)

  • Index Cond: (("OutcomeType")::text = 'open'::text)
20. 987.355 987.355 ↑ 1.0 3,316,986 1

Bitmap Index Scan on ix_outcomes_onboardingtemplateid (cost=0.00..61,755.51 rows=3,334,277 width=0) (actual time=987.355..987.355 rows=3,316,986 loops=1)

  • Index Cond: ("OnboardingTemplateId" = 20)
21. 1.054 1.054 ↓ 0.0 0 58

Index Scan using "PK_OnboardingActionId" on "OnboardingActions" a_1 (cost=0.43..7.31 rows=1 width=4) (actual time=1.054..1.054 rows=0 loops=58)

  • Index Cond: ("OnboardingActionId" = o_1."OnboardingActionId")
  • Filter: ("CampaignId" = 137)
  • Rows Removed by Filter: 1
22. 0.001 53.426 ↓ 0.0 0 1

GroupAggregate (cost=22,534.58..22,534.90 rows=16 width=114) (actual time=53.425..53.426 rows=0 loops=1)

  • Group Key: a_2."OutcomeType
23. 0.037 53.425 ↓ 0.0 0 1

Sort (cost=22,534.58..22,534.62 rows=16 width=90) (actual time=53.424..53.425 rows=0 loops=1)

  • Sort Key: a_2."OutcomeType
  • Sort Method: quicksort Memory: 25kB
24. 0.000 53.388 ↓ 0.0 0 1

Subquery Scan on a_2 (cost=22,533.78..22,534.26 rows=16 width=90) (actual time=53.388..53.388 rows=0 loops=1)

25. 0.003 53.388 ↓ 0.0 0 1

GroupAggregate (cost=22,533.78..22,534.10 rows=16 width=95) (actual time=53.387..53.388 rows=0 loops=1)

  • Group Key: o_2."OnboardingActionId", o_2."Url
26. 0.000 53.385 ↓ 0.0 0 1

Sort (cost=22,533.78..22,533.82 rows=16 width=9) (actual time=53.385..53.385 rows=0 loops=1)

  • Sort Key: o_2."OnboardingActionId", o_2."Url
  • Sort Method: quicksort Memory: 25kB
27. 23.890 57.762 ↓ 0.0 0 1

Gather (cost=1,497.41..22,533.46 rows=16 width=9) (actual time=53.377..57.762 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
28. 0.002 33.872 ↓ 0.0 0 2

Nested Loop (cost=497.41..21,531.86 rows=9 width=9) (actual time=33.872..33.872 rows=0 loops=2)

29. 0.000 33.870 ↓ 0.0 0 2

Parallel Bitmap Heap Scan on "Outcomes" o_2 (cost=496.98..16,244.96 rows=633 width=9) (actual time=33.870..33.870 rows=0 loops=2)

  • Recheck Cond: ((("OutcomeType")::text = 'click'::text) AND ("CreationDate" >= '2019-03-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone))
  • Rows Removed by Index Recheck: 18
  • Filter: (("BankId" >= 1) AND (("ChannelType")::text = 'Email'::text) AND ("OnboardingTemplateId" = 20))
  • Rows Removed by Filter: 14
  • Heap Blocks: exact=17
30. 0.043 38.321 ↓ 0.0 0 1

BitmapAnd (cost=496.98..496.98 rows=4,112 width=0) (actual time=38.321..38.321 rows=0 loops=1)

31. 0.697 0.697 ↓ 1.1 5,335 1

Bitmap Index Scan on ix_outcomes_outcometype (cost=0.00..117.63 rows=4,959 width=0) (actual time=0.697..0.697 rows=5,335 loops=1)

  • Index Cond: (("OutcomeType")::text = 'click'::text)
32. 37.581 37.581 ↓ 1.0 6,184,960 1

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..378.56 rows=6,168,247 width=0) (actual time=37.581..37.581 rows=6,184,960 loops=1)

  • Index Cond: (("CreationDate" >= '2019-03-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone))
33. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_OnboardingActionId" on "OnboardingActions" a_3 (cost=0.43..8.35 rows=1 width=4) (never executed)

  • Index Cond: ("OnboardingActionId" = o_2."OnboardingActionId")
  • Filter: ("CampaignId" = 137)
Planning time : 2.237 ms