explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dw33

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 170,617.796 ↑ 1.0 500 1

Limit (cost=508,242.51..508,243.76 rows=500 width=520) (actual time=170,617.605..170,617.796 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. 931.501 170,617.690 ↑ 70.9 500 1

Sort (cost=508,242.51..508,331.12 rows=35,443 width=520) (actual time=170,617.603..170,617.690 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))
  • Sort Key: ((((((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))
  • Sort Method: top-N heapsort Memory: 1000kB
3. 111,177.831 169,686.189 ↓ 5.8 207,220 1

Nested Loop (cost=571.06..506,476.42 rows=35,443 width=520) (actual time=67.738..169,686.189 rows=207,220 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)
4. 574.522 24,109.838 ↓ 5.8 207,220 1

Nested Loop (cost=570.63..288,466.59 rows=35,443 width=442) (actual time=66.771..24,109.838 rows=207,220 loops=1)

  • Output: t_json_sal_xli.json_xli, t_json_sal_xli.xmlf_flux_cdn, t_json_sal_spe.json_xli_css_spe
5. 6,537.255 6,599.320 ↓ 7.2 256,606 1

Bitmap Heap Scan on ers3.t_json_sal_xli (cost=570.20..78,578.92 rows=35,582 width=280) (actual time=66.738..6,599.320 rows=256,606 loops=1)

  • Output: t_json_sal_xli.t_json_sal_xli_cdn, t_json_sal_xli.json_xli, t_json_sal_xli.xmlf_flux_cdn
  • Recheck Cond: ((t_json_sal_xli.json_xli ->> 'portDeVente_XLI_SL'::text) = 'FRQUY'::text)
  • Heap Blocks: exact=11334
6. 62.065 62.065 ↓ 7.2 256,606 1

Bitmap Index Scan on i_t_json_sal_xli_portdevente_xli_sl (cost=0.00..561.30 rows=35,582 width=0) (actual time=62.065..62.065 rows=256,606 loops=1)

  • Index Cond: ((t_json_sal_xli.json_xli ->> 'portDeVente_XLI_SL'::text) = 'FRQUY'::text)
7. 16,935.996 16,935.996 ↑ 1.0 1 256,606

Index Scan using i_t_json_sal_spe_sal_xli_cdn on ers3.t_json_sal_spe (cost=0.43..5.89 rows=1 width=170) (actual time=0.060..0.066 rows=1 loops=256,606)

  • 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)
8. 34,398.520 34,398.520 ↑ 1.0 1 207,220

Index Scan using pk_t_xml_file_ers3_sal on ers3.t_xml_file_ers3_sal (cost=0.43..6.09 rows=1 width=82) (actual time=0.156..0.166 rows=1 loops=207,220)

  • 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
  • Index Cond: (t_xml_file_ers3_sal.xmlf_flux_cdn = t_json_sal_xli.xmlf_flux_cdn)