explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NdWh : v_xml_file_ers3_log sur cfr

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.220 30,962.424 ↓ 1.9 537 1

Limit (cost=1.30..11,186.68 rows=277 width=125) (actual time=231.217..30,962.424 rows=537 loops=1)

  • Output: t_xml_file_ers3_log.xmlf_cdn, t_xml_file_ers3_log.xmlf_flux_cdn, t_xml_file_ers3_log.xmlf_creation_dt, t_xml_file_ers3_log.xmlf_file_name_lb, (((xpath('//ers:OPS/@AD'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:OPS/@FR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), ((((((xpath('/ers:OPS/@OD'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text || ' '::text) || ((xpath('/ers:OPS/@OT'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text) || ':00'::text)), t_xml_file_ers3_log.xmlf_on_lb, (((xpath('//ers:ERS/ers:LOG/@IR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:LOG/@XR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:LOG/@FS'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:LOG/@NA'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:LOG/ers:ELOG/@TN'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), t_xml_file_ers3_log.xmlf_type_message, t_xml_file_ers3_log.ret_cdn
2. 603.094 30,960.204 ↓ 1.9 537 1

Nested Loop (cost=1.30..11,186.68 rows=277 width=125) (actual time=231.209..30,960.204 rows=537 loops=1)

  • Output: t_xml_file_ers3_log.xmlf_cdn, t_xml_file_ers3_log.xmlf_flux_cdn, t_xml_file_ers3_log.xmlf_creation_dt, t_xml_file_ers3_log.xmlf_file_name_lb, ((xpath('//ers:OPS/@AD'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:OPS/@FR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, (((((xpath('/ers:OPS/@OD'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text || ' '::text) || ((xpath('/ers:OPS/@OT'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text) || ':00'::text), t_xml_file_ers3_log.xmlf_on_lb, ((xpath('//ers:ERS/ers:LOG/@IR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:LOG/@XR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:LOG/@FS'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:LOG/@NA'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:LOG/ers:ELOG/@TN'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, t_xml_file_ers3_log.xmlf_type_message, t_xml_file_ers3_log.ret_cdn
3. 8.627 17,932.142 ↑ 1.0 552 1

Nested Loop (cost=0.86..8,525.79 rows=579 width=133) (actual time=172.353..17,932.142 rows=552 loops=1)

  • Output: t_xml_file_ers3_log.xmlf_cdn, t_xml_file_ers3_log.xmlf_flux_cdn, t_xml_file_ers3_log.xmlf_creation_dt, t_xml_file_ers3_log.xmlf_file_name_lb, t_xml_file_ers3_log.xmlf_contenu_xml, t_xml_file_ers3_log.xmlf_on_lb, t_xml_file_ers3_log.xmlf_type_message, t_xml_file_ers3_log.ret_cdn, t_ops.ops_cdn
4. 4,750.502 4,750.502 ↑ 1.6 553 1

Index Scan using i_t_xml_file_ers3_log_xpath_cfr_journaux_bord on ers3.t_xml_file_ers3_log (cost=0.43..2,719.57 rows=901 width=125) (actual time=136.398..4,750.502 rows=553 loops=1)

  • Output: t_xml_file_ers3_log.xmlf_flux_cdn, t_xml_file_ers3_log.xmlf_cdn, t_xml_file_ers3_log.xmlf_file_name_lb, t_xml_file_ers3_log.xmlf_on_lb, t_xml_file_ers3_log.xmlf_creation_dt, t_xml_file_ers3_log.xmlf_contenu_txt, t_xml_file_ers3_log.xmlf_datesys_dt, t_xml_file_ers3_log.xmlf_creation_flux_dt, t_xml_file_ers3_log.ret_cdn, t_xml_file_ers3_log.xmlf_contenu_xml, t_xml_file_ers3_log.xmlf_json, t_xml_file_ers3_log.xmlf_type_message, t_xml_file_ers3_log.xmlf_active
  • Index Cond: (((xpath('//ers:ERS/ers:LOG/@IR'::text, t_xml_file_ers3_log.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text = 'FRA000926671'::text)
5. 13,173.013 13,173.013 ↑ 1.0 1 553

Index Scan using i_t_ops_xmlf_cdn on ers3.t_ops (cost=0.44..6.43 rows=1 width=16) (actual time=23.818..23.821 rows=1 loops=553)

  • Output: t_ops.ops_cdn, t_ops.opr_rfa, t_ops.ops_ad_lb, t_ops.ops_fr_lb, t_ops.ops_on_lb, t_ops.ops_od_dt, t_ops.ops_ot_lb, t_ops.ops_ts_lb, t_ops.ops_odot_dt, t_ops.ops_creation_dt, t_ops.ops_type_mes_lb, t_ops.fd_cdn, t_ops.ops_conversion_dt, t_ops.ops_eav_lb, t_ops.ops_evl_lb, t_ops.ops_version_ers_lb, t_ops.ops_act_deb_da_dt, t_ops.ops_act_fin_da_dt, t_ops.ops_act_deb_ti_lb, t_ops.ops_act_fin_ti_lb, t_ops.ops_act_deb_dati_dt, t_ops.ops_act_fin_dati_dt, t_ops.ret_cdn, t_ops.rsp_cdn, t_ops.xmlf_cdn
  • Index Cond: (t_ops.xmlf_cdn = t_xml_file_ers3_log.xmlf_cdn)
6. 12,424.968 12,424.968 ↑ 1.0 1 552

Index Scan using i_t_ers3_ops_cdn on ers3.t_ers3 (cost=0.43..4.57 rows=1 width=8) (actual time=22.505..22.509 rows=1 loops=552)

  • Output: t_ers3.ers_cdn, t_ers3.ers_rn_lb, t_ers3.ers_rd_dt, t_ers3.ers_rt_lb, t_ers3.ers_rdt_dt, t_ers3.ers_creation_dt, t_ers3.ers_modification_dt, t_ers3.ers_type_lb, t_ers3.ers_groupe_lb, t_ers3.ers_fr_rn_lb, t_ers3.ops_cdn
  • Index Cond: (t_ers3.ops_cdn = t_ops.ops_cdn)
  • Filter: (t_ers3.ers_modification_dt IS NULL)
Planning time : 1.472 ms
Execution time : 30,963.698 ms