explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EZTU

Settings
# exclusive inclusive rows x rows loops node
1. 0.109 239,968.894 ↑ 1.0 50 1

Limit (cost=1,565,352.18..1,565,352.31 rows=50 width=1,679) (actual time=239,968.738..239,968.894 rows=50 loops=1)

2.          

CTE infra_col_data

3. 172.975 2,704.755 ↓ 6.2 82,454 1

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

4. 253.677 2,531.780 ↓ 6.4 84,268 1

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

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: quicksort Memory: 20,177kB
5. 209.325 2,278.103 ↓ 6.4 84,268 1

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

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

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

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

8. 136.113 973.454 ↓ 1.5 84,771 1

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

  • Buckets: 131,072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12,693kB
9. 414.562 837.341 ↓ 1.5 84,771 1

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

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

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

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=0.379..0.380 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
12. 0.289 0.289 ↑ 1.0 53 1

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

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

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

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

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

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

16. 13.149 87.013 ↓ 1.8 12,498 1

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

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

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

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

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

19. 0.100 0.231 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.230..0.231 rows=70 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
20. 0.131 0.131 ↑ 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.010..0.131 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. 4.515 239,968.785 ↑ 5,345.3 50 1

Sort (cost=1,538,096.23..1,538,764.39 rows=267,264 width=1,679) (actual time=239,968.734..239,968.785 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 38kB
22. 117.217 239,964.270 ↑ 136.5 1,958 1

Hash Join (cost=14,099.51..1,529,217.91 rows=267,264 width=1,679) (actual time=3,972.894..239,964.270 rows=1,958 loops=1)

  • Hash Cond: (_req.id_infra_instan = infra_col_data.id_infra_instan)
23. 195.515 236,840.646 ↓ 24.8 99,976 1

Append (cost=13,668.33..1,519,420.92 rows=4,029 width=107) (actual time=467.284..236,840.646 rows=99,976 loops=1)

24. 235,267.442 235,605.212 ↓ 12.7 49,988 1

Bitmap Heap Scan on po_import_request_ext_copy _req (cost=13,668.33..1,409,974.94 rows=3,935 width=106) (actual time=467.280..235,605.212 rows=49,988 loops=1)

  • Recheck Cond: ((nu_extraction_status / 1000) = ANY ('{8,9}'::integer[]))
  • Filter: (((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
  • Rows Removed by Filter: 1,809,205
  • Heap Blocks: exact=411,537
25. 337.770 337.770 ↓ 2.4 1,859,193 1

Bitmap Index Scan on idx_po_import_request_ext_copy_nu_extraction_status (cost=0.00..13,667.35 rows=786,998 width=0) (actual time=337.768..337.770 rows=1,859,193 loops=1)

  • Index Cond: ((nu_extraction_status / 1000) = ANY ('{8,9}'::integer[]))
26. 1,038.387 1,038.387 ↓ 537.5 49,986 1

Seq Scan on po_import_request_ext_copy_8 _req_1 (cost=0.00..109,249.98 rows=93 width=148) (actual time=86.762..1,038.387 rows=49,986 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,9}'::integer[])))
  • Rows Removed by Filter: 1,806,127
27. 1.532 1.532 ↓ 2.0 2 1

Seq Scan on po_import_request_ext_copy_9 _req_2 (cost=0.00..196.00 rows=1 width=159) (actual time=0.385..1.532 rows=2 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,9}'::integer[])))
  • Rows Removed by Filter: 3,078
28. 115.629 3,006.407 ↓ 6.2 82,454 1

Hash (cost=265.34..265.34 rows=13,267 width=1,576) (actual time=3,006.405..3,006.407 rows=82,454 loops=1)

  • Buckets: 131,072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 13,659kB
29. 2,890.778 2,890.778 ↓ 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,426.961..2,890.778 rows=82,454 loops=1)

Planning time : 13.201 ms
Execution time : 239,978.088 ms