explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gzzc

Settings
# exclusive inclusive rows x rows loops node
1. 0.108 56,687.417 ↑ 1.0 50 1

Limit (cost=547,736.88..547,737.01 rows=50 width=1,680) (actual time=56,687.258..56,687.417 rows=50 loops=1)

2.          

CTE infra_col_data

3. 202.138 2,753.370 ↓ 6.2 82,454 1

Unique (cost=28,049.08..28,115.42 rows=13,267 width=174) (actual time=2,418.985..2,753.370 rows=82,454 loops=1)

4. 306.335 2,551.232 ↓ 6.4 84,268 1

Sort (cost=28,049.08..28,082.25 rows=13,267 width=174) (actual time=2,418.981..2,551.232 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11,520kB
5. 281.789 2,244.897 ↓ 6.4 84,268 1

Hash Join (cost=10,514.34..27,140.59 rows=13,267 width=174) (actual time=1,103.633..2,244.897 rows=84,268 loops=1)

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

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

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
7. 560.674 560.674 ↑ 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.509..560.674 rows=274,442 loops=1)

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

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=1.519..1.520 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
9. 1.461 1.461 ↑ 1.0 53 1

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

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

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

  • Buckets: 131,072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3,073kB
11. 369.215 927.956 ↓ 1.3 84,697 1

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

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

13. 10.200 278.249 ↑ 2.1 9,532 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 666kB
14. 94.657 268.049 ↑ 2.1 9,532 1

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

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

16. 12.657 85.302 ↓ 1.8 12,498 1

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

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
17. 41.403 72.645 ↓ 1.8 12,498 1

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

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

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

19. 0.074 0.215 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.214..0.215 rows=70 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
20. 0.141 0.141 ↑ 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.014..0.141 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. 3,723.248 56,687.309 ↑ 129.5 50 1

Sort (cost=519,621.47..519,637.65 rows=6,473 width=1,680) (actual time=56,687.254..56,687.309 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 42kB
22. 4,687.305 52,964.061 ↓ 354.4 2,294,198 1

Hash Join (cost=512,520.36..519,406.44 rows=6,473 width=1,680) (actual time=47,741.294..52,964.061 rows=2,294,198 loops=1)

  • Hash Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
23. 2,992.184 2,992.184 ↓ 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,418.990..2,992.184 rows=82,454 loops=1)

24. 5,345.355 45,284.572 ↓ 32.5 2,294,198 1

Hash (cost=510,464.75..510,464.75 rows=70,609 width=108) (actual time=45,284.570..45,284.572 rows=2,294,198 loops=1)

  • Buckets: 32,768 (originally 32768) Batches: 128 (originally 4) Memory Usage: 3,841kB
25. 4,645.717 39,939.217 ↓ 32.5 2,294,198 1

Append (cost=0.00..510,464.75 rows=70,609 width=108) (actual time=663.460..39,939.217 rows=2,294,198 loops=1)

26. 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 = 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,5}'::integer[])))
27. 35,293.493 35,293.493 ↓ 32.5 2,294,198 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.56..510,464.75 rows=70,608 width=108) (actual time=663.449..35,293.493 rows=2,294,198 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,5}'::integer[])))
Planning time : 32.206 ms
Execution time : 56,693.753 ms