explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3UTi

Settings
# exclusive inclusive rows x rows loops node
1. 308.397 4,103.949 ↓ 4.1 966,994 1

Unique (cost=131,832.35..136,522.03 rows=234,484 width=73) (actual time=3,609.318..4,103.949 rows=966,994 loops=1)

  • Buffers: shared hit=40,404
2. 1,041.520 3,795.552 ↓ 4.1 966,994 1

Sort (cost=131,832.35..132,418.56 rows=234,484 width=73) (actual time=3,609.316..3,795.552 rows=966,994 loops=1)

  • Sort Key: obj.oid, level.borne_inf, (CASE WHEN ((cond.calcul_mode)::text = 'TAX_BRACKET'::text) THEN level.borne_sup ELSE (min(t_gha_level.borne_inf))::numeric(19,2) END), level.valeur, t_gha_level.valeur, attr.val_min_att, attr.base_prec
  • Sort Method: quicksort Memory: 108,118kB
  • Buffers: shared hit=40,404
3. 1,114.331 2,754.032 ↓ 4.1 966,994 1

Finalize HashAggregate (cost=107,400.14..110,917.40 rows=234,484 width=73) (actual time=2,316.686..2,754.032 rows=966,994 loops=1)

  • Group Key: obj.oid, level.borne_inf, attr.t_cen_num_cen, level.valeur, level.borne_sup, cond.calcul_mode, attr.val_min_att, attr.base_prec, t_gha_level.valeur
  • Buffers: shared hit=40,404
4. 160.417 1,639.701 ↓ 4.9 967,008 1

Gather (cost=81,997.62..102,515.04 rows=195,404 width=85) (actual time=1,346.394..1,639.701 rows=967,008 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=40,404
5. 372.619 1,479.284 ↓ 3.3 322,336 3 / 3

Partial HashAggregate (cost=80,997.62..81,974.64 rows=97,702 width=85) (actual time=1,341.272..1,479.284 rows=322,336 loops=3)

  • Group Key: obj.oid, level.borne_inf, attr.t_cen_num_cen, level.valeur, level.borne_sup, cond.calcul_mode, attr.val_min_att, attr.base_prec, t_gha_level.valeur
  • Buffers: shared hit=40,404
6. 515.516 1,106.665 ↓ 3.3 322,411 3 / 3

Parallel Hash Left Join (cost=47,460.33..78,555.07 rows=97,702 width=58) (actual time=436.883..1,106.665 rows=322,411 loops=3)

  • Hash Cond: (level.t_gha_condition_attributs_oid = t_gha_level.t_gha_condition_attributs_oid)
  • Join Filter: ((t_gha_level.borne_inf > level.borne_inf) AND (level.oid >= (t_gha_level.oid - 1)))
  • Rows Removed by Join Filter: 2,408,728
  • Buffers: shared hit=40,404
7. 129.985 456.466 ↓ 2.8 268,621 3 / 3

Parallel Hash Join (cost=30,060.42..45,077.86 rows=95,463 width=64) (actual time=299.825..456.466 rows=268,621 loops=3)

  • Hash Cond: (level.t_gha_condition_attributs_oid = cmn_cond.t_gha_condition_attributs_oid)
  • Buffers: shared hit=30,596
8. 27.267 27.267 ↑ 1.2 270,148 3 / 3

Parallel Seq Scan on t_gha_level level (cost=0.00..13,178.85 rows=337,685 width=31) (actual time=0.007..27.267 rows=270,148 loops=3)

  • Buffers: shared hit=9,802
9. 21.877 299.214 ↓ 2.0 49,555 3 / 3

Parallel Hash (cost=29,745.48..29,745.48 rows=25,195 width=49) (actual time=299.213..299.214 rows=49,555 loops=3)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 13,120kB
  • Buffers: shared hit=20,794
10. 28.392 277.337 ↓ 2.0 49,555 3 / 3

Parallel Hash Join (cost=26,868.05..29,745.48 rows=25,195 width=49) (actual time=244.204..277.337 rows=49,555 loops=3)

  • Hash Cond: (attr.oid = cmn_cond.t_gha_condition_attributs_oid)
  • Buffers: shared hit=20,794
11. 4.864 4.864 ↑ 1.8 50,504 3 / 3

Parallel Seq Scan on t_gha_condition_attributs attr (cost=0.00..2,438.24 rows=89,124 width=27) (actual time=0.007..4.864 rows=50,504 loops=3)

  • Buffers: shared hit=1,547
12. 17.791 244.081 ↓ 2.8 49,555 3 / 3

Parallel Hash (cost=26,644.96..26,644.96 rows=17,847 width=22) (actual time=244.081..244.081 rows=49,555 loops=3)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 11,808kB
  • Buffers: shared hit=19,247
13. 80.696 226.290 ↓ 2.8 49,555 3 / 3

Parallel Hash Join (cost=14,139.52..26,644.96 rows=17,847 width=22) (actual time=125.887..226.290 rows=49,555 loops=3)

  • Hash Cond: (obj.oid_cond = cond.oid)
  • Buffers: shared hit=19,247
14. 19.834 19.834 ↑ 1.3 161,750 3 / 3

Parallel Seq Scan on t_gha_obj_nego obj (cost=0.00..11,672.88 rows=202,188 width=16) (actual time=0.008..19.834 rows=161,750 loops=3)

  • Buffers: shared hit=9,651
15. 19.753 125.760 ↓ 2.7 49,556 3 / 3

Parallel Hash (cost=13,908.49..13,908.49 rows=18,482 width=30) (actual time=125.759..125.760 rows=49,556 loops=3)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12,960kB
  • Buffers: shared hit=9,596
16. 18.690 106.007 ↓ 2.7 49,556 3 / 3

Hash Join (cost=4,252.10..13,908.49 rows=18,482 width=30) (actual time=45.654..106.007 rows=49,556 loops=3)

  • Hash Cond: (cond.oid = cmn_cond.oid)
  • Buffers: shared hit=9,596
17. 42.452 42.452 ↑ 1.3 49,556 3 / 3

Parallel Seq Scan on t_gha_condition cond (cost=0.00..9,492.40 rows=62,475 width=14) (actual time=0.014..42.452 rows=49,556 loops=3)

  • Filter: ((condition_type)::text = 'COMMON_LEVEL'::text)
  • Rows Removed by Filter: 117,957
  • Buffers: shared hit=6,875
18. 24.248 44.865 ↑ 1.0 148,671 3 / 3

Hash (cost=2,393.71..2,393.71 rows=148,671 width=16) (actual time=44.865..44.865 rows=148,671 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,017kB
  • Buffers: shared hit=2,721
19. 20.617 20.617 ↑ 1.0 148,671 3 / 3

Seq Scan on t_gha_common_condition cmn_cond (cost=0.00..2,393.71 rows=148,671 width=16) (actual time=0.024..20.617 rows=148,671 loops=3)

  • Buffers: shared hit=2,721
20. 71.507 134.683 ↑ 1.2 270,148 3 / 3

Parallel Hash (cost=13,178.85..13,178.85 rows=337,685 width=26) (actual time=134.682..134.683 rows=270,148 loops=3)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 58,848kB
  • Buffers: shared hit=9,802
21. 63.176 63.176 ↑ 1.2 270,148 3 / 3

Parallel Seq Scan on t_gha_level (cost=0.00..13,178.85 rows=337,685 width=26) (actual time=0.012..63.176 rows=270,148 loops=3)

  • Buffers: shared hit=9,802
Planning time : 1.547 ms
Execution time : 4,264.992 ms