explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1qN

Settings
# exclusive inclusive rows x rows loops node
1. 0.499 725.427 ↑ 1.0 1,000 1

Limit (cost=838.89..34,413.61 rows=1,000 width=465) (actual time=19.003..725.427 rows=1,000 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. 598.956 724.928 ↑ 11.5 1,000 1

Nested Loop (cost=838.89..387,888.26 rows=11,528 width=465) (actual time=19.001..724.928 rows=1,000 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. 1.698 121.912 ↑ 21.8 1,015 1

Nested Loop (cost=838.46..308,173.85 rows=22,124 width=473) (actual time=15.197..121.912 rows=1,015 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. 103.742 114.124 ↑ 43.6 1,015 1

Bitmap Heap Scan on ers3.t_xml_file_ers3_log (cost=838.02..77,525.55 rows=44,263 width=465) (actual time=15.176..114.124 rows=1,015 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: ((t_xml_file_ers3_log.xmlf_creation_dt >= '2018-02-01 00:30:47.93+01'::timestamp with time zone) AND (t_xml_file_ers3_log.xmlf_creation_dt <= '2018-03-01 00:30:47.93+01'::timestamp with time zone))
  • Filter: (((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 = 'FRA'::text)
  • Rows Removed by Filter: 35
  • Heap Blocks: exact=275
5. 10.382 10.382 ↓ 1.0 46,013 1

Bitmap Index Scan on i_t_xml_file_ers3_log_creation_dt (cost=0.00..826.95 rows=45,153 width=0) (actual time=10.382..10.382 rows=46,013 loops=1)

  • Index Cond: ((t_xml_file_ers3_log.xmlf_creation_dt >= '2018-02-01 00:30:47.93+01'::timestamp with time zone) AND (t_xml_file_ers3_log.xmlf_creation_dt <= '2018-03-01 00:30:47.93+01'::timestamp with time zone))
6. 6.090 6.090 ↑ 1.0 1 1,015

Index Scan using i_t_ops_xmlf_cdn on ers3.t_ops (cost=0.43..5.20 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=1,015)

  • 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. 4.060 4.060 ↑ 1.0 1 1,015

Index Scan using i_t_ers_ops_cdn on ers3.t_ers (cost=0.43..3.57 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1,015)

  • 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)