explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xPZW

Settings
# exclusive inclusive rows x rows loops node
1. 2,987.547 40,929.142 ↑ 1.0 1 1

Aggregate (cost=566,834.33..566,834.34 rows=1 width=48) (actual time=40,929.140..40,929.142 rows=1 loops=1)

2.          

CTE infra_col_data

3. 202.407 2,489.331 ↓ 6.2 82,454 1

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

4. 254.844 2,286.924 ↓ 6.4 84,268 1

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

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

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

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

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

  • Hash Cond: (po_proj_sub_infra.id_infra_instan = po_infra_instan.id_infra_instan)
7. 281.119 281.119 ↓ 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.023..281.119 rows=274,533 loops=1)

8. 114.466 861.123 ↓ 1.5 84,771 1

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

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

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

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

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

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=0.375..0.376 rows=53 loops=1)

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

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

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

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

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

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

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

16. 13.315 88.531 ↓ 1.8 12,498 1

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

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

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

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

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

19. 0.077 0.220 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.219..0.220 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.143 0.143 ↑ 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.024..0.143 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,083.207 37,941.595 ↓ 19.8 1,363,606 1

Nested Loop (cost=0.57..536,477.39 rows=68,911 width=4) (actual time=2,156.868..37,941.595 rows=1,363,606 loops=1)

22. 2,701.328 2,701.328 ↓ 6.2 82,454 1

CTE Scan on infra_col_data (cost=0.00..265.34 rows=13,267 width=4) (actual time=2,153.067..2,701.328 rows=82,454 loops=1)

23. 3,298.160 32,157.060 ↓ 2.4 17 82,454

Append (cost=0.57..40.35 rows=7 width=12) (actual time=0.204..0.390 rows=17 loops=82,454)

24. 11,213.744 11,213.744 ↓ 0.0 0 82,454

Index Scan using idx_po_import_request_ext_copy_ts_status_last_updated_at_infra on po_import_request_ext_copy _req (cost=0.57..16.32 rows=3 width=12) (actual time=0.136..0.136 rows=0 loops=82,454)

  • Index Cond: ((id_infra_instan = infra_col_data.id_infra_instan) AND ((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
25. 17,232.886 17,232.886 ↓ 5.7 17 82,454

Index Scan using idx_po_import_request_ext_copy_5_ts_status_last_updated_at_infr on po_import_request_ext_copy_5 _req_1 (cost=0.57..16.40 rows=3 width=12) (actual time=0.060..0.209 rows=17 loops=82,454)

  • Index Cond: ((id_infra_instan = infra_col_data.id_infra_instan) AND ((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
26. 412.270 412.270 ↓ 0.0 0 82,454

Index Scan using idx_po_import_request_ext_copy_all_ts_status_last_updated_at_in on po_import_request_ext_copy_all _req_2 (cost=0.43..7.63 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=82,454)

  • Index Cond: ((id_infra_instan = infra_col_data.id_infra_instan) AND ((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
Planning time : 3.990 ms
Execution time : 40,937.278 ms