explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nlKj

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,168.702 ↑ 3.0 5 1

Append (cost=1,419,646.23..1,535,926.79 rows=15 width=64) (actual time=4,081.174..4,168.702 rows=5 loops=1)

2. 0.013 4,149.734 ↑ 1.8 5 1

Result (cost=1,419,646.23..1,526,652.39 rows=9 width=64) (actual time=4,081.173..4,149.734 rows=5 loops=1)

3. 0.003 4,149.721 ↑ 1.8 5 1

Append (cost=1,419,646.23..1,526,652.27 rows=9 width=40) (actual time=4,081.169..4,149.721 rows=5 loops=1)

4. 33.866 4,112.467 ↑ 2.0 4 1

GroupAggregate (cost=1,419,646.23..1,419,714.47 rows=8 width=17) (actual time=4,081.168..4,112.467 rows=4 loops=1)

  • Group Key: o."OutcomeType
5. 167.978 4,078.601 ↓ 25.0 226,993 1

Sort (cost=1,419,646.23..1,419,668.95 rows=9,087 width=13) (actual time=4,043.483..4,078.601 rows=226,993 loops=1)

  • Sort Key: o."OutcomeType
  • Sort Method: external merge Disk: 5776kB
6. 405.706 3,910.623 ↓ 25.0 226,993 1

Hash Join (cost=415,361.31..1,419,048.78 rows=9,087 width=13) (actual time=1,131.238..3,910.623 rows=226,993 loops=1)

  • Hash Cond: (o."OnboardingActionId" = a."OnboardingActionId")
7. 2,424.507 3,357.690 ↓ 2.3 1,362,538 1

