explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mG9n

Settings
# exclusive inclusive rows x rows loops node
1. 3.592 48,945.852 ↑ 151.0 748 1

Sort (cost=5,720,371.54..5,720,654 rows=112,984 width=273) (actual time=48,945.714..48,945.852 rows=748 loops=1)

  • Sort Key: (count(DISTINCT datas.id_synthese)) DESC, taxref.group1_inpn, taxref.group2_inpn, taxref.nom_valide
  • Sort Method: quicksort Memory: 461kB
  • Buffers: shared hit=1,793,090 read=2,650,241, temp read=134,668 written=137,754
2.          

CTE datas

3. 0.000 19,049.875 ↑ 1.1 442,007 1

Gather (cost=1,000.44..5,161,446.64 rows=495,668 width=41) (actual time=153.062..19,049.875 rows=442,007 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=1,768,648 read=2,650,241
4. 154.330 20,170.560 ↑ 1.4 88,401 5 / 5

Nested Loop (cost=0.44..5,110,879.84 rows=123,917 width=41) (actual time=129.551..20,170.56 rows=88,401 loops=5)

  • Buffers: shared hit=1,768,648 read=2,650,241
5. 19,662.624 19,662.624 ↑ 1.4 88,401 5 / 5

Seq Scan on cor_area_synthese cor_area_synthese (cost=0..4,509,611.6 rows=123,917 width=4) (actual time=129.477..19,662.624 rows=88,401 loops=5)

  • Filter: (cor_area_synthese.id_area = 2,191,825)
  • Buffers: shared hit=616 read=2,650,241
6. 353.606 353.606 ↑ 1.0 1 442,007 / 5

Index Scan using pk_synthese on synthese synthese (cost=0.44..4.85 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=442,007)

  • Index Cond: (synthese.id_synthese = cor_area_synthese.id_synthese)
  • Buffers: shared hit=1,768,032
7. 14,108.197 48,942.260 ↑ 151.0 748 1

GroupAggregate (cost=503,723.75..534,766.8 rows=112,984 width=273) (actual time=32,264.674..48,942.26 rows=748 loops=1)

  • Group Key: taxref.cd_ref, taxref.nom_vern, taxref.nom_valide, taxref.group1_inpn, taxref.group2_inpn
  • Buffers: shared hit=1,793,084 read=2,650,241, temp read=134,668 written=137,754
8. 10,374.550 34,834.063 ↓ 5.8 2,868,625 1

Sort (cost=503,723.75..504,962.92 rows=495,668 width=631) (actual time=32,261.341..34,834.063 rows=2,868,625 loops=1)

  • Sort Key: taxref.cd_ref, taxref.nom_vern, taxref.nom_valide, taxref.group1_inpn, taxref.group2_inpn
  • Sort Method: external merge Disk: 361,088kB
  • Buffers: shared hit=1,793,066 read=2,650,241, temp read=130,005 written=133,075
9. 2,298.970 24,459.513 ↓ 5.8 2,868,625 1

Hash Join (cost=69,266.3..178,985 rows=495,668 width=631) (actual time=1,110.018..24,459.513 rows=2,868,625 loops=1)

  • Buffers: shared hit=1,793,063 read=2,650,241, temp read=16,013 written=18,929
10. 1,700.343 22,158.850 ↓ 2.5 1,241,791 1

Hash Join (cost=69,056.61..163,128.41 rows=495,668 width=631) (actual time=1,108.308..22,158.85 rows=1,241,791 loops=1)

  • Buffers: shared hit=1,792,934 read=2,650,241, temp read=16,013 written=18,929
11. 19,503.913 19,503.913 ↑ 1.1 442,007 1

CTE Scan on datas datas (cost=0..9,913.36 rows=495,668 width=540) (actual time=153.067..19,503.913 rows=442,007 loops=1)

  • Buffers: shared hit=1,768,648 read=2,650,241, temp written=2,916
12. 210.302 954.594 ↓ 1.0 569,112 1

Hash (cost=53,564.07..53,564.07 rows=550,843 width=99) (actual time=954.593..954.594 rows=569,112 loops=1)

  • Buffers: shared hit=24,286, temp read=6,262 written=12,654
13. 256.349 744.292 ↓ 1.0 569,112 1

Hash Join (cost=44,245.97..53,564.07 rows=550,843 width=99) (actual time=482.409..744.292 rows=569,112 loops=1)

  • Buffers: shared hit=24,286, temp read=6,262 written=6,262
14. 6.257 6.257 ↑ 1.0 36,444 1

Seq Scan on taxref_protection_especes taxref_protection_especes (cost=0..867.44 rows=36,444 width=4) (actual time=0.01..6.257 rows=36,444 loops=1)

  • Buffers: shared hit=503
15. 223.918 481.686 ↑ 1.0 550,843 1

Hash (cost=29,291.43..29,291.43 rows=550,843 width=95) (actual time=481.685..481.686 rows=550,843 loops=1)

  • Buffers: shared hit=23,783, temp written=6,145
16. 257.768 257.768 ↑ 1.0 550,843 1

Seq Scan on taxref taxref (cost=0..29,291.43 rows=550,843 width=95) (actual time=0.023..257.768 rows=550,843 loops=1)

  • Buffers: shared hit=23,783
17. 0.692 1.693 ↑ 1.0 3,586 1

Hash (cost=164.86..164.86 rows=3,586 width=4) (actual time=1.693..1.693 rows=3,586 loops=1)

  • Buffers: shared hit=129
18. 1.001 1.001 ↑ 1.0 3,586 1

Seq Scan on taxref_liste_rouge_fr taxref_liste_rouge_fr (cost=0..164.86 rows=3,586 width=4) (actual time=0.027..1.001 rows=3,586 loops=1)

  • Buffers: shared hit=129
Planning time : 6.801 ms
Execution time : 49,076.108 ms