explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wxgh

Settings
# exclusive inclusive rows x rows loops node
1. 11.098 43,524.263 ↑ 1.0 1 1

Aggregate (cost=106,369.87..106,369.89 rows=1 width=112) (actual time=43,524.262..43,524.263 rows=1 loops=1)

2. 15,585.640 43,513.165 ↓ 5,275.0 5,275 1

Nested Loop Left Join (cost=99,048.10..106,369.84 rows=1 width=100) (actual time=529.477..43,513.165 rows=5,275 loops=1)

  • Join Filter: ((lead.lead_id)::integer = t.id)
  • Rows Removed by Join Filter: 98,578,456
3. 7.836 671.600 ↓ 5,275.0 5,275 1

Merge Left Join (cost=98,543.16..100,086.46 rows=1 width=80) (actual time=517.861..671.600 rows=5,275 loops=1)

  • Merge Cond: (t.id = b.user_id)
4. 0.000 393.115 ↓ 5,275.0 5,275 1

Nested Loop (cost=43,254.61..44,740.73 rows=1 width=16) (actual time=255.328..393.115 rows=5,275 loops=1)

5. 136.698 292.294 ↓ 8,583.5 34,334 1

GroupAggregate (cost=43,254.32..44,707.44 rows=4 width=96) (actual time=144.596..292.294 rows=34,334 loops=1)

  • Group Key: t_1.user_id
  • Filter: (string_agg(DISTINCT (t_1.network)::text, ','::text) ~~* '%instagram%'::text)
  • Rows Removed by Filter: 24,269
6. 28.558 155.596 ↑ 1.1 61,519 1

Sort (cost=43,254.32..43,423.70 rows=67,749 width=25) (actual time=144.509..155.596 rows=61,519 loops=1)

  • Sort Key: t_1.user_id
  • Sort Method: external merge Disk: 1,880kB
7. 118.952 127.038 ↑ 1.1 61,519 1

Bitmap Heap Scan on blogger t_1 (cost=1,301.48..37,818.17 rows=67,749 width=25) (actual time=11.999..127.038 rows=61,519 loops=1)

  • Recheck Cond: (user_id IS NOT NULL)
  • Heap Blocks: exact=30,119
8. 8.086 8.086 ↓ 1.1 71,867 1

Bitmap Index Scan on blogger_user_id_idx (cost=0.00..1,284.54 rows=67,749 width=0) (actual time=8.086..8.086 rows=71,867 loops=1)

  • Index Cond: (user_id IS NOT NULL)
9. 103.002 103.002 ↓ 0.0 0 34,334

Index Scan using user_pkey on ""user"" t (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=34,334)

  • Index Cond: (id = t_1.user_id)
  • Filter: (((role)::text = ANY ('{blogger,scout}'::text[])) AND (created_at >= '2020-06-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
10. 17.626 270.649 ↓ 101.9 20,380 1

Finalize GroupAggregate (cost=55,288.55..55,343.22 rows=200 width=68) (actual time=249.847..270.649 rows=20,380 loops=1)

  • Group Key: b.user_id
11. 143.724 253.023 ↓ 51.0 20,380 1

Gather Merge (cost=55,288.55..55,335.22 rows=400 width=68) (actual time=249.839..253.023 rows=20,380 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1.639 109.299 ↓ 34.0 6,793 3 / 3

Sort (cost=54,288.52..54,289.02 rows=200 width=68) (actual time=108.955..109.299 rows=6,793 loops=3)

  • Sort Key: b.user_id
  • Sort Method: quicksort Memory: 187kB
  • Worker 0: Sort Method: quicksort Memory: 2,222kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
13. 3.727 107.660 ↓ 34.0 6,793 3 / 3

Partial HashAggregate (cost=54,277.88..54,280.88 rows=200 width=68) (actual time=105.490..107.660 rows=6,793 loops=3)

  • Group Key: b.user_id
14. 0.245 103.933 ↑ 1.4 6,793 3 / 3

Parallel Append (cost=16,016.30..54,207.30 rows=9,410 width=20) (actual time=102.935..103.933 rows=6,793 loops=3)

15. 6.852 76.729 ↑ 1.1 18,611 1 / 3

HashAggregate (cost=53,736.95..53,948.60 rows=21,165 width=20) (actual time=228.099..230.188 rows=18,611 loops=1)

  • Group Key: b.user_id
16. 4.212 69.877 ↓ 5.5 116,676 1 / 3

Hash Left Join (cost=38,746.32..53,525.30 rows=21,165 width=8) (actual time=141.157..209.632 rows=116,676 loops=1)

  • Hash Cond: (p.project_id = pr.id)
17. 6.402 65.576 ↓ 5.5 116,676 1 / 3

Hash Join (cost=38,665.04..53,387.82 rows=21,165 width=8) (actual time=140.875..196.727 rows=116,676 loops=1)

  • Hash Cond: (p.blogger_id = b.id)
18. 12.328 12.328 ↑ 1.0 122,943 1 / 3

Seq Scan on post p (cost=0.00..14,395.27 rows=124,766 width=28) (actual time=0.012..36.985 rows=122,943 loops=1)

  • Filter: ((status)::text = 'published'::text)
  • Rows Removed by Filter: 13,127
19. 3.504 46.846 ↑ 1.1 61,519 1 / 3

Hash (cost=37,818.17..37,818.17 rows=67,749 width=8) (actual time=140.537..140.537 rows=61,519 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 3,428kB
20. 40.710 43.341 ↑ 1.1 61,519 1 / 3

Bitmap Heap Scan on blogger b (cost=1,301.48..37,818.17 rows=67,749 width=8) (actual time=12.633..130.024 rows=61,519 loops=1)

  • Recheck Cond: (user_id IS NOT NULL)
21. 2.631 2.631 ↓ 1.1 71,867 1 / 3

Bitmap Index Scan on blogger_user_id_idx (cost=0.00..1,284.54 rows=67,749 width=0) (actual time=7.893..7.893 rows=71,867 loops=1)

  • Index Cond: (user_id IS NOT NULL)
22. 0.012 0.090 ↑ 1.0 368 1 / 3

Hash (cost=76.68..76.68 rows=368 width=8) (actual time=0.270..0.270 rows=368 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
23. 0.078 0.078 ↑ 1.0 368 1 / 3

Seq Scan on project pr (cost=0.00..76.68 rows=368 width=8) (actual time=0.017..0.234 rows=368 loops=1)

24. 5.899 26.959 ↓ 1.2 1,769 1 / 3

HashAggregate (cost=16,016.30..16,030.48 rows=1,418 width=20) (actual time=80.705..80.877 rows=1,769 loops=1)

  • Group Key: p_1.scout_id
25. 4.978 21.060 ↑ 1.0 120,852 1 / 3

Hash Left Join (cost=81.28..14,799.62 rows=121,668 width=8) (actual time=0.269..63.179 rows=120,852 loops=1)

  • Hash Cond: (p_1.project_id = pr_1.id)
26. 15.997 15.997 ↑ 1.0 120,852 1 / 3

Seq Scan on post p_1 (cost=0.00..14,395.27 rows=121,668 width=32) (actual time=0.010..47.991 rows=120,852 loops=1)

  • Filter: ((scout_id IS NOT NULL) AND ((status)::text = 'published'::text))
  • Rows Removed by Filter: 15,218
27. 0.010 0.085 ↑ 1.0 368 1 / 3

Hash (cost=76.68..76.68 rows=368 width=8) (actual time=0.255..0.255 rows=368 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
28. 0.075 0.075 ↑ 1.0 368 1 / 3

Seq Scan on project pr_1 (cost=0.00..76.68 rows=368 width=8) (actual time=0.005..0.224 rows=368 loops=1)

29. 21,384.850 27,255.925 ↓ 1.0 18,688 5,275

Bitmap Heap Scan on lead (cost=504.94..5,957.04 rows=18,648 width=37) (actual time=1.461..5.167 rows=18,688 loops=5,275)

  • Recheck Cond: (project_id = 54)
  • Heap Blocks: exact=14,089,525
30. 5,871.075 5,871.075 ↓ 1.0 18,688 5,275

Bitmap Index Scan on lead_idx (cost=0.00..500.28 rows=18,648 width=0) (actual time=1.113..1.113 rows=18,688 loops=5,275)

  • Index Cond: (project_id = 54)
Planning time : 4.349 ms
Execution time : 43,525.078 ms