explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pz7G

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 327.541 ↑ 1.0 10 1

Limit (cost=93,257.68..93,257.71 rows=10 width=96) (actual time=327.540..327.541 rows=10 loops=1)

  • Buffers: shared hit=70317
2.          

CTE stats

3. 0.068 327.368 ↓ 3.1 47 1

HashAggregate (cost=93,256.19..93,256.34 rows=15 width=72) (actual time=327.357..327.368 rows=47 loops=1)

  • Group Key: d.ad_name, d.ad_identity_uuid, i.view_count, (NULL::uuid)
  • Buffers: shared hit=70317
4. 0.007 327.300 ↓ 4.1 62 1

Append (cost=0.98..93,256.04 rows=15 width=72) (actual time=0.047..327.300 rows=62 loops=1)

  • Buffers: shared hit=70317
5. 0.039 0.411 ↓ 7.6 61 1

Nested Loop (cost=0.98..114.27 rows=8 width=69) (actual time=0.047..0.411 rows=61 loops=1)

  • Buffers: shared hit=384
6. 0.144 0.144 ↓ 2.8 76 1

Index Scan using "t_interaction_form_views_daily$pk" on t_interaction_form_views_daily i (cost=0.56..42.92 rows=27 width=24) (actual time=0.027..0.144 rows=76 loops=1)

  • Index Cond: ((organization_uuid = '56678ff4-07cc-4a86-b336-eafcc06ab0a1'::uuid) AND (event_date_key >= 20200218) AND (event_date_key <= 20200318))
  • Buffers: shared hit=80
7. 0.228 0.228 ↑ 1.0 1 76

Index Scan using "t_activity_definition_dim$idx1" on t_activity_definition_dim d (cost=0.42..2.64 rows=1 width=61) (actual time=0.003..0.003 rows=1 loops=76)

  • Index Cond: (adi_identity_uuid = i.activity_definition_instance_id)
  • Filter: (ad_activity_type = 'Fundraise'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=304
8. 0.004 326.882 ↑ 7.0 1 1

Nested Loop (cost=14,813.50..93,141.62 rows=7 width=70) (actual time=319.912..326.882 rows=1 loops=1)

  • Buffers: shared hit=69933
9. 0.045 326.857 ↑ 15.0 1 1

Nested Loop (cost=14,812.94..93,118.43 rows=15 width=62) (actual time=319.888..326.857 rows=1 loops=1)

  • Buffers: shared hit=69928
10. 8.344 326.462 ↓ 11.7 35 1

Hash Join (cost=14,812.37..93,061.59 rows=3 width=73) (actual time=46.591..326.462 rows=35 loops=1)

  • Hash Cond: (tadi.activity_definition_uuid = tad.identity_uuid)
  • Buffers: shared hit=69787
11. 272.478 272.478 ↑ 1.0 68,530 1

Seq Scan on t_activity_definition_instance tadi (cost=0.00..77,901.26 rows=69,585 width=32) (actual time=0.007..272.478 rows=68,530 loops=1)

  • Filter: (effective_end = '2100-01-01 00:00:00-05'::timestamp with time zone)
  • Rows Removed by Filter: 1536574
  • Buffers: shared hit=57838
12. 0.024 45.640 ↓ 17.5 35 1

Hash (cost=14,812.35..14,812.35 rows=2 width=57) (actual time=45.640..45.640 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=11949
13. 39.044 45.616 ↓ 17.5 35 1

Bitmap Heap Scan on t_activity_definition tad (cost=1,114.12..14,812.35 rows=2 width=57) (actual time=9.049..45.616 rows=35 loops=1)

  • Recheck Cond: (activity_type = 'Fundraise'::text)
  • Filter: ((effective_end = '2100-01-01 00:00:00-05'::timestamp with time zone) AND (organization_uuid = '56678ff4-07cc-4a86-b336-eafcc06ab0a1'::uuid))
  • Rows Removed by Filter: 90363
  • Heap Blocks: exact=11571
  • Buffers: shared hit=11949
14. 6.572 6.572 ↑ 1.0 90,404 1

Bitmap Index Scan on t_activity_definition_type (cost=0.00..1,114.12 rows=90,413 width=0) (actual time=6.572..6.572 rows=90,404 loops=1)

  • Index Cond: (activity_type = 'Fundraise'::text)
  • Buffers: shared hit=378
15. 0.350 0.350 ↓ 0.0 0 35

Index Scan using "t_activity$idx3" on t_activity ta (cost=0.56..18.68 rows=27 width=42) (actual time=0.010..0.010 rows=0 loops=35)

  • Index Cond: ((activity_definition_instance_id = tadi.identity_uuid) AND (activity_date >= '2020-02-18 10:06:12-05'::timestamp with time zone) AND (activity_date <= '2020-03-18 11:06:12.999-04'::timestamp with time zone))
  • Filter: (activity_type = 'Fundraise'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=141
16. 0.021 0.021 ↑ 1.0 1 1

Index Scan using "t_donation_intent$idx5" on t_donation_intent tdi (cost=0.56..1.54 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=1)

  • Index Cond: (activity_uuid = ta.uuid)
  • Buffers: shared hit=5
17. 0.084 327.539 ↑ 1.5 10 1

Sort (cost=1.34..1.38 rows=15 width=96) (actual time=327.539..327.539 rows=10 loops=1)

  • Sort Key: (CASE WHEN (sum(stats.view) = '0'::numeric) THEN '0'::double precision ELSE ('100'::double precision - (((count(stats.donation))::double precision / (sum(stats.view))::double precision) * '100'::double precision)) END) DESC, stats.form_name
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=70317
18. 0.065 327.455 ↓ 1.7 25 1

HashAggregate (cost=0.56..1.05 rows=15 width=96) (actual time=327.440..327.455 rows=25 loops=1)

  • Group Key: stats.form_name, stats.identity_uuid
  • Buffers: shared hit=70317
19. 327.390 327.390 ↓ 3.1 47 1

CTE Scan on stats (cost=0.00..0.30 rows=15 width=72) (actual time=327.360..327.390 rows=47 loops=1)

  • Buffers: shared hit=70317
Planning time : 33.209 ms
Execution time : 327.758 ms