explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y83t

Settings
# exclusive inclusive rows x rows loops node
1. 0.929 163,694.898 ↑ 410,023.5 4 1

GroupAggregate (cost=23,555,957.50..24,810,628.97 rows=1,640,094 width=172) (actual time=163,694.319..163,694.898 rows=4 loops=1)

2.          

CTE filtered_tcr

3. 2,414.087 11,044.271 ↓ 1.0 2,586,608 1

Hash Left Join (cost=130,702.57..591,838.25 rows=2,524,493 width=16) (actual time=4,310.725..11,044.271 rows=2,586,608 loops=1)

  • Hash Cond: (tcr_1.id = tcrts_1.tcr_id)
  • Filter: ((mrts_1.tag_id = ANY ('{456,457,26,60,78}'::bigint[])) OR (tcrts_1.tag_id = ANY ('{456,457,26,60,78}'::bigint[])))
  • Rows Removed by Filter: 13706261
4. 6,439.332 8,630.117 ↓ 1.0 16,292,680 1

Hash Join (cost=130,692.59..510,649.67 rows=16,226,659 width=24) (actual time=4,310.645..8,630.117 rows=16,292,680 loops=1)

  • Hash Cond: (tcr_1.matrix_run_id = mr_1.id)
5. 424.789 424.789 ↑ 1.0 2,497,312 1

Seq Scan on sttestcaseruns tcr_1 (cost=0.00..80,402.68 rows=2,497,668 width=16) (actual time=0.003..424.789 rows=2,497,312 loops=1)

6. 1,312.426 1,765.996 ↓ 1.0 2,021,911 1

Hash (cost=95,566.98..95,566.98 rows=2,020,689 width=16) (actual time=1,765.996..1,765.996 rows=2,021,911 loops=1)

  • Buckets: 4096 Batches: 128 Memory Usage: 784kB
7. 252.231 453.570 ↓ 1.0 2,021,911 1

Merge Left Join (cost=3.68..95,566.98 rows=2,020,689 width=16) (actual time=0.015..453.570 rows=2,021,911 loops=1)

  • Merge Cond: (mr_1.id = mrts_1.mr_id)
8. 25.668 25.668 ↑ 1.0 311,032 1

Index Only Scan using stmatrixruns_pkey on stmatrixruns mr_1 (cost=0.42..8,085.90 rows=311,032 width=8) (actual time=0.007..25.668 rows=311,032 loops=1)

  • Heap Fetches: 0
9. 175.671 175.671 ↑ 1.0 2,020,689 1

Index Only Scan using stmrtags_pkey on stmrtags mrts_1 (cost=0.43..61,446.76 rows=2,020,689 width=16) (actual time=0.006..175.671 rows=2,020,689 loops=1)

  • Heap Fetches: 0
10. 0.032 0.067 ↑ 1.0 310 1

