explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BTCF

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,575.576 ↑ 3.2 5 1

Append (cost=1,413,606.25..1,536,207.52 rows=16 width=64) (actual time=4,482.842..4,575.576 rows=5 loops=1)

2. 0.014 4,558.612 ↑ 1.8 5 1

Result (cost=1,413,606.25..1,525,370.63 rows=9 width=64) (actual time=4,482.842..4,558.612 rows=5 loops=1)

3. 0.003 4,558.598 ↑ 1.8 5 1

Append (cost=1,413,606.25..1,525,370.52 rows=9 width=40) (actual time=4,482.837..4,558.598 rows=5 loops=1)

4. 41.415 4,521.777 ↑ 2.0 4 1

GroupAggregate (cost=1,413,606.25..1,413,673.74 rows=8 width=17) (actual time=4,482.836..4,521.777 rows=4 loops=1)

  • Group Key: o."OutcomeType
5. 209.237 4,480.362 ↓ 25.3 226,993 1

Sort (cost=1,413,606.25..1,413,628.72 rows=8,988 width=13) (actual time=4,439.115..4,480.362 rows=226,993 loops=1)

  • Sort Key: o."OutcomeType
  • Sort Method: external merge Disk: 5776kB
6. 436.835 4,271.125 ↓ 25.3 226,993 1

Hash Join (cost=414,127.42..1,413,016.02 rows=8,988 width=13) (actual time=1,277.516..4,271.125 rows=226,993 loops=1)

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

Bitmap Heap Scan on "Outcomes" o (cost=123,649.34..1,120,985.72 rows=591,320 width=17) (actual time=1,119.091..3,676.439 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. 1,078.360 1,078.360 ↓ 2.3 1,362,538 1

Bitmap Index Scan on ix_outcomes_outbound_communication_status (cost=0.00..123,501.51 rows=591,320 width=0) (actual time=1,078.360..1,078.360 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. 28.830 157.851 ↓ 1.0 115,207 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5075kB
10. 120.340 129.021 ↓ 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=10.372..129.021 rows=115,207 loops=1)

  • Recheck Cond: ("CampaignId" = 137)
  • Heap Blocks: exact=11011
11. 8.681 8.681 ↓ 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=8.681..8.681 rows=115,207 loops=1)

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

Aggregate (cost=111,696.67..111,696.68 rows=1 width=40) (actual time=36.818..36.818 rows=1 loops=1)

13. 19.809 36.904 ↑ 18.0 5 1

Gather (cost=2,931.98..111,696.45 rows=90 width=4) (actual time=34.863..36.904 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
14. 0.356 17.095 ↑ 17.7 3 2

Nested Loop (cost=1,931.98..110,687.45 rows=53 width=4) (actual time=16.757..17.095 rows=3 loops=2)

15. 0.000 16.718 ↑ 192.6 18 2

Parallel Bitmap Heap Scan on "Outcomes" o_1 (cost=1,931.54..83,204.95 rows=3,466 width=4) (actual time=16.523..16.718 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. 6.510 32.854 ↓ 0.0 0 1

BitmapAnd (cost=1,931.54..1,931.54 rows=22,822 width=0) (actual time=32.854..32.854 rows=0 loops=1)

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

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..227.20 rows=2,289,856 width=0) (actual time=17.530..17.530 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.814 8.814 ↓ 1.1 81,703 1

Bitmap Index Scan on ix_outcomes_outcometype (cost=0.00..1,701.15 rows=74,229 width=0) (actual time=8.814..8.814 rows=81,703 loops=1)

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

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

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

GroupAggregate (cost=10,836.68..10,836.82 rows=7 width=114) (actual time=16.962..16.962 rows=0 loops=1)

  • Group Key: a_2."OutcomeType
21. 0.010 16.961 ↓ 0.0 0 1

Sort (cost=10,836.68..10,836.70 rows=7 width=90) (actual time=16.961..16.961 rows=0 loops=1)

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

Subquery Scan on a_2 (cost=10,836.38..10,836.59 rows=7 width=90) (actual time=16.950..16.951 rows=0 loops=1)

23. 0.002 16.950 ↓ 0.0 0 1

GroupAggregate (cost=10,836.38..10,836.52 rows=7 width=95) (actual time=16.950..16.950 rows=0 loops=1)

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

Sort (cost=10,836.38..10,836.39 rows=7 width=9) (actual time=16.948..16.948 rows=0 loops=1)

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

Gather (cost=1,365.23..10,836.28 rows=7 width=9) (actual time=16.943..17.027 rows=0 loops=1)

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

Nested Loop (cost=365.23..9,835.58 rows=4 width=9) (actual time=16.151..16.151 rows=0 loops=1)

27. 0.029 16.150 ↓ 0.0 0 1

Parallel Bitmap Heap Scan on "Outcomes" o_2 (cost=364.80..7,497.54 rows=278 width=9) (actual time=16.150..16.150 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.029 16.121 ↓ 0.0 0 1

BitmapAnd (cost=364.80..364.80 rows=1,832 width=0) (actual time=16.121..16.121 rows=0 loops=1)

29. 0.641 0.641 ↑ 1.1 5,335 1

Bitmap Index Scan on ix_outcomes_outcometype (cost=0.00..137.12 rows=5,958 width=0) (actual time=0.641..0.641 rows=5,335 loops=1)

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

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..227.20 rows=2,289,856 width=0) (actual time=15.451..15.451 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.41 rows=1 width=4) (never executed)

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