explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bd1N

Settings
# exclusive inclusive rows x rows loops node
1. 0.163 9,692.775 ↑ 1.0 50 1

Limit (cost=351,931.91..351,932.04 rows=50 width=1,680) (actual time=9,692.539..9,692.775 rows=50 loops=1)

2.          

CTE infra_col_data

3. 189.949 3,926.996 ↓ 6.2 82,454 1

Unique (cost=28,049.08..28,115.42 rows=13,267 width=174) (actual time=3,620.367..3,926.996 rows=82,454 loops=1)

4. 366.914 3,737.047 ↓ 6.4 84,268 1

Sort (cost=28,049.08..28,082.25 rows=13,267 width=174) (actual time=3,620.362..3,737.047 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11520kB
5. 315.537 3,370.133 ↓ 6.4 84,268 1

Hash Join (cost=10,514.34..27,140.59 rows=13,267 width=174) (actual time=1,855.288..3,370.133 rows=84,268 loops=1)

  • Hash Cond: (po_infra_instan.id_infra_instan = po_proj_sub_infra.id_infra_instan)
6. 435.554 1,322.714 ↓ 1.5 84,771 1

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

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

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

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
8. 0.085 73.499 ↑ 1.0 53 1

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=73.498..73.499 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 73.414 73.414 ↑ 1.0 53 1

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

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
10. 158.509 1,731.882 ↓ 1.3 84,697 1

Hash (cost=9,698.11..9,698.11 rows=63,631 width=16) (actual time=1,731.881..1,731.882 rows=84,697 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
11. 530.097 1,573.373 ↓ 1.3 84,697 1

Hash Join (cost=3,538.13..9,698.11 rows=63,631 width=16) (actual time=569.644..1,573.373 rows=84,697 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
12. 475.880 475.880 ↓ 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=1.890..475.880 rows=274,533 loops=1)

13. 16.159 567.396 ↑ 2.1 9,532 1

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

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

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

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
15. 180.524 180.524 ↓ 1.0 85,261 1

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

16. 13.822 224.754 ↓ 1.8 12,498 1

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

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

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

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

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

19. 0.124 0.273 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.272..0.273 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.149 0.149 ↑ 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.023..0.149 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. 270.848 9,692.612 ↑ 107.9 50 1

Sort (cost=323,816.50..323,829.98 rows=5,394 width=1,680) (actual time=9,692.533..9,692.612 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 50kB
22. 565.712 9,421.764 ↓ 28.4 153,377 1

Hash Join (cost=316,974.61..323,637.31 rows=5,394 width=1,680) (actual time=8,473.723..9,421.764 rows=153,377 loops=1)

  • Hash Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
23. 4,173.653 4,173.653 ↓ 6.2 82,454 1

CTE Scan on infra_col_data (cost=0.00..265.34 rows=13,267 width=1,576) (actual time=3,620.372..4,173.653 rows=82,454 loops=1)

24. 463.183 4,682.399 ↓ 2.6 153,377 1

Hash (cost=315,262.10..315,262.10 rows=58,841 width=108) (actual time=4,682.397..4,682.399 rows=153,377 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 8 (originally 4) Memory Usage: 3841kB
25. 350.611 4,219.216 ↓ 2.6 153,377 1

Append (cost=0.00..315,262.10 rows=58,841 width=108) (actual time=315.319..4,219.216 rows=153,377 loops=1)

26. 0.009 0.009 ↓ 0.0 0 1

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

  • Filter: ((id_enterprise = 83) AND ((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
27. 3,868.596 3,868.596 ↓ 2.6 153,377 1

Index Scan using idx_po_import_request_83_id_enterprise_ts_status_last_updated_a on po_import_request_83 _req_1 (cost=0.43..315,262.10 rows=58,840 width=108) (actual time=315.305..3,868.596 rows=153,377 loops=1)

  • Index Cond: ((id_enterprise = 83) AND ((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
Planning time : 1,495.440 ms
Execution time : 9,702.097 ms