explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aznv

Settings
# exclusive inclusive rows x rows loops node
1. 0.108 3,003.898 ↓ 1.3 50 1

Limit (cost=243,345.08..243,345.17 rows=38 width=1,679) (actual time=3,003.745..3,003.898 rows=50 loops=1)

2.          

CTE infra_col_data

3. 31.971 1,100.519 ↓ 1.5 16,614 1

Unique (cost=26,068.07..26,121.84 rows=10,754 width=174) (actual time=1,049.324..1,100.519 rows=16,614 loops=1)

4. 54.114 1,068.548 ↓ 1.5 16,614 1

Sort (cost=26,068.07..26,094.96 rows=10,754 width=174) (actual time=1,049.321..1,068.548 rows=16,614 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external sort Disk: 3000kB
5. 35.063 1,014.434 ↓ 1.5 16,614 1

Hash Join (cost=1,096.57..25,347.95 rows=10,754 width=174) (actual time=61.225..1,014.434 rows=16,614 loops=1)

  • Hash Cond: (po_project.id_organization = org.id_organization)
6. 35.838 978.758 ↓ 1.5 16,614 1

Hash Join (cost=1,085.77..25,189.29 rows=10,754 width=174) (actual time=60.606..978.758 rows=16,614 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
7. 70.638 883.306 ↓ 1.5 16,614 1

Nested Loop (cost=20.58..23,976.23 rows=10,754 width=170) (actual time=0.923..883.306 rows=16,614 loops=1)

8. 62.012 762.826 ↓ 1.5 16,614 1

Nested Loop (cost=20.29..20,352.08 rows=10,754 width=166) (actual time=0.913..762.826 rows=16,614 loops=1)

9. 278.551 634.358 ↓ 1.5 16,614 1

Hash Join (cost=19.86..15,117.15 rows=10,760 width=162) (actual time=0.898..634.358 rows=16,614 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
10. 355.759 355.759 ↓ 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.007..355.759 rows=274,440 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
11. 0.012 0.048 ↑ 1.0 10 1

Hash (cost=19.74..19.74 rows=10 width=4) (actual time=0.047..0.048 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.025 0.036 ↑ 1.0 10 1

Bitmap Heap Scan on po_connector_details (cost=4.08..19.74 rows=10 width=4) (actual time=0.017..0.036 rows=10 loops=1)

  • Recheck Cond: (id_enterprise = 105)
  • Heap Blocks: exact=5
13. 0.011 0.011 ↑ 1.0 10 1

Bitmap Index Scan on idx_po_connector_details_id_enterprise (cost=0.00..4.08 rows=10 width=0) (actual time=0.010..0.011 rows=10 loops=1)

  • Index Cond: (id_enterprise = 105)
14. 66.456 66.456 ↑ 1.0 1 16,614

Index Scan using index_po_proj_sub_infra_id_infra_instan on po_proj_sub_infra (cost=0.42..0.48 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=16,614)

  • Index Cond: (id_infra_instan = po_infra_instan.id_infra_instan)
15. 49.842 49.842 ↑ 1.0 1 16,614

Index Scan using po_project_sub_id_project_sub_key on po_project_sub (cost=0.29..0.33 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=16,614)

  • Index Cond: (id_project_sub = po_proj_sub_infra.id_project_sub)
16. 29.589 59.614 ↓ 1.0 29,343 1

Hash (cost=698.42..698.42 rows=29,342 width=8) (actual time=59.613..59.614 rows=29,343 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1403kB
17. 30.025 30.025 ↓ 1.0 29,343 1

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

18. 0.295 0.613 ↓ 1.0 303 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
19. 0.318 0.318 ↓ 1.0 303 1

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

20. 2.014 3,003.790 ↓ 1.3 50 1

Sort (cost=217,223.24..217,223.33 rows=38 width=1,679) (actual time=3,003.741..3,003.790 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 50kB
21. 17.563 3,001.776 ↓ 29.4 1,116 1

Hash Join (cost=216,845.47..217,222.24 rows=38 width=1,679) (actual time=2,894.711..3,001.776 rows=1,116 loops=1)

  • Hash Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
22. 1,138.938 1,138.938 ↓ 1.5 16,614 1

CTE Scan on infra_col_data (cost=0.00..215.08 rows=10,754 width=1,576) (actual time=1,049.327..1,138.938 rows=16,614 loops=1)

23. 1.622 1,845.275 ↓ 2.2 1,116 1

Hash (cost=216,839.09..216,839.09 rows=510 width=107) (actual time=1,845.274..1,845.275 rows=1,116 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 220kB
24. 2.124 1,843.653 ↓ 2.2 1,116 1

Append (cost=0.00..216,839.09 rows=510 width=107) (actual time=133.864..1,843.653 rows=1,116 loops=1)

25. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on po_import_request_copy _req (cost=0.00..0.00 rows=1 width=107) (actual time=0.005..0.007 rows=0 loops=1)

  • Filter: ((id_enterprise = 105) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
26. 1,841.522 1,841.522 ↓ 2.2 1,116 1

Seq Scan on po_import_request_105 _req_1 (cost=0.00..216,839.09 rows=509 width=107) (actual time=133.853..1,841.522 rows=1,116 loops=1)

  • Filter: ((id_enterprise = 105) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
  • Rows Removed by Filter: 4069687
Planning time : 3.806 ms
Execution time : 3,005.609 ms