explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XRgd

Settings
# exclusive inclusive rows x rows loops node
1. 0.129 3,759.275 ↓ 1.2 50 1

Limit (cost=120,142.04..120,142.15 rows=43 width=1,699) (actual time=3,759.097..3,759.275 rows=50 loops=1)

2.          

CTE infra_col_data

3. 175.909 2,638.038 ↓ 6.2 82,454 1

Unique (cost=27,189.62..27,255.95 rows=13,267 width=174) (actual time=2,356.114..2,638.038 rows=82,454 loops=1)

4. 232.647 2,462.129 ↓ 6.4 84,268 1

Sort (cost=27,189.62..27,222.79 rows=13,267 width=174) (actual time=2,356.108..2,462.129 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: quicksort Memory: 20177kB
5. 187.592 2,229.482 ↓ 6.4 84,268 1

Hash Join (cost=19,837.78..26,281.12 rows=13,267 width=174) (actual time=1,297.285..2,229.482 rows=84,268 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
6. 449.078 1,686.080 ↓ 1.5 84,268 1

Hash Join (cost=16,299.65..22,395.69 rows=57,236 width=166) (actual time=941.312..1,686.080 rows=84,268 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_infra_instan = po_infra_instan.id_infra_instan)
7. 296.376 296.376 ↓ 1.0 274,533 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=0.020..296.376 rows=274,533 loops=1)

8. 122.746 940.626 ↓ 1.5 84,771 1

Hash (cost=15,583.75..15,583.75 rows=57,272 width=162) (actual time=940.625..940.626 rows=84,771 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12693kB
9. 386.796 817.880 ↓ 1.5 84,771 1

Hash Join (cost=20.84..15,583.75 rows=57,272 width=162) (actual time=0.471..817.880 rows=84,771 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
10. 430.710 430.710 ↑ 1.0 274,442 1

Seq Scan on po_infra_instan (cost=0.00..13,960.92 rows=274,472 width=162) (actual time=0.018..430.710 rows=274,442 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
11. 0.083 0.374 ↑ 1.0 53 1

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=0.373..0.374 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.291 0.291 ↑ 1.0 53 1

Seq Scan on po_connector_details (cost=0.00..20.18 rows=53 width=4) (actual time=0.026..0.291 rows=53 loops=1)

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
13. 12.784 355.810 ↑ 2.1 9,532 1

Hash (cost=3,291.11..3,291.11 rows=19,762 width=12) (actual time=355.809..355.810 rows=9,532 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 666kB
14. 114.604 343.026 ↑ 2.1 9,532 1

Hash Join (cost=989.16..3,291.11 rows=19,762 width=12) (actual time=116.894..343.026 rows=9,532 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
15. 111.629 111.629 ↓ 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.022..111.629 rows=85,261 loops=1)

16. 18.226 116.793 ↓ 1.8 12,498 1

Hash (cost=904.15..904.15 rows=6,801 width=8) (actual time=116.792..116.793 rows=12,498 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
17. 57.671 98.567 ↓ 1.8 12,498 1

Hash Join (cost=27.69..904.15 rows=6,801 width=8) (actual time=1.192..98.567 rows=12,498 loops=1)

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

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

19. 0.109 0.333 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.332..0.333 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.224 0.224 ↑ 1.0 70 1

Index Only Scan using po_organization_pkey on po_organization org (cost=0.15..26.81 rows=70 width=4) (actual time=0.026..0.224 rows=70 loops=1)

  • Index Cond: (id_organization = ANY ('{126,133,190,191,192,193,194,195,196,197,198,199,203,204,211,272,287,288,289,290,291,292,293,331,333,337,338,339,340,341,342,347,349,350,351,352,353,354,355,356,361,362,363,366,367,368,369,373,374,375,376,377,378,379,380,381,382,383,384,385,386,396,397,398,399,400,401,402,403,424}'::integer[]))
  • Heap Fetches: 70
21. 2.047 3,759.146 ↓ 1.2 50 1

Sort (cost=92,886.09..92,886.20 rows=43 width=1,699) (actual time=3,759.089..3,759.146 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 38kB
22. 56.286 3,757.099 ↓ 22.8 980 1

Hash Join (cost=431.18..92,884.92 rows=43 width=1,699) (actual time=3,512.358..3,757.099 rows=980 loops=1)

  • Hash Cond: (_req.id_infra_instan = infra_col_data.id_infra_instan)
23. 98.354 761.339 ↓ 114.7 49,989 1

Append (cost=0.00..91,729.01 rows=436 width=127) (actual time=572.658..761.339 rows=49,989 loops=1)

24. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on po_import_request_ext_copy _req (cost=0.00..0.00 rows=1 width=106) (actual time=0.004..0.006 rows=0 loops=1)

  • Filter: (((ts_status_last_updated_at)::date >= '2018-12-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[])))
25. 91.405 662.979 ↓ 114.9 49,989 1

Bitmap Heap Scan on po_import_request_ext_copy_all _req_1 (cost=90,063.76..91,729.01 rows=435 width=127) (actual time=572.650..662.979 rows=49,989 loops=1)

  • Recheck Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
  • Heap Blocks: exact=3994
26. 19.164 571.574 ↓ 0.0 0 1

BitmapAnd (cost=90,063.76..90,063.76 rows=435 width=0) (actual time=571.572..571.574 rows=0 loops=1)

27. 297.460 297.460 ↓ 40.0 3,481,984 1

Bitmap Index Scan on idx_po_import_request_ext_copy_all_nu_extraction_status (cost=0.00..1,607.03 rows=87,050 width=0) (actual time=297.458..297.460 rows=3,481,984 loops=1)

  • Index Cond: ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[]))
28. 254.950 254.950 ↓ 2.9 49,989 1

Bitmap Index Scan on idx_po_import_request_ext_copy_all_ts_status_last_updated_at_in (cost=0.00..88,456.27 rows=17,410 width=0) (actual time=254.948..254.950 rows=49,989 loops=1)

  • Index Cond: (((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
29. 114.623 2,939.474 ↓ 6.2 82,454 1

Hash (cost=265.34..265.34 rows=13,267 width=1,576) (actual time=2,939.472..2,939.474 rows=82,454 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 13659kB
30. 2,824.851 2,824.851 ↓ 6.2 82,454 1

CTE Scan on infra_col_data (cost=0.00..265.34 rows=13,267 width=1,576) (actual time=2,356.121..2,824.851 rows=82,454 loops=1)

Planning time : 37.823 ms
Execution time : 3,763.343 ms