explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aZNb

Settings
# exclusive inclusive rows x rows loops node
1. 0.131 152.681 ↓ 1.0 148 1

Limit (cost=14.64..6,258.23 rows=146 width=465) (actual time=1.581..152.681 rows=148 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, (replace((((t_xml_file_ers3_log.xmlf_json -> 'ers:OPS'::text) -> 'AD'::text))::text, '"'::text, ''::text)), (replace((((t_xml_file_ers3_log.xmlf_json -> 'ers:OPS'::text) -> 'FR'::text))::text, '"'::text, ''::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]): (...)
2. 147.961 152.550 ↓ 1.0 148 1

Nested Loop (cost=14.64..6,258.23 rows=146 width=465) (actual time=1.581..152.550 rows=148 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, replace((((t_xml_file_ers3_log.xmlf_json -> 'ers:OPS'::text) -> 'AD'::text))::text, '"'::text, ''::text), replace((((t_xml_file_ers3_log.xmlf_json -> 'ers:OPS'::text) -> 'FR'::text))::text, '"'::text, ''::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])::te (...)
3. 0.656 3.109 ↑ 1.9 148 1

Nested Loop (cost=14.21..5,249.37 rows=280 width=473) (actual time=0.157..3.109 rows=148 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_json, 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. 0.723 0.825 ↑ 3.8 148 1

Bitmap Heap Scan on ers3.t_xml_file_ers3_log (cost=13.78..1,631.50 rows=561 width=465) (actual time=0.144..0.825 rows=148 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.rn_groupe_fichier, t_xml_file_ers3_log.on_qui_invalide
  • Recheck 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 = 'FRA000612440'::text)
  • Heap Blocks: exact=138
5. 0.102 0.102 ↑ 3.8 148 1

Bitmap Index Scan on i_t_xml_file_ers3_log_xpath_cfr_journaux_bord (cost=0.00..13.63 rows=561 width=0) (actual time=0.102..0.102 rows=148 loops=1)

  • 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 = 'FRA000612440'::text)
6. 1.628 1.628 ↑ 1.0 1 148

Index Scan using i_t_ops_xmlf_cdn on ers3.t_ops (cost=0.43..6.44 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=148)

  • 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)
7. 1.480 1.480 ↑ 1.0 1 148

Index Scan using i_t_ers_ops_cdn on ers3.t_ers (cost=0.43..3.57 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=148)

  • Output: t_ers.ers_cdn, t_ers.ers_rn_lb, t_ers.ers_rd_dt, t_ers.ers_rt_lb, t_ers.ers_rdt_dt, t_ers.ers_creation_dt, t_ers.ers_modification_dt, t_ers.ers_type_lb, t_ers.ers_groupe_lb, t_ers.ers_fr_rn_lb, t_ers.ops_cdn
  • Index Cond: (t_ers.ops_cdn = t_ops.ops_cdn)
  • Filter: (t_ers.ers_modification_dt IS NULL)