explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gu2U

Settings
# exclusive inclusive rows x rows loops node
1. 0.971 19,901.244 ↑ 65,204.8 4 1

GroupAggregate (cost=3,518,511.53..3,718,037.48 rows=260,819 width=274) (actual time=19,900.647..19,901.244 rows=4 loops=1)

2. 2.433 19,900.273 ↑ 3,459.1 754 1

Sort (cost=3,518,511.53..3,525,031.98 rows=2,608,182 width=274) (actual time=19,900.240..19,900.273 rows=754 loops=1)

  • Sort Key: filtered.d0, filtered.d1, filtered.d2
  • Sort Method: quicksort Memory: 131kB
3. 174.563 19,897.840 ↑ 3,459.1 754 1

Hash Join (cost=1,393,591.55..1,885,503.57 rows=2,608,182 width=274) (actual time=19,798.725..19,897.840 rows=754 loops=1)

  • Hash Cond: (filtered.m_id = mr.id)
4. 1,012.048 19,345.614 ↑ 3,459.1 754 1

Hash Left Join (cost=1,370,791.70..1,710,023.31 rows=2,608,182 width=124) (actual time=18,707.144..19,345.614 rows=754 loops=1)

  • Hash Cond: (filtered.tc_id = tcr.id)
5. 0.176 16,585.330 ↑ 3,459.1 754 1

Subquery Scan on filtered (cost=1,244,533.17..1,433,626.37 rows=2,608,182 width=112) (actual time=16,441.506..16,585.330 rows=754 loops=1)

6. 4,666.927 16,585.154 ↑ 3,459.1 754 1

GroupAggregate (cost=1,244,533.17..1,407,544.55 rows=2,608,182 width=80) (actual time=16,441.505..16,585.154 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 (...)
  • Rows Removed by Filter: 2507571
7. 2,935.878 11,918.227 ↑ 1.0 2,600,316 1

Sort (cost=1,244,533.17..1,251,053.63 rows=2,608,182 width=80) (actual time=11,455.449..11,918.227 rows=2,600,316 loops=1)

  • Sort Key: mr_1.id, tcr_1.id
  • Sort Method: external merge Disk: 128184kB
8. 489.637 8,982.349 ↑ 1.0 2,600,316 1

Hash Left Join (cost=142,183.78..618,895.22 rows=2,608,182 width=80) (actual time=2,396.235..8,982.349 rows=2,600,316 loops=1)

  • Hash Cond: (mrts.tag_id = mt.id)
9. 2,457.528 8,492.511 ↑ 1.0 2,600,316 1

Hash Left Join (cost=142,161.98..583,010.91 rows=2,608,182 width=56) (actual time=2,396.027..8,492.511 rows=2,600,316 loops=1)

  • Hash Cond: (tcr_1.id = tcrts.tcr_id)
  • Filter: ((mrts.tag_id = ANY ('{456,457,26,60,78}'::bigint[])) OR (tcrts.tag_id = ANY ('{456,457,26,60,78}'::bigint[])))
  • Rows Removed by Filter: 13780729
10. 3,313.284 6,034.628 ↓ 1.0 16,380,856 1

Hash Right Join (cost=142,122.27..522,064.75 rows=16,225,199 width=24) (actual time=2,395.651..6,034.628 rows=16,380,856 loops=1)

  • Hash Cond: (tcr_1.matrix_run_id = mr_1.id)
11. 815.218 815.218 ↑ 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.012..815.218 rows=2,497,312 loops=1)

12. 769.846 1,906.126 ↓ 1.0 2,021,911 1

Hash (cost=106,996.66..106,996.66 rows=2,020,689 width=16) (actual time=1,906.126..1,906.126 rows=2,021,911 loops=1)

  • Buckets: 4096 Batches: 128 Memory Usage: 785kB
13. 799.369 1,136.280 ↓ 1.0 2,021,911 1

Hash Right Join (cost=17,028.85..106,996.66 rows=2,020,689 width=16) (actual time=160.221..1,136.280 rows=2,021,911 loops=1)

  • Hash Cond: (mrts.mr_id = mr_1.id)
14. 201.402 201.402 ↑ 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.004..201.402 rows=2,020,689 loops=1)

15. 93.709 135.509 ↑ 1.0 311,032 1

Hash (cost=11,924.60..11,924.60 rows=311,060 width=8) (actual time=135.509..135.509 rows=311,032 loops=1)

  • Buckets: 4096 Batches: 16 Memory Usage: 774kB
16. 41.800 41.800 ↑ 1.0 311,032 1

Seq Scan on stmatrixruns mr_1 (cost=0.00..11,924.60 rows=311,060 width=8) (actual time=0.002..41.800 rows=311,032 loops=1)

17. 0.062 0.355 ↑ 1.4 310 1

Hash (cost=34.41..34.41 rows=424 width=40) (actual time=0.355..0.355 rows=310 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
18. 0.057 0.293 ↑ 1.4 310 1

Hash Left Join (cost=15.51..34.41 rows=424 width=40) (actual time=0.167..0.293 rows=310 loops=1)

  • Hash Cond: (tct.group_id = tcg.id)
19. 0.064 0.230 ↑ 1.4 310 1

Hash Left Join (cost=14.30..27.38 rows=424 width=40) (actual time=0.153..0.230 rows=310 loops=1)

  • Hash Cond: (tcrts.tag_id = tct.id)
20. 0.028 0.028 ↑ 1.4 310 1

Seq Scan on sttcrtags tcrts (cost=0.00..7.24 rows=424 width=16) (actual time=0.007..0.028 rows=310 loops=1)

21. 0.078 0.138 ↓ 1.0 459 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
22. 0.060 0.060 ↓ 1.0 459 1

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

23. 0.000 0.006 ↓ 1.1 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
24. 0.006 0.006 ↓ 1.1 10 1

Seq Scan on sttaggroups tcg (cost=0.00..1.09 rows=9 width=8) (actual time=0.004..0.006 rows=10 loops=1)

25. 0.078 0.201 ↓ 1.0 459 1

Hash (cost=16.08..16.08 rows=458 width=32) (actual time=0.201..0.201 rows=459 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
26. 0.100 0.123 ↓ 1.0 459 1

Hash Left Join (cost=1.20..16.08 rows=458 width=32) (actual time=0.012..0.123 rows=459 loops=1)

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

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

28. 0.001 0.003 ↓ 1.1 10 1

Hash (cost=1.09..1.09 rows=9 width=8) (actual time=0.003..0.003 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 0.002 0.002 ↓ 1.1 10 1

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

30. 1,349.265 1,748.236 ↑ 1.0 2,497,312 1

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

  • Buckets: 2048 Batches: 256 Memory Usage: 510kB
31. 398.971 398.971 ↑ 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.007..398.971 rows=2,497,312 loops=1)

32. 313.407 377.663 ↑ 1.0 311,032 1

Hash (cost=11,924.60..11,924.60 rows=311,060 width=158) (actual time=377.663..377.663 rows=311,032 loops=1)

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

Seq Scan on stmatrixruns mr (cost=0.00..11,924.60 rows=311,060 width=158) (actual time=0.005..64.256 rows=311,032 loops=1)