explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N9Vh

Settings
# exclusive inclusive rows x rows loops node
1. 0.099 105,926.269 ↑ 1.0 1 1

Limit (cost=828,798.47..828,798.59 rows=1 width=150) (actual time=105,926.172..105,926.269 rows=1 loops=1)

2. 0.026 105,926.170 ↑ 42,068.0 1 1

WindowAgg (cost=828,798.47..833,531.12 rows=42,068 width=150) (actual time=105,926.164..105,926.170 rows=1 loops=1)

3. 0.026 105,926.144 ↑ 42,068.0 1 1

WindowAgg (cost=828,798.47..832,584.59 rows=42,068 width=142) (actual time=105,926.138..105,926.144 rows=1 loops=1)

4. 0.325 105,926.118 ↑ 42,068.0 1 1

GroupAggregate (cost=828,798.47..831,953.57 rows=42,068 width=138) (actual time=105,926.113..105,926.118 rows=1 loops=1)

  • Group Key: m_export_n79.nom_espece, communes_2016.nom_com, t_liste_sp.famille_n79, t_liste_sp.nom_latin, communes_2016.wkb_geometry, communes_2016.code_dept
5. 13,263.890 105,925.793 ↑ 779.0 54 1

Sort (cost=828,798.47..828,903.64 rows=42,068 width=118) (actual time=105,925.494..105,925.793 rows=54 loops=1)

  • Sort Key: m_export_n79.nom_espece, communes_2016.nom_com, t_liste_sp.famille_n79, t_liste_sp.nom_latin, communes_2016.wkb_geometry
  • Sort Method: external merge Disk: 1034016kB
6. 7,485.014 92,661.903 ↓ 15.2 638,008 1

Hash Left Join (cost=181,228.87..825,567.55 rows=42,068 width=118) (actual time=14,379.296..92,661.903 rows=638,008 loops=1)

  • Hash Cond: (CASE WHEN (m_export_n79.nom_espece ~~* '%(%'::text) THEN left(m_export_n79.nom_espece, (strpos(m_export_n79.nom_espece, '('::text) - 2)) ELSE m_export_n79.nom_espece END = (t_liste_sp.nom_espece)::text)
7. 10,511.696 85,173.827 ↓ 15.2 638,008 1

Nested Loop (cost=181,214.12..824,764.03 rows=42,068 width=91) (actual time=14,376.194..85,173.827 rows=638,008 loops=1)

8. 5,569.103 25,006.910 ↓ 1.5 644,873 1

Unique (cost=181,213.84..202,898.54 rows=433,694 width=500) (actual time=14,372.426..25,006.910 rows=644,873 loops=1)

9. 10,538.527 19,437.807 ↓ 1.5 644,873 1

Sort (cost=181,213.84..182,298.08 rows=433,694 width=500) (actual time=14,372.418..19,437.807 rows=644,873 loops=1)

  • Sort Key: (('n79_'::text || m_export_n79.id_sighting)), m_export_n79.date_format, m_export_n79.horaire, m_export_n79.prenom_obs, (upper(m_export_n79.nom_obs)), m_export_n79.nom_espece, m_export_n79.effectif, m_export_n79.comportement, m_export_n79.atlas_code, m_export_n79.detail, m_export_n79.rmq, m_export_n79.rmq_prive, m_export_n79.rmq_liste, m_export_n79.localisation, m_export_n79.mortalite, (NULL::text), (NULL::text), m_export_n79.deuxieme_main, m_export_n79.the_geom
  • Sort Method: external merge Disk: 93368kB
10. 4,495.095 8,899.280 ↓ 1.5 644,873 1

Append (cost=0.00..42,770.88 rows=433,694 width=500) (actual time=0.963..8,899.280 rows=644,873 loops=1)

11. 3,352.872 3,352.872 ↓ 1.6 568,901 1

Seq Scan on m_export_n79 (cost=0.00..29,078.79 rows=355,604 width=352) (actual time=0.956..3,352.872 rows=568,901 loops=1)

  • Filter: ((date_format >= '2011-01-01'::date) AND (date_format <= '2017-12-31'::date) AND (effectif > 0))
  • Rows Removed by Filter: 99335
12. 516.768 1,051.313 ↑ 1.0 75,972 1

Subquery Scan on *SELECT* 2 (cost=0.00..10,136.05 rows=78,090 width=500) (actual time=0.846..1,051.313 rows=75,972 loops=1)

13. 534.545 534.545 ↑ 1.0 75,972 1

Seq Scan on t_observations (cost=0.00..9,355.15 rows=78,090 width=398) (actual time=0.837..534.545 rows=75,972 loops=1)

  • Filter: ((date_obs >= '2011-01-01'::date) AND (date_obs <= '2017-12-31'::date) AND (effectif > 0))
  • Rows Removed by Filter: 41765
14. 49,655.221 49,655.221 ↑ 1.0 1 644,873

Index Scan using sidx_communes_2016_wkb_geometry on communes_2016 (cost=0.28..1.41 rows=1 width=47) (actual time=0.068..0.077 rows=1 loops=644,873)

  • Index Cond: (m_export_n79.the_geom && wkb_geometry)
  • Filter: (((code_dept)::text = '79'::text) AND _st_intersects(m_export_n79.the_geom, wkb_geometry))
  • Rows Removed by Filter: 1
15. 1.005 3.062 ↑ 1.0 300 1

Hash (cost=11.00..11.00 rows=300 width=44) (actual time=3.056..3.062 rows=300 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
16. 2.057 2.057 ↑ 1.0 300 1

Seq Scan on t_liste_sp (cost=0.00..11.00 rows=300 width=44) (actual time=0.560..2.057 rows=300 loops=1)