explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7mL0

Settings
# exclusive inclusive rows x rows loops node
1. 135.078 6,740.897 ↑ 2.0 829,209 1

Subquery Scan on v_gha_windward_dossier_nego_palier_3 (cost=658,548.63..708,505.23 rows=1,665,220 width=99) (actual time=6,135.324..6,740.897 rows=829,209 loops=1)

  • Buffers: shared hit=37,711
  • time=0.031..25.133 rows=152,705 loops=3)
2. 283.672 6,605.819 ↑ 2.0 829,209 1

Unique (cost=658,548.63..691,853.03 rows=1,665,220 width=141) (actual time=6,135.318..6,605.819 rows=829,209 loops=1)

  • Buffers: shared hit=37,711
3.          

CTE levels

4. 672.396 1,655.778 ↑ 1.0 833,892 1

WindowAgg (cost=100,457.12..117,134.96 rows=833,892 width=83) (actual time=876.607..1,655.778 rows=833,892 loops=1)

  • Buffers: shared hit=10,107
5. 792.247 983.382 ↑ 1.0 833,892 1

Sort (cost=100,457.12..102,541.85 rows=833,892 width=75) (actual time=876.589..983.382 rows=833,892 loops=1)

  • Sort Key: t_gha_level.t_gha_condition_attributs_oid, t_gha_level.borne_inf
  • Sort Method: quicksort Memory: 141,843kB
  • Buffers: shared hit=10,107
6. 191.135 191.135 ↑ 1.0 833,892 1

Seq Scan on t_gha_level (cost=0.00..18,445.92 rows=833,892 width=75) (actual time=0.020..191.135 rows=833,892 loops=1)

  • Buffers: shared hit=10,107
7. 936.990 6,322.147 ↑ 2.0 829,209 1

Sort (cost=541,413.67..545,576.72 rows=1,665,220 width=141) (actual time=6,135.317..6,322.147 rows=829,209 loops=1)

  • Sort Key: obj.oid, levels_1.borne_inf, (CASE WHEN ((cond.calcul_mode)::text = 'TAX_BRACKET'::text) THEN levels_1.borne_sup ELSE (min(levels.borne_inf))::numeric(19,2) END), levels_1.valeur, levels.valeur, attr.val_min_att, attr.base_prec
  • Sort Method: quicksort Memory: 131,996kB
  • Buffers: shared hit=37,711
8. 1,082.730 5,385.157 ↑ 2.0 829,209 1

HashAggregate (cost=344,357.52..369,335.82 rows=1,665,220 width=141) (actual time=4,932.338..5,385.157 rows=829,209 loops=1)

  • Group Key: obj.oid, levels_1.borne_inf, attr.t_cen_num_cen, levels_1.valeur, levels_1.borne_sup, cond.calcul_mode, attr.val_min_att, attr.base_prec, levels.valeur,levels_1.t_gha_condition_attributs_oid
  • Buffers: shared hit=37,711
9. 500.603 4,302.427 ↑ 2.0 829,221 1

Merge Left Join (cost=183,765.33..298,563.97 rows=1,665,220 width=141) (actual time=3,657.602..4,302.427 rows=829,221 loops=1)

  • Merge Cond: ((levels_1.t_gha_condition_attributs_oid = levels.t_gha_condition_attributs_oid) AND (((levels_1.row_numbers + 1)) = levels.row_numbers))
  • Join Filter: (levels.borne_inf > levels_1.borne_inf)
  • Rows Removed by Join Filter: 37
  • Buffers: shared hit=37,711
10. 474.224 3,321.300 ↓ 3.5 829,221 1

Sort (cost=85,076.29..85,675.37 rows=239,631 width=109) (actual time=3,249.891..3,321.300 rows=829,221 loops=1)

  • Sort Key: levels_1.t_gha_condition_attributs_oid, ((levels_1.row_numbers + 1))
  • Sort Method: quicksort Memory: 141,186kB
  • Buffers: shared hit=37,711
11. 391.268 2,847.076 ↓ 3.5 829,221 1

Hash Join (cost=41,463.47..63,664.72 rows=239,631 width=109) (actual time=1,287.876..2,847.076 rows=829,221 loops=1)

  • Hash Cond: (levels_1.t_gha_condition_attributs_oid = cmn_cond.t_gha_condition_attributs_oid)
  • Buffers: shared hit=37,711
12. 2,044.647 2,044.647 ↑ 1.0 833,892 1