Hash (cost=6.10..6.10 rows=310 width=16) (actual time=0.067..0.067 rows=310 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
11. 0.035 0.035 ↑ 1.0 310 1

Seq Scan on sttcrtags tcrts_1 (cost=0.00..6.10 rows=310 width=16) (actual time=0.003..0.035 rows=310 loops=1)

12. 2.121 163,693.969 ↑ 21,751.9 754 1

Sort (cost=22,964,119.24..23,005,121.58 rows=16,400,934 width=172) (actual time=163,693.947..163,693.969 rows=754 loops=1)

  • Sort Key: filtered.d0, filtered.d1, filtered.d2
  • Sort Method: quicksort Memory: 131kB
13. 0.090 163,691.848 ↑ 21,751.9 754 1

Subquery Scan on filtered (cost=12,597,366.41..13,991,445.80 rows=16,400,934 width=172) (actual time=162,838.762..163,691.848 rows=754 loops=1)

14. 15,768.127 163,691.758 ↑ 21,751.9 754 1

GroupAggregate (cost=12,597,366.41..13,827,436.46 rows=16,400,934 width=242) (actual time=162,838.761..163,691.758 rows=754 loops=1)

  • Filter: ((count(DISTINCT CASE WHEN (tcrts.tag_id = ANY ('{456,457,26,60,78}'::bigint[])) THEN tcg.id ELSE NULL::bigint END) + count(DISTINCT CASE WHEN (mrts.tag_id = ANY ('{456,457,26,60,78}'::bigint[])) THEN mg.id ELSE NULL::bigint END (...)
  • Rows Removed by Filter: 2494263
15. 95,799.650 147,923.631 ↓ 1.0 16,912,057 1

Sort (cost=12,597,366.41..12,638,368.74 rows=16,400,934 width=242) (actual time=144,372.083..147,923.631 rows=16,912,057 loops=1)

  • Sort Key: mr.id, tcr.id
  • Sort Method: external merge Disk: 1656872kB
16. 2,009.615 52,123.981 ↓ 1.0 16,912,057 1

Hash Left Join (cost=351,171.24..1,102,085.46 rows=16,400,934 width=242) (actual time=20,527.971..52,123.981 rows=16,912,057 loops=1)

  • Hash Cond: (tct.group_id = tcg.id)
17. 21,821.966 50,114.361 ↓ 1.0 16,912,057 1

Hash Join (cost=351,170.02..876,571.39 rows=16,400,934 width=242) (actual time=20,527.960..50,114.361 rows=16,912,057 loops=1)

  • Hash Cond: (ftcr.matrix_run_id = mr.id)
18. 321.355 22,277.078 ↓ 1.0 2,586,716 1

Hash Left Join (cost=126,287.07..276,025.77 rows=2,524,493 width=60) (actual time=9,371.173..22,277.078 rows=2,586,716 loops=1)

  • Hash Cond: (tcr.id = tcrts.tcr_id)
19. 6,678.018 21,955.458 ↓ 1.0 2,586,608 1

Hash Join (cost=126,258.53..263,371.63 rows=2,524,493 width=28) (actual time=9,370.901..21,955.458 rows=2,586,608 loops=1)

  • Hash Cond: (ftcr.tcr_id = tcr.id)
20. 11,599.742 11,599.742 ↓ 1.0 2,586,608 1

CTE Scan on filtered_tcr ftcr (cost=0.00..50,489.86 rows=2,524,493 width=16) (actual time=4,310.727..11,599.742 rows=2,586,608 loops=1)

21. 3,276.048 3,677.698 ↑ 1.0 2,497,312 1

Hash (cost=80,402.68..80,402.68 rows=2,497,668 width=20) (actual time=3,677.698..3,677.698 rows=2,497,312 loops=1)

  • Buckets: 2048 Batches: 256 Memory Usage: 510kB
22. 401.650 401.650 ↑ 1.0 2,497,312 1

Seq Scan on sttestcaseruns tcr (cost=0.00..80,402.68 rows=2,497,668 width=20) (actual time=0.002..401.650 rows=2,497,312 loops=1)

23. 0.046 0.265 ↑ 1.0 310 1

Hash (cost=24.67..24.67 rows=310 width=40) (actual time=0.265..0.265 rows=310 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
24. 0.070 0.219 ↑ 1.0 310 1

Hash Left Join (cost=14.30..24.67 rows=310 width=40) (actual time=0.134..0.219 rows=310 loops=1)

  • Hash Cond: (tcrts.tag_id = tct.id)
25. 0.024 0.024 ↑ 1.0 310 1

Seq Scan on sttcrtags tcrts (cost=0.00..6.10 rows=310 width=16) (actual time=0.005..0.024 rows=310 loops=1)

26. 0.061 0.125 ↓ 1.0 459 1

Hash (cost=8.58..8.58 rows=458 width=32) (actual time=0.125..0.125 rows=459 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
27. 0.064 0.064 ↓ 1.0 459 1

Seq Scan on sttags tct (cost=0.00..8.58 rows=458 width=32) (actual time=0.004..0.064 rows=459 loops=1)

28. 3,229.998 6,015.317 ↓ 1.0 2,021,911 1

Hash (cost=146,344.33..146,344.33 rows=2,020,689 width=190) (actual time=6,015.317..6,015.317 rows=2,021,911 loops=1)

  • Buckets: 1024 Batches: 512 Memory Usage: 424kB
29. 421.377 2,785.319 ↓ 1.0 2,021,911 1

Hash Left Join (cost=22,821.05..146,344.33 rows=2,020,689 width=190) (actual time=936.175..2,785.319 rows=2,021,911 loops=1)

  • Hash Cond: (mrts.tag_id = mt.id)
30. 1,341.032 2,363.738 ↓ 1.0 2,021,911 1

Hash Right Join (cost=22,799.22..118,538.03 rows=2,020,689 width=166) (actual time=935.961..2,363.738 rows=2,021,911 loops=1)

  • Hash Cond: (mrts.mr_id = mr.id)
31. 204.445 204.445 ↑ 1.0 2,020,689 1

Seq Scan on stmrtags mrts (cost=0.00..31,129.89 rows=2,020,689 width=16) (actual time=0.002..204.445 rows=2,020,689 loops=1)

32. 762.207 818.261 ↑ 1.0 311,032 1

Hash (cost=11,924.32..11,924.32 rows=311,032 width=158) (actual time=818.261..818.261 rows=311,032 loops=1)

  • Buckets: 1024 Batches: 64 Memory Usage: 305kB
33. 56.054 56.054 ↑ 1.0 311,032 1

Seq Scan on stmatrixruns mr (cost=0.00..11,924.32 rows=311,032 width=158) (actual time=0.002..56.054 rows=311,032 loops=1)

34. 0.060 0.204 ↓ 1.0 459 1

Hash (cost=16.10..16.10 rows=458 width=32) (actual time=0.204..0.204 rows=459 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
35. 0.100 0.144 ↓ 1.0 459 1

Hash Left Join (cost=1.23..16.10 rows=458 width=32) (actual time=0.018..0.144 rows=459 loops=1)

  • Hash Cond: (mt.group_id = mg.id)
36. 0.038 0.038 ↓ 1.0 459 1

Seq Scan on sttags mt (cost=0.00..8.58 rows=458 width=32) (actual time=0.008..0.038 rows=459 loops=1)

37. 0.002 0.006 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.006..0.006 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
38. 0.004 0.004 ↑ 1.0 10 1

Seq Scan on sttaggroups mg (cost=0.00..1.10 rows=10 width=8) (actual time=0.002..0.004 rows=10 loops=1)

39. 0.002 0.005 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.005..0.005 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.003 0.003 ↑ 1.0 10 1

Seq Scan on sttaggroups tcg (cost=0.00..1.10 rows=10 width=8) (actual time=0.002..0.003 rows=10 loops=1)