explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bx0

Settings
# exclusive inclusive rows x rows loops node
1. 0.921 17,421.365 ↑ 65,204.8 4 1

GroupAggregate (cost=3,596,676.60..3,796,202.55 rows=260,819 width=172) (actual time=17,420.788..17,421.365 rows=4 loops=1)

2. 2.031 17,420.444 ↑ 3,459.1 754 1

Sort (cost=3,596,676.60..3,603,197.05 rows=2,608,182 width=172) (actual time=17,420.415..17,420.444 rows=754 loops=1)

  • Sort Key: filtered.d0, filtered.d1, filtered.d2
  • Sort Method: quicksort Memory: 131kB
3. 0.069 17,418.413 ↑ 3,459.1 754 1

Subquery Scan on filtered (cost=2,205,541.17..2,427,236.64 rows=2,608,182 width=172) (actual time=17,258.847..17,418.413 rows=754 loops=1)

4. 5,539.787 17,418.344 ↑ 3,459.1 754 1

GroupAggregate (cost=2,205,541.17..2,401,154.82 rows=2,608,182 width=242) (actual time=17,258.845..17,418.344 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
5. 3,716.104 11,878.557 ↑ 1.0 2,586,608 1

Sort (cost=2,205,541.17..2,212,061.63 rows=2,608,182 width=242) (actual time=11,226.590..11,878.557 rows=2,586,608 loops=1)

  • Sort Key: mr.id, tcr.id
  • Sort Method: external merge Disk: 228744kB
6. 512.272 8,162.453 ↑ 1.0 2,586,608 1

Hash Left Join (cost=191,218.78..715,179.22 rows=2,608,182 width=242) (actual time=1,408.811..8,162.453 rows=2,586,608 loops=1)

  • Hash Cond: (mrts.tag_id = mt.id)
7. 2,532.507 7,650.006 ↑ 1.0 2,586,608 1

Hash Left Join (cost=191,196.98..679,294.91 rows=2,608,182 width=218) (actual time=1,408.633..7,650.006 rows=2,586,608 loops=1)

  • Hash Cond: (tcr.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: 13706261
8. 3,297.069 5,117.200 ↓ 1.0 16,292,680 1

Hash Join (cost=191,157.27..618,348.75 rows=16,225,199 width=186) (actual time=1,408.322..5,117.200 rows=16,292,680 loops=1)

  • Hash Cond: (tcr.matrix_run_id = mr.id)
9. 414.270 414.270 ↑ 1.0 2,497,312 1

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

10. 340.832 1,405.861 ↓ 1.0 2,021,911 1

Hash (cost=118,538.66..118,538.66 rows=2,020,689 width=166) (actual time=1,405.861..1,405.861 rows=2,021,911 loops=1)

  • Buckets: 1024 Batches: 512 Memory Usage: 309kB
11. 765.099 1,065.029 ↓ 1.0 2,021,911 1

Hash Right Join (cost=22,799.85..118,538.66 rows=2,020,689 width=166) (actual time=104.655..1,065.029 rows=2,021,911 loops=1)

  • Hash Cond: (mrts.mr_id = mr.id)
12. 195.439 195.439 ↑ 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..195.439 rows=2,020,689 loops=1)

13. 49.470 104.491 ↑ 1.0 311,032 1

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

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

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

15. 0.044 0.299 ↑ 1.4 310 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
16. 0.045 0.255 ↑ 1.4 310 1

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

  • Hash Cond: (tct.group_id = tcg.id)
17. 0.065 0.206 ↑ 1.4 310 1

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

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

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

19. 0.066 0.111 ↓ 1.0 459 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
20. 0.045 0.045 ↓ 1.0 459 1

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

21. 0.002 0.004 ↓ 1.1 10 1

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

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

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

23. 0.077 0.175 ↓ 1.0 459 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
24. 0.068 0.098 ↓ 1.0 459 1

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

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

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

26. 0.002 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
27. 0.001 0.001 ↓ 1.1 10 1

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