CTE Scan on levels levels_1 (cost=0.00..16,677.84 rows=833,892 width=76) (actual time=876.611..2,044.647 rows=833,892 loops=1)

  • Buffers: shared hit=10,107
13. 36.387 411.161 ↓ 3.5 152,699 1

Hash (cost=40,914.95..40,914.95 rows=43,882 width=49) (actual time=411.161..411.161 rows=152,699 loops=1)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 11,756kB
  • Buffers: shared hit=27,604
14. 18.984 374.774 ↓ 3.5 152,699 1

Gather (cost=28,271.30..40,914.95 rows=43,882 width=49) (actual time=308.086..374.774 rows=152,699 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=27,604
15. 38.807 355.790 ↓ 2.8 50,900 3 / 3

Parallel Hash Join (cost=27,271.30..35,526.75 rows=18,284 width=49) (actual time=301.437..355.790 rows=50,900 loops=3)

  • Hash Cond: (attr.oid = cmn_cond.t_gha_condition_attributs_oid)
  • Buffers: shared hit=27,604
16. 16.344 16.344 ↑ 1.3 51,867 3 / 3

Parallel Seq Scan on t_gha_condition_attributs attr (cost=0.00..7,953.34 rows=64,834 width=27) (actual time=0.012..16.344 rows=51,867 loops=3)

  • Buffers: shared hit=7,305
17. 24.943 300.639 ↓ 3.6 50,900 3 / 3

Parallel Hash (cost=27,094.37..27,094.37 rows=14,155 width=22) (actual time=300.639..300.639 rows=50,900 loops=3)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12,000kB
  • Buffers: shared hit=20,169
18. 101.340 275.696 ↓ 3.6 50,900 3 / 3

Parallel Hash Join (cost=14,699.08..27,094.37 rows=14,155 width=22) (actual time=146.324..275.696 rows=50,900 loops=3)

  • Hash Cond: (obj.oid_cond = cond.oid)
  • Buffers: shared hit=20,169
19. 28.259 28.259 ↓ 1.0 165,882 3 / 3

Parallel Seq Scan on t_gha_obj_nego obj (cost=0.00..11,734.31 rows=160,531 width=16) (actual time=0.008..28.259 rows=165,882 loops=3)

  • Buffers: shared hit=10,129
20. 22.224 146.097 ↓ 2.7 50,901 3 / 3

Parallel Hash (cost=14,462.50..14,462.50 rows=18,927 width=30) (actual time=146.096..146.097 rows=50,901 loops=3)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 13,216kB
  • Buffers: shared hit=10,040
21. 21.507 123.873 ↓ 2.7 50,901 3 / 3

Hash Join (cost=4,367.86..14,462.50 rows=18,927 width=30) (actual time=60.231..123.873 rows=50,901 loops=3)

  • Hash Cond: (cond.oid = cmn_cond.oid)
  • Buffers: shared hit=10,040
22. 43.175 43.175 ↑ 1.3 50,901 3 / 3

Parallel Seq Scan on t_gha_condition cond (cost=0.00..9,927.03 rows=63,849 width=14) (actual time=0.026..43.175 rows=50,901 loops=3)

  • Filter: ((condition_type)::text = 'COMMON_LEVEL'::text)
  • Rows Removed by Filter: 120,813
  • Buffers: shared hit=7,244
23. 59.191 59.191 ↑ 1.0 152,705 3 / 3

Hash (cost=2,459.05..2,459.05 rows=152,705 width=16) (actual time=59.191..59.191 rows=152,705 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,207kB
  • Buffers: shared hit=2,796
  • -> Seq Scan on t_gha_common_condition cmn_cond (cost=0.00..2459.05 rows=152,705 width=16) (actual
  • Buffers: shared hit=2,796
24. 306.191 480.524 ↑ 1.0 833,892 1

Sort (cost=98,689.04..100,773.77 rows=833,892 width=56) (actual time=407.691..480.524 rows=833,892 loops=1)

  • Sort Key: levels.t_gha_condition_attributs_oid, levels.row_numbers
  • Sort Method: quicksort Memory: 89,724kB
25. 174.333 174.333 ↑ 1.0 833,892 1

CTE Scan on levels (cost=0.00..16,677.84 rows=833,892 width=56) (actual time=0.005..174.333 rows=833,892 loops=1)

Planning time : 3.152 ms
Execution time : 6,951.300 ms