explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tjZw

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 5,503.264 ↑ 1.0 50 1

Limit (cost=168,407.05..168,407.17 rows=50 width=1,678) (actual time=5,503.111..5,503.264 rows=50 loops=1)

2.          

CTE infra_col_data

3. 162.828 2,663.449 ↓ 1.4 82,452 1

Unique (cost=45,818.87..46,103.86 rows=56,997 width=174) (actual time=2,398.409..2,663.449 rows=82,452 loops=1)

4. 249.476 2,500.621 ↓ 1.5 84,266 1

Sort (cost=45,818.87..45,961.37 rows=56,997 width=174) (actual time=2,398.404..2,500.621 rows=84,266 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11,512kB
5. 174.197 2,251.145 ↓ 1.5 84,266 1

Hash Join (cost=22,294.46..36,444.51 rows=56,997 width=174) (actual time=1,126.607..2,251.145 rows=84,266 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
6. 200.185 1,956.376 ↓ 1.5 84,266 1

Hash Join (cost=20,815.01..34,181.36 rows=56,997 width=170) (actual time=1,005.987..1,956.376 rows=84,266 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
7. 451.109 1,575.199 ↓ 1.5 84,266 1

Hash Join (cost=17,630.66..27,207.30 rows=56,997 width=166) (actual time=824.818..1,575.199 rows=84,266 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_infra_instan = po_infra_instan.id_infra_instan)
8. 300.381 300.381 ↓ 1.0 274,531 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=0.014..300.381 rows=274,531 loops=1)

9. 110.952 823.709 ↓ 1.5 84,769 1

Hash (cost=15,580.81..15,580.81 rows=57,028 width=162) (actual time=823.708..823.709 rows=84,769 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 3,169kB
10. 351.571 712.757 ↓ 1.5 84,769 1

Hash Join (cost=20.85..15,580.81 rows=57,028 width=162) (actual time=1.815..712.757 rows=84,769 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
11. 360.944 360.944 ↓ 1.0 274,440 1

Seq Scan on po_infra_instan (cost=0.00..13,960.75 rows=274,382 width=162) (actual time=0.014..360.944 rows=274,440 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
12. 0.058 0.242 ↑ 1.0 53 1

Hash (cost=20.19..20.19 rows=53 width=4) (actual time=0.242..0.242 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
13. 0.184 0.184 ↑ 1.0 53 1

Seq Scan on po_connector_details (cost=0.00..20.19 rows=53 width=4) (actual time=0.015..0.184 rows=53 loops=1)

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
14. 90.965 180.992 ↓ 1.0 85,261 1

Hash (cost=1,784.60..1,784.60 rows=85,260 width=8) (actual time=180.992..180.992 rows=85,261 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,697kB
15. 90.027 90.027 ↓ 1.0 85,261 1

Seq Scan on po_project_sub (cost=0.00..1,784.60 rows=85,260 width=8) (actual time=0.016..90.027 rows=85,261 loops=1)

16. 30.155 120.572 ↓ 1.0 29,343 1

Hash (cost=1,112.67..1,112.67 rows=29,342 width=8) (actual time=120.572..120.572 rows=29,343 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,403kB
17. 59.279 90.417 ↓ 1.0 29,343 1

Hash Join (cost=10.79..1,112.67 rows=29,342 width=8) (actual time=0.654..90.417 rows=29,343 loops=1)

  • Hash Cond: (po_project.id_organization = org.id_organization)
18. 30.509 30.509 ↓ 1.0 29,343 1

Seq Scan on po_project (cost=0.00..698.42 rows=29,342 width=8) (actual time=0.016..30.509 rows=29,343 loops=1)

19. 0.300 0.629 ↓ 1.0 303 1

Hash (cost=7.02..7.02 rows=302 width=4) (actual time=0.629..0.629 rows=303 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
20. 0.329 0.329 ↓ 1.0 303 1

Seq Scan on po_organization org (cost=0.00..7.02 rows=302 width=4) (actual time=0.005..0.329 rows=303 loops=1)

21. 2.049 5,503.158 ↑ 73.4 50 1

Sort (cost=122,303.19..122,312.36 rows=3,669 width=1,678) (actual time=5,503.108..5,503.158 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 39kB
22. 240.320 5,501.109 ↑ 3.4 1,066 1

Merge Join (cost=121,810.45..122,181.31 rows=3,669 width=1,678) (actual time=5,037.954..5,501.109 rows=1,066 loops=1)

  • Merge Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
23. 190.725 3,040.355 ↑ 1.6 35,320 1

Sort (cost=83,573.30..83,715.80 rows=56,997 width=1,576) (actual time=3,001.009..3,040.355 rows=35,320 loops=1)

  • Sort Key: infra_col_data.id_infra_instan
  • Sort Method: external sort Disk: 10,880kB
24. 2,849.630 2,849.630 ↓ 1.4 82,452 1

CTE Scan on infra_col_data (cost=0.00..1,139.94 rows=56,997 width=1,576) (actual time=2,398.414..2,849.630 rows=82,452 loops=1)

25. 730.268 2,220.434 ↓ 22.3 219,157 1

Sort (cost=38,237.14..38,261.74 rows=9,837 width=106) (actual time=1,982.020..2,220.434 rows=219,157 loops=1)

  • Sort Key: _req.id_infra_instan
  • Sort Method: external sort Disk: 35,296kB
26. 1,446.851 1,490.166 ↓ 22.3 219,157 1

Bitmap Heap Scan on po_import_request_exact_copy _req (cost=307.55..37,584.75 rows=9,837 width=106) (actual time=48.246..1,490.166 rows=219,157 loops=1)

  • Recheck Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
  • Rows Removed by Index Recheck: 2,392,939
  • Heap Blocks: exact=25,952 lossy=54,651
27. 43.315 43.315 ↓ 22.3 219,157 1

Bitmap Index Scan on idx_po_import_request_exact_copy_extraction_ts_update_id_infra (cost=0.00..305.09 rows=9,837 width=0) (actual time=43.313..43.315 rows=219,157 loops=1)

  • Index Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
Planning time : 1.461 ms
Execution time : 5,523.334 ms