explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 93BQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,016.297 ↑ 4.8 5 1

Append (cost=1,317,697.97..1,451,083.25 rows=24 width=64) (actual time=3,903.427..4,016.297 rows=5 loops=1)

2. 0.015 3,984.019 ↑ 2.0 5 1

Result (cost=1,317,697.97..1,431,770.93 rows=10 width=64) (actual time=3,903.427..3,984.019 rows=5 loops=1)

3. 0.005 3,984.004 ↑ 2.0 5 1

Append (cost=1,317,697.97..1,431,770.80 rows=10 width=40) (actual time=3,903.422..3,984.004 rows=5 loops=1)

4. 41.017 3,942.284 ↑ 2.2 4 1

GroupAggregate (cost=1,317,697.97..1,317,766.26 rows=9 width=17) (actual time=3,903.421..3,942.284 rows=4 loops=1)

  • Group Key: o."OutcomeType
5. 170.976 3,901.267 ↓ 25.0 226,993 1

Sort (cost=1,317,697.97..1,317,720.70 rows=9,094 width=13) (actual time=3,858.458..3,901.267 rows=226,993 loops=1)

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

Hash Join (cost=313,074.09..1,317,100.00 rows=9,094 width=13) (actual time=883.596..3,730.291 rows=226,993 loops=1)

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

Bitmap Heap Scan on "Outcomes" o (cost=22,596.01..1,025,051.44 rows=598,279 width=17) (actual time=685.939..3,116.250 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. 639.414 639.414 ↓ 2.3 1,362,538 1

Bitmap Index Scan on ix_outcomes_outbound_communication_status (cost=0.00..22,446.44 rows=598,279 width=0) (actual time=639.413..639.414 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.638 196.897 ↓ 1.0 115,207 1

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

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

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

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

Aggregate (cost=114,004.43..114,004.44 rows=1 width=40) (actual time=41.715..41.715 rows=1 loops=1)

13. 30.599 46.820 ↑ 18.6 5 1

Gather (cost=2,970.24..114,004.20 rows=93 width=4) (actual time=33.001..46.820 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
14. 0.285 16.221 ↑ 18.3 3 2

Nested Loop (cost=1,970.24..112,994.90 rows=55 width=4) (actual time=15.952..16.221 rows=3 loops=2)

15. 0.000 15.919 ↑ 200.1 18 2

Parallel Bitmap Heap Scan on "Outcomes" o_1 (cost=1,969.80..84,505.59 rows=3,601 width=4) (actual time=15.787..15.919 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.796 31.394 ↓ 0.0 0 1

BitmapAnd (cost=1,969.80..1,969.80 rows=23,202 width=0) (actual time=31.394..31.394 rows=0 loops=1)

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

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..227.03 rows=2,267,263 width=0) (actual time=16.698..16.698 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.900 8.900 ↓ 1.1 81,703 1

Bitmap Index Scan on ix_outcomes_outcometype (cost=0.00..1,739.46 rows=75,604 width=0) (actual time=8.900..8.900 rows=81,703 loops=1)

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

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

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

GroupAggregate (cost=19,311.90..19,312.18 rows=14 width=114) (actual time=32.276..32.276 rows=0 loops=1)

  • Group Key: a_2."OutcomeType
21. 0.014 32.273 ↓ 0.0 0 1

Sort (cost=19,311.90..19,311.93 rows=14 width=90) (actual time=32.273..32.273 rows=0 loops=1)

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

Subquery Scan on a_2 (cost=19,311.21..19,311.63 rows=14 width=90) (actual time=32.258..32.259 rows=0 loops=1)

23. 0.002 32.257 ↓ 0.0 0 1

GroupAggregate (cost=19,311.21..19,311.49 rows=14 width=95) (actual time=32.257..32.257 rows=0 loops=1)

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

Sort (cost=19,311.21..19,311.25 rows=14 width=9) (actual time=32.255..32.255 rows=0 loops=1)

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

Gather (cost=1,489.25..19,310.95 rows=14 width=9) (actual time=32.247..32.358 rows=0 loops=1)

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

Nested Loop (cost=489.25..18,309.55 rows=8 width=9) (actual time=31.421..31.421 rows=0 loops=1)

27. 0.075 31.420 ↓ 0.0 0 1

Parallel Bitmap Heap Scan on "Outcomes" o_2 (cost=488.82..13,809.19 rows=538 width=9) (actual time=31.420..31.420 rows=0 loops=1)

  • 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 = 'click'::text))
  • Rows Removed by Index Recheck: 19
  • Filter: (("BankId" >= 1) AND (("ChannelType")::text = 'Email'::text) AND ("OnboardingTemplateId" = 20))
  • Rows Removed by Filter: 4
  • Heap Blocks: lossy=4
28. 15.123 31.345 ↓ 0.0 0 1

BitmapAnd (cost=488.82..488.82 rows=3,464 width=0) (actual time=31.345..31.345 rows=0 loops=1)

29. 15.613 15.613 ↓ 1.1 2,448,640 1

Bitmap Index Scan on ix_outcomes_creationdate (cost=0.00..227.03 rows=2,267,263 width=0) (actual time=15.613..15.613 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))
30. 0.609 0.609 ↑ 2.1 5,335 1

Bitmap Index Scan on ix_outcomes_outcometype (cost=0.00..261.09 rows=11,287 width=0) (actual time=0.609..0.609 rows=5,335 loops=1)

  • Index Cond: (("OutcomeType")::text = 'click'::text)
31. 0.000 0.000 ↓ 0.0 0

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

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