explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qh03

Settings
# exclusive inclusive rows x rows loops node
1. 0.573 312,501.556 ↑ 1.0 500 1

Limit (cost=0.86..33,147.25 rows=500 width=520) (actual time=102.470..312,501.556 rows=500 loops=1)

  • Output: t_xml_file_ers3_sal.xmlf_cdn, ((t_json_sal_spe.json_xli_css_spe ->> 'espece_SPE_SN'::text)), ((t_json_sal_spe.json_xli_css_spe ->> 'espece_SPE_WT'::text)), t_xml_file_ers3_sal.xmlf_flux_cdn, t_xml_file_ers3_sal.xmlf_file_name_lb, ((((((xpath('/ers:OPS/@OD'::text, t_xml_file_ers3_sal.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_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text) || ':00'::text)), (((xpath('/ers:OPS/@ON'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:SAL/@IR'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:SAL/@XR'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:SAL/@FS'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), (((xpath('//ers:ERS/ers:SAL/@NA'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text), t_xml_file_ers3_sal.xmlf_type_message, t_xml_file_ers3_sal.xmlf_contenu_xml, t_xml_file_ers3_sal.ret_cdn, ((t_json_sal_xli.json_xli ->> 'type_SLI_TLI'::text)), ((t_json_sal_xli.json_xli ->> 'numeroMaree_ESPE_MR'::text)), ((t_json_sal_xli.json_xli ->> 'portDeVente_XLI_SL'::text)), ((t_json_sal_xli.json_xli ->> 'portDeDebarquement_SRC_PO'::text)), ((t_json_sal_xli.json_xli ->> 'dateDeDebarquement_SRC_DL'::text)), ((t_json_sal_xli.json_xli ->> 'dateDeVente_XLI_DA'::text)), ((t_json_sal_xli.json_xli ->> 'heureDeVente_ESLI_TI'::text))
2. 389.747 312,500.983 ↑ 70.9 500 1

Nested Loop (cost=0.86..2,349,549.34 rows=35,442 width=520) (actual time=102.467..312,500.983 rows=500 loops=1)

  • Output: t_xml_file_ers3_sal.xmlf_cdn, (t_json_sal_spe.json_xli_css_spe ->> 'espece_SPE_SN'::text), (t_json_sal_spe.json_xli_css_spe ->> 'espece_SPE_WT'::text), t_xml_file_ers3_sal.xmlf_flux_cdn, t_xml_file_ers3_sal.xmlf_file_name_lb, (((((xpath('/ers:OPS/@OD'::text, t_xml_file_ers3_sal.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_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text) || ':00'::text), ((xpath('/ers:OPS/@ON'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:SAL/@IR'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:SAL/@XR'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:SAL/@FS'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, ((xpath('//ers:ERS/ers:SAL/@NA'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text, t_xml_file_ers3_sal.xmlf_type_message, t_xml_file_ers3_sal.xmlf_contenu_xml, t_xml_file_ers3_sal.ret_cdn, (t_json_sal_xli.json_xli ->> 'type_SLI_TLI'::text), (t_json_sal_xli.json_xli ->> 'numeroMaree_ESPE_MR'::text), (t_json_sal_xli.json_xli ->> 'portDeVente_XLI_SL'::text), (t_json_sal_xli.json_xli ->> 'portDeDebarquement_SRC_PO'::text), (t_json_sal_xli.json_xli ->> 'dateDeDebarquement_SRC_DL'::text), (t_json_sal_xli.json_xli ->> 'dateDeVente_XLI_DA'::text), (t_json_sal_xli.json_xli ->> 'heureDeVente_ESLI_TI'::text)
3. 2.284 307,785.736 ↑ 71.2 500 1

Nested Loop (cost=0.43..2,142,212.97 rows=35,581 width=358) (actual time=29.402..307,785.736 rows=500 loops=1)

  • Output: t_xml_file_ers3_sal.xmlf_cdn, t_xml_file_ers3_sal.xmlf_flux_cdn, t_xml_file_ers3_sal.xmlf_file_name_lb, t_xml_file_ers3_sal.xmlf_contenu_xml, t_xml_file_ers3_sal.xmlf_type_message, t_xml_file_ers3_sal.ret_cdn, t_json_sal_xli.json_xli, t_json_sal_xli.t_json_sal_xli_cdn
4. 304,417.452 304,417.452 ↑ 72.7 500 1

Seq Scan on ers3.t_xml_file_ers3_sal (cost=0.00..1,921,317.39 rows=36,359 width=82) (actual time=0.338..304,417.452 rows=500 loops=1)

  • Output: t_xml_file_ers3_sal.xmlf_flux_cdn, t_xml_file_ers3_sal.xmlf_cdn, t_xml_file_ers3_sal.xmlf_file_name_lb, t_xml_file_ers3_sal.xmlf_on_lb, t_xml_file_ers3_sal.xmlf_creation_dt, t_xml_file_ers3_sal.xmlf_contenu_txt, t_xml_file_ers3_sal.xmlf_datesys_dt, t_xml_file_ers3_sal.xmlf_creation_flux_dt, t_xml_file_ers3_sal.ret_cdn, t_xml_file_ers3_sal.xmlf_contenu_xml, t_xml_file_ers3_sal.xmlf_json, t_xml_file_ers3_sal.xmlf_type_message, t_xml_file_ers3_sal.xmlf_active
  • Filter: (((xpath('//ers:ERS/ers:SAL/@NA'::text, t_xml_file_ers3_sal.xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text = 'SPES'::text)
  • Rows Removed by Filter: 1665938
5. 3,366.000 3,366.000 ↑ 4.0 1 500

Index Scan using i_t_json_sal_xli_xmlf_flux_cdn on ers3.t_json_sal_xli (cost=0.43..6.04 rows=4 width=280) (actual time=6.731..6.732 rows=1 loops=500)

  • Output: t_json_sal_xli.t_json_sal_xli_cdn, t_json_sal_xli.json_xli, t_json_sal_xli.xmlf_flux_cdn
  • Index Cond: (t_json_sal_xli.xmlf_flux_cdn = t_xml_file_ers3_sal.xmlf_flux_cdn)
6. 4,325.500 4,325.500 ↑ 1.0 1 500

Index Scan using i_t_json_sal_spe_sal_xli_cdn on ers3.t_json_sal_spe (cost=0.43..5.77 rows=1 width=170) (actual time=8.648..8.651 rows=1 loops=500)

  • Output: t_json_sal_spe.t_json_sal_spe_cdn, t_json_sal_spe.json_xli_css_spe, t_json_sal_spe.xmlf_flux_cdn, t_json_sal_spe.t_json_sal_xli_cdn
  • Index Cond: (t_json_sal_spe.t_json_sal_xli_cdn = t_json_sal_xli.t_json_sal_xli_cdn)