explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cSk8 : Expertise Philippe

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.079 4,119.262 ↑ 1.0 100 1

Limit (cost=160,456.46..160,458.46 rows=100 width=572) (actual time=4,119.104..4,119.262 rows=100 loops=1)

2. 9.315 4,119.183 ↑ 8.4 100 1

HashAggregate (cost=160,456.46..160,464.89 rows=843 width=279) (actual time=4,119.101..4,119.183 rows=100 loops=1)

  • Group Key: ('ERS3'::text), (concat('xmlf_'::text, t_xml_file_ers3_log.xmlf_cdn)), (CASE WHEN (t_xml_file_ers3_log.ret_cdn IS NULL) THEN 'MANQ'::text WHEN ((t_ret.ret_rs_lb)::text ~~ '000'::text) THEN 'ACQT'::text WHEN ((t_ret.ret_rs_lb)::text <> '000'::text) THEN 'NACQT'::text ELSE NULL::text END), t_ret.ret_cdn, (NULL::integer), t_ops.ops_odot_dt, ("left"((t_xml_file_ers3_log.xmlf_on_lb)::text, 3)), t_xml_file_ers3_log.xmlf_type_message, (((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), t_xml_file_ers3_log.xmlf_creation_dt, ('FA'::text), t_ops.ops_fr_lb, t_ops.ops_ad_lb, t_ops.ops_version_ers_lb, t_xml_file_ers3_log.xmlf_file_name_lb, t_xml_file_ers3_log.xmlf_on_lb, (NULL::text), (NULL::text), (NULL::text), t_xml_file_ers3_log.xmlf_contenu_txt
3. 1.003 4,109.868 ↑ 1.7 496 1

Append (cost=153.13..160,412.20 rows=843 width=279) (actual time=3.581..4,109.868 rows=496 loops=1)

4. 5.450 9.072 ↓ 2.1 15 1

Nested Loop Left Join (cost=153.13..263.92 rows=7 width=775) (actual time=3.578..9.072 rows=15 loops=1)

5. 0.217 3.367 ↓ 2.1 15 1

Nested Loop Left Join (cost=152.70..218.58 rows=7 width=763) (actual time=2.772..3.367 rows=15 loops=1)

6. 0.115 2.850 ↓ 2.1 15 1

Bitmap Heap Scan on t_xml_file_ers3_log (cost=152.26..173.32 rows=7 width=744) (actual time=2.748..2.850 rows=15 loops=1)

  • Recheck Cond: ((((xpath('//ers:ERS/ers:LOG/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text = 'FRA000900290'::text) AND (xmlf_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlf_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone))
  • Heap Blocks: exact=14
7. 0.198 2.735 ↓ 0.0 0 1

BitmapAnd (cost=152.26..152.26 rows=7 width=0) (actual time=2.735..2.735 rows=0 loops=1)

8. 0.549 0.549 ↓ 1.5 1,320 1

Bitmap Index Scan on i_t_xml_file_ers3_log_xpath_cfr_journaux_bord (cost=0.00..19.20 rows=903 width=0) (actual time=0.549..0.549 rows=1,320 loops=1)

  • Index Cond: (((xpath('//ers:ERS/ers:LOG/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text = 'FRA000900290'::text)
9. 1.988 1.988 ↓ 1.1 8,115 1

Bitmap Index Scan on i_t_xml_file_ers3_log_creation_dt (cost=0.00..132.81 rows=7,238 width=0) (actual time=1.988..1.988 rows=8,115 loops=1)

  • Index Cond: ((xmlf_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlf_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone))
10. 0.300 0.300 ↑ 1.0 1 15

Index Scan using i_t_ops_xmlf_cdn on t_ops (cost=0.44..6.46 rows=1 width=27) (actual time=0.018..0.020 rows=1 loops=15)

  • Index Cond: (xmlf_cdn = t_xml_file_ers3_log.xmlf_cdn)
11. 0.255 0.255 ↑ 1.0 1 15

Index Scan using pk_t_ret on t_ret (cost=0.43..6.45 rows=1 width=12) (actual time=0.014..0.017 rows=1 loops=15)

  • Index Cond: (ret_cdn = t_xml_file_ers3_log.ret_cdn)
12. 85.340 133.247 ↑ 1.1 477 1

Nested Loop Left Join (cost=2,729.42..10,773.28 rows=506 width=163) (actual time=39.506..133.247 rows=477 loops=1)

13. 2.412 45.045 ↑ 1.1 477 1

Nested Loop Left Join (cost=2,728.98..7,507.67 rows=506 width=151) (actual time=38.945..45.045 rows=477 loops=1)

14. 0.963 39.771 ↑ 1.1 477 1

Bitmap Heap Scan on t_xml_file_ers3_sal (cost=2,728.55..4,242.38 rows=506 width=132) (actual time=38.903..39.771 rows=477 loops=1)

  • Recheck Cond: ((((xpath('//ers:ERS/ers:SAL/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text = 'FRA000900290'::text) AND (xmlf_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlf_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone))
  • Heap Blocks: exact=124
15. 4.095 38.808 ↓ 0.0 0 1

BitmapAnd (cost=2,728.55..2,728.55 rows=506 width=0) (actual time=38.808..38.808 rows=0 loops=1)

16. 19.847 19.847 ↓ 1.0 62,983 1

Bitmap Index Scan on i_t_xml_file_ers3_sal_xpath_cfr (cost=0.00..1,172.42 rows=61,464 width=0) (actual time=19.847..19.847 rows=62,983 loops=1)

  • Index Cond: (((xpath('//ers:ERS/ers:SAL/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text = 'FRA000900290'::text)
17. 14.866 14.866 ↑ 1.4 59,743 1

Bitmap Index Scan on i_t_xml_file_ers3_sal_creation_dt (cost=0.00..1,555.62 rows=85,319 width=0) (actual time=14.866..14.866 rows=59,743 loops=1)

  • Index Cond: ((xmlf_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlf_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone))
18. 2.862 2.862 ↑ 1.0 1 477

Index Scan using i_t_ops_xmlf_cdn on t_ops t_ops_1 (cost=0.44..6.44 rows=1 width=27) (actual time=0.005..0.006 rows=1 loops=477)

  • Index Cond: (xmlf_cdn = t_xml_file_ers3_sal.xmlf_cdn)
19. 2.862 2.862 ↑ 1.0 1 477

Index Scan using pk_t_ret on t_ret t_ret_1 (cost=0.43..6.43 rows=1 width=12) (actual time=0.004..0.006 rows=1 loops=477)

  • Index Cond: (ret_cdn = t_xml_file_ers3_sal.ret_cdn)
20. 4.930 3,966.063 ↑ 82.2 4 1

Nested Loop Left Join (cost=1,203.01..149,166.37 rows=329 width=447) (actual time=546.160..3,966.063 rows=4 loops=1)

21. 0.033 3,961.129 ↑ 82.2 4 1

Nested Loop Left Join (cost=1,202.58..147,031.69 rows=329 width=435) (actual time=544.992..3,961.129 rows=4 loops=1)

22. 3,950.628 3,961.048 ↑ 82.2 4 1

Bitmap Heap Scan on t_xml_file_ers3_autre (cost=1,202.14..144,904.71 rows=329 width=416) (actual time=544.971..3,961.048 rows=4 loops=1)

  • Recheck Cond: ((xmlf_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlf_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone))
  • Filter: (CASE WHEN xpath_exists('//ers:ERS/ers:LOG/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]) THEN ((xpath('//ers:ERS/ers:LOG/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text WHEN xpath_exists('//ers:ERS/ers:SAL/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]) THEN ((xpath('//ers:ERS/ers:SAL/@IR'::text, xmlf_contenu_xml, '{{ers,http://ec.europa.eu/fisheries/schema/ers/v3}}'::text[]))[1])::text ELSE NULL::text END = 'FRA000900290'::text)
  • Rows Removed by Filter: 67828
  • Heap Blocks: exact=8845
23. 10.420 10.420 ↓ 1.0 67,832 1

Bitmap Index Scan on i_t_xml_file_ers3_autre_creation_dt (cost=0.00..1,202.06 rows=65,863 width=0) (actual time=10.420..10.420 rows=67,832 loops=1)

  • Index Cond: ((xmlf_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlf_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone))
24. 0.048 0.048 ↓ 0.0 0 4

Index Scan using i_t_ops_xmlf_cdn on t_ops t_ops_2 (cost=0.44..6.46 rows=1 width=27) (actual time=0.012..0.012 rows=0 loops=4)

  • Index Cond: (xmlf_cdn = t_xml_file_ers3_autre.xmlf_cdn)
25. 0.004 0.004 ↓ 0.0 0 4

Index Scan using pk_t_ret on t_ret t_ret_2 (cost=0.43..6.44 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: (ret_cdn = t_xml_file_ers3_autre.ret_cdn)
26. 0.002 0.483 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..200.21 rows=1 width=475) (actual time=0.483..0.483 rows=0 loops=1)

27. 0.481 0.481 ↓ 0.0 0 1

Seq Scan on t_xml_file_uncefact (cost=0.00..200.20 rows=1 width=475) (actual time=0.481..0.481 rows=0 loops=1)

  • Filter: ((xmlfu_creation_dt >= '2019-02-02 20:13:00.896+01'::timestamp with time zone) AND (xmlfu_creation_dt <= '2019-02-06 20:13:00.896+01'::timestamp with time zone) AND (((xpath('//ram:RelatedVesselTransportMeans/ram:ID[@schemeID="CFR"]/text()'::text, xmlfu_contenu_xml, '{{ram,urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:20}}'::text[]))[1])::text = 'FRA000900290'::text))
  • Rows Removed by Filter: 1258
Planning time : 3.829 ms
Execution time : 4,119.837 ms