explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z3ju

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=2,185,354,076.96..2,185,354,246.30 rows=1 width=16) (actual rows= loops=)

  • Hash Cond: (p.person_uuid = ruleset0.person_uuid)
2.          

CTE rule1

3. 0.000 0.000 ↓ 0.0

Index Scan using "t_person_insight$idx1" on t_person_insight (cost=0.56..6,063.92 rows=2,970 width=16) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: ((date_trunc('day'::text, supporter_since) < '2019-05-20 00:00:00'::timestamp without time zone) OR (date_trunc('day'::text, supporter_since) > '2020-05-20 00:00:00'::timestamp without time zone))
4.          

CTE rule2

5. 0.000 0.000 ↓ 0.0

Index Scan using "t_person_insight$idx1" on t_person_insight t_person_insight_1 (cost=0.56..6,063.92 rows=2,970 width=16) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: ((date_trunc('day'::text, subscription_change_date) < '2019-05-20 00:00:00'::timestamp without time zone) OR (date_trunc('day'::text, subscription_change_date) > '2020-05-20 00:00:00'::timestamp without time zone))
6.          

CTE rule4

7. 0.000 0.000 ↓ 0.0

Index Scan using "t_activity$idx1" on t_activity (cost=0.56..2,117,568.32 rows=6,017,167 width=16) (actual rows= loops=)

  • Index Cond: ((organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid) AND (activity_type = ANY ('{Subscribe,Fundraise,Petition,TargetedLTR,RegulationComment,TicketedEvent,P2PEvent}'::text[])))
  • Filter: ((date_trunc('day'::text, activity_date) < '2019-05-20 00:00:00'::timestamp without time zone) OR (date_trunc('day'::text, activity_date) > '2020-05-20 00:00:00'::timestamp without time zone))
8.          

CTE rule5

9. 0.000 0.000 ↓ 0.0

Gather (cost=1,089.64..2,181,994,266.52 rows=2,413 width=16) (actual rows= loops=)

  • Workers Planned: 2
10. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on t_person_dim_backend p_1 (cost=89.64..2,181,993,025.22 rows=1,005 width=16) (actual rows= loops=)

  • Recheck Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: (NOT (SubPlan 4))
11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on "t_person_dim$idx1" (cost=0.00..88.47 rows=4,827 width=0) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
12.          

SubPlan (for Parallel Bitmap Heap Scan)

13. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..2,142,841.30 rows=10,776,746 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using "t_activity$idx1" on t_activity t_activity_1 (cost=0.56..2,036,336.57 rows=10,776,746 width=16) (actual rows= loops=)

  • Index Cond: ((organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid) AND (activity_type = ANY ('{Subscribe,Fundraise,Petition,TargetedLTR,RegulationComment,TicketedEvent,P2PEvent}'::text[])))
  • Filter: (date_trunc('day'::text, activity_date) IS NOT NULL)
15.          

CTE ruleset3

16. 0.000 0.000 ↓ 0.0

Unique (cost=978,940.47..1,009,038.37 rows=6,019,580 width=16) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=978,940.47..993,989.42 rows=6,019,580 width=16) (actual rows= loops=)

  • Sort Key: rule4.person_uuid
18. 0.000 0.000 ↓ 0.0

Append (cost=0.00..180,587.40 rows=6,019,580 width=16) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

CTE Scan on rule4 (cost=0.00..120,343.34 rows=6,017,167 width=16) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

CTE Scan on rule5 (cost=0.00..48.26 rows=2,413 width=16) (actual rows= loops=)

21.          

CTE rule7

22. 0.000 0.000 ↓ 0.0

Index Scan using "t_person_insight$idx1" on t_person_insight t_person_insight_2 (cost=0.56..6,063.92 rows=2,970 width=16) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: ((date_trunc('day'::text, last_blast_open_date) < '2019-05-20 00:00:00'::timestamp without time zone) OR (date_trunc('day'::text, last_blast_open_date) > '2020-05-20 00:00:00'::timestamp without time zone))
23.          

CTE rule8

24. 0.000 0.000 ↓ 0.0

