explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YdnIe

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

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

2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=828,798.47..833,531.12 rows=42,068 width=150) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=828,798.47..832,584.59 rows=42,068 width=142) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=828,798.47..831,953.57 rows=42,068 width=138) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Sort (cost=828,798.47..828,903.64 rows=42,068 width=118) (actual rows= loops=)

  • 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
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=181,228.87..825,567.55 rows=42,068 width=118) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Nested Loop (cost=181,214.12..824,764.03 rows=42,068 width=91) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Unique (cost=181,213.84..202,898.54 rows=433,694 width=500) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=181,213.84..182,298.08 rows=433,694 width=500) (actual rows= loops=)

  • 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
10. 0.000 0.000 ↓ 0.0

Append (cost=0.00..42,770.88 rows=433,694 width=500) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on m_export_n79 (cost=0.00..29,078.79 rows=355,604 width=352) (actual rows= loops=)

  • Filter: ((date_format >= '2011-01-01'::date) AND (date_format <= '2017-12-31'::date) AND (effectif > 0))
12. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..10,136.05 rows=78,090 width=500) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on t_observations (cost=0.00..9,355.15 rows=78,090 width=398) (actual rows= loops=)

  • Filter: ((date_obs >= '2011-01-01'::date) AND (date_obs <= '2017-12-31'::date) AND (effectif > 0))
14. 0.000 0.000 ↓ 0.0

Index Scan using sidx_communes_2016_wkb_geometry on communes_2016 (cost=0.28..1.41 rows=1 width=47) (actual rows= loops=)

  • 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))
15. 0.000 0.000 ↓ 0.0

Hash (cost=11.00..11.00 rows=300 width=44) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

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