Bitmap Heap Scan on "Outcomes" o (cost=124,883.23..1,127,001.43 rows=597,817 width=17) (actual time=983.420..3,357.690 rows=1,362,538 loops=1)

  • Recheck Cond: (("OnboardingTemplateId" = 20) AND (("ChannelType")::text = 'Email'::text) AND ("BankId" >= 1) AND ("CreationDate" >= '2019-08-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: 207
  • Heap Blocks: exact=119804 lossy=107357
8. 933.183 933.183 ↓ 2.3 1,362,538 1

Bitmap Index Scan on ix_outcomes_outbound_communication_status (cost=0.00..124,733.77 rows=597,817 width=0) (actual time=933.183..933.183 rows=1,362,538 loops=1)

  • Index Cond: (("OnboardingTemplateId" = 20) AND (("ChannelType")::text = 'Email'::text) AND ("BankId" >= 1) AND ("CreationDate" >= '2019-08-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone))
9. 27.935 147.227 ↓ 1.0 115,207 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5075kB
10. 109.187 119.292 ↓ 1.0 115,207 1

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

  • Recheck Cond: ("CampaignId" = 137)
  • Heap Blocks: exact=11011
11. 10.105 10.105 ↓ 1.0 115,207 1

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

  • Index Cond: ("CampaignId" = 137)
12. 0.000 37.251 ↑ 1.0 1 1

Aggregate (cost=106,937.71..106,937.72 rows=1 width=40) (actual time=37.251..37.251 rows=1 loops=1)

13. 21.086 37.358 ↑ 17.2 5 1

Gather (cost=2,849.04..106,937.49 rows=86 width=4) (actual time=33.051..37.358 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
14. 0.315 16.272 ↑ 17.0 3 2

Nested Loop (cost=1,849.04..105,928.89 rows=51 width=4) (actual time=15.988..16.272 rows=3 loops=2)

15. 0.000 15.939 ↑ 185.6 18 2

Parallel Bitmap Heap Scan on "Outcomes" o_1 (cost=1,848.61..79,381.06 rows=3,341 width=4) (actual time=15.799..15.939 rows=18 loops=2)

  • Recheck Cond: (("CreationDate" >= '2019-08-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))
  • Rows Removed by Index Recheck: 41
  • Filter: (("BankId" >= 1) AND (("ChannelType")::text = 'Email'::text) AND ("OnboardingTemplateId" = 20))
  • Rows Removed by Filter: 16
  • Heap Blocks: lossy=30
16. 5.706 31.413 ↓ 0.0 0 1

BitmapAnd (cost=1,848.61..1,848.61 rows=21,698 width=0) (actual time=31.413..31.413 rows=0 loops=1)

17. 16.794 16.794 ↓ 1.1 2,448,640 1

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..227.08 rows=2,283,964 width=0) (actual time=16.794..16.794 rows=2,448,640 loops=1)

  • Index Cond: (("CreationDate" >= '2019-08-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone))
18. 8.913 8.913 ↓ 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=8.913..8.913 rows=81,703 loops=1)

  • Index Cond: (("OutcomeType")::text = 'open'::text)
19. 0.018 0.018 ↓ 0.0 0 35

Index Scan using "PK_OnboardingActionId" on "OnboardingActions" a_1 (cost=0.43..7.95 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=35)

  • Index Cond: ("OnboardingActionId" = o_1."OnboardingActionId")
  • Filter: ("CampaignId" = 137)
  • Rows Removed by Filter: 1
20. 0.002 18.965 ↓ 0.0 0 1

GroupAggregate (cost=9,274.22..9,274.34 rows=6 width=114) (actual time=18.965..18.965 rows=0 loops=1)

  • Group Key: a_2."OutcomeType
21. 0.011 18.963 ↓ 0.0 0 1

Sort (cost=9,274.22..9,274.24 rows=6 width=90) (actual time=18.963..18.963 rows=0 loops=1)

  • Sort Key: a_2."OutcomeType
  • Sort Method: quicksort Memory: 25kB
22. 0.001 18.952 ↓ 0.0 0 1

Subquery Scan on a_2 (cost=9,273.96..9,274.14 rows=6 width=90) (actual time=18.952..18.952 rows=0 loops=1)

23. 0.002 18.951 ↓ 0.0 0 1

GroupAggregate (cost=9,273.96..9,274.08 rows=6 width=95) (actual time=18.951..18.951 rows=0 loops=1)

  • Group Key: o_2."OnboardingActionId", o_2."Url
24. 0.000 18.949 ↓ 0.0 0 1

Sort (cost=9,273.96..9,273.98 rows=6 width=9) (actual time=18.949..18.949 rows=0 loops=1)

  • Sort Key: o_2."OnboardingActionId", o_2."Url
  • Sort Method: quicksort Memory: 25kB
25. 0.906 19.008 ↓ 0.0 0 1

Gather (cost=1,345.58..9,273.89 rows=6 width=9) (actual time=18.942..19.008 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 0
26. 0.001 18.102 ↓ 0.0 0 1

Nested Loop (cost=345.58..8,273.29 rows=4 width=9) (actual time=18.102..18.102 rows=0 loops=1)

27. 0.031 18.101 ↓ 0.0 0 1

Parallel Bitmap Heap Scan on "Outcomes" o_2 (cost=345.15..6,294.02 rows=235 width=9) (actual time=18.101..18.101 rows=0 loops=1)

  • Recheck Cond: ((("OutcomeType")::text = 'click'::text) AND ("CreationDate" >= '2019-08-01 00:00:00'::timestamp without time zone) AND ("CreationDate" <= '2019-08-19 23:59:59'::timestamp without time zone))
  • Filter: (("BankId" >= 1) AND (("ChannelType")::text = 'Email'::text) AND ("OnboardingTemplateId" = 20))
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=4
28. 0.030 18.070 ↓ 0.0 0 1

BitmapAnd (cost=345.15..345.15 rows=1,523 width=0) (actual time=18.069..18.070 rows=0 loops=1)

29. 0.716 0.716 ↓ 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.716..0.716 rows=5,335 loops=1)

  • Index Cond: (("OutcomeType")::text = 'click'::text)
30. 17.324 17.324 ↓ 1.1 2,448,640 1

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..227.08 rows=2,283,964 width=0) (actual time=17.324..17.324 rows=2,448,640 loops=1)

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

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

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