Index Scan using "t_person_insight$idx1" on t_person_insight t_person_insight_3 (cost=0.56..6,010.45 rows=3,610 width=16) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: (last_blast_open_date IS NULL)
25.          

CTE ruleset6

26. 0.000 0.000 ↓ 0.0

HashAggregate (cost=213.85..279.65 rows=6,580 width=16) (actual rows= loops=)

  • Group Key: rule7.person_uuid
27. 0.000 0.000 ↓ 0.0

Append (cost=0.00..197.40 rows=6,580 width=16) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

CTE Scan on rule7 (cost=0.00..59.40 rows=2,970 width=16) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

CTE Scan on rule8 (cost=0.00..72.20 rows=3,610 width=16) (actual rows= loops=)

30.          

CTE rule10

31. 0.000 0.000 ↓ 0.0

Index Scan using "t_person_insight$idx1" on t_person_insight t_person_insight_4 (cost=0.56..6,063.92 rows=2,970 width=16) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: ((date_trunc('day'::text, last_blast_click_date) < '2019-05-20 00:00:00'::timestamp without time zone) OR (date_trunc('day'::text, last_blast_click_date) > '2020-05-20 00:00:00'::timestamp without time zone))
32.          

CTE rule11

33. 0.000 0.000 ↓ 0.0

Index Scan using "t_person_insight$idx1" on t_person_insight t_person_insight_5 (cost=0.56..6,010.45 rows=4,893 width=16) (actual rows= loops=)

  • Index Cond: (organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid)
  • Filter: (last_blast_click_date IS NULL)
34.          

CTE ruleset9

35. 0.000 0.000 ↓ 0.0

HashAggregate (cost=255.55..334.18 rows=7,863 width=16) (actual rows= loops=)

  • Group Key: rule10.person_uuid
36. 0.000 0.000 ↓ 0.0

Append (cost=0.00..235.89 rows=7,863 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

CTE Scan on rule10 (cost=0.00..59.40 rows=2,970 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

CTE Scan on rule11 (cost=0.00..97.86 rows=4,893 width=16) (actual rows= loops=)

39.          

CTE ruleset0

40. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=0.00..196,306.30 rows=200 width=20) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Append (cost=0.00..196,286.14 rows=8,063 width=20) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Result (cost=0.00..196,050.25 rows=200 width=20) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=0.00..196,048.25 rows=200 width=20) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Append (cost=0.00..196,031.30 rows=6,780 width=20) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Result (cost=0.00..195,833.90 rows=200 width=20) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=0.00..195,831.90 rows=200 width=20) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Append (cost=0.00..180,782.45 rows=6,019,780 width=20) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Result (cost=0.00..195.05 rows=200 width=20) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=0.00..193.05 rows=200 width=20) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Append (cost=0.00..178.20 rows=5,940 width=20) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.00..89.10 rows=2,970 width=20) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

CTE Scan on rule1 (cost=0.00..59.40 rows=2,970 width=16) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..89.10 rows=2,970 width=20) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

CTE Scan on rule2 (cost=0.00..59.40 rows=2,970 width=16) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=0.00..180,587.40 rows=6,019,580 width=20) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

CTE Scan on ruleset3 (cost=0.00..120,391.60 rows=6,019,580 width=16) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=0.00..197.40 rows=6,580 width=20) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

CTE Scan on ruleset6 (cost=0.00..131.60 rows=6,580 width=16) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=0.00..235.89 rows=7,863 width=20) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

CTE Scan on ruleset9 (cost=0.00..157.26 rows=7,863 width=16) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Index Only Scan using "t_person_dim$idx2" on t_person_dim_backend p (cost=0.56..158.69 rows=4,275 width=32) (actual rows= loops=)

  • Index Cond: ((organization_uuid = '064cdd3b-f4dc-4c36-94cb-4dae6ca07ca8'::uuid) AND (effective_end > '2020-05-20 03:40:53-04'::timestamp with time zone))
62. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=16) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

CTE Scan on ruleset0 (cost=0.00..4.00 rows=200 width=16) (actual rows= loops=)