explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kqrO

Settings
# exclusive inclusive rows x rows loops node
1. 0.109 24,419.594 ↑ 1.0 50 1

Limit (cost=2,428,053.86..2,428,053.98 rows=50 width=1,678) (actual time=24,419.435..24,419.594 rows=50 loops=1)

2.          

CTE infra_col_data

3. 161.044 2,292.093 ↓ 6.2 82,454 1

Unique (cost=27,175.54..27,241.59 rows=13,211 width=174) (actual time=2,031.133..2,292.093 rows=82,454 loops=1)

4. 218.406 2,131.049 ↓ 6.4 84,268 1

Sort (cost=27,175.54..27,208.57 rows=13,211 width=174) (actual time=2,031.128..2,131.049 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: quicksort Memory: 20177kB
5. 170.243 1,912.643 ↓ 6.4 84,268 1

Hash Join (cost=19,831.80..26,271.28 rows=13,211 width=174) (actual time=1,070.778..1,912.643 rows=84,268 loops=1)

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

Hash Join (cost=16,293.66..22,387.30 rows=56,997 width=166) (actual time=799.287..1,470.954 rows=84,268 loops=1)

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

8. 102.591 798.966 ↓ 1.5 84,771 1

Hash (cost=15,580.81..15,580.81 rows=57,028 width=162) (actual time=798.965..798.966 rows=84,771 loops=1)

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

Hash Join (cost=20.85..15,580.81 rows=57,028 width=162) (actual time=0.491..696.375 rows=84,771 loops=1)

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

Seq Scan on po_infra_instan (cost=0.00..13,960.75 rows=274,382 width=162) (actual time=0.016..350.524 rows=274,442 loops=1)

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

Hash (cost=20.19..20.19 rows=53 width=4) (actual time=0.250..0.251 rows=53 loops=1)

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

Seq Scan on po_connector_details (cost=0.00..20.19 rows=53 width=4) (actual time=0.016..0.196 rows=53 loops=1)

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

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

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

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

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

16. 12.642 83.629 ↓ 1.8 12,498 1

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

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

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

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

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

19. 0.075 0.210 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.209..0.210 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.135 0.135 ↑ 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.012..0.135 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,154.972 24,419.485 ↑ 8,393.5 50 1

Sort (cost=2,400,812.26..2,401,861.44 rows=419,673 width=1,678) (actual time=24,419.433..24,419.485 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 42kB
22. 3,729.430 21,264.513 ↓ 5.5 2,294,198 1

Hash Join (cost=2,081,886.70..2,386,871.03 rows=419,673 width=1,678) (actual time=17,120.245..21,264.513 rows=2,294,198 loops=1)

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

CTE Scan on infra_col_data (cost=0.00..264.22 rows=13,211 width=1,576) (actual time=2,031.139..2,465.184 rows=82,454 loops=1)

24. 6,472.980 15,069.899 ↓ 1.1 5,015,642 1

Hash (cost=1,943,870.77..1,943,870.77 rows=4,742,555 width=106) (actual time=15,069.897..15,069.899 rows=5,015,642 loops=1)

  • Buckets: 4194304 Batches: 2 Memory Usage: 380330kB
25. 8,050.754 8,596.919 ↓ 1.1 5,015,642 1

Bitmap Heap Scan on po_import_request _req (cost=105,127.10..1,943,870.77 rows=4,742,555 width=106) (actual time=617.812..8,596.919 rows=5,015,642 loops=1)

  • Recheck Cond: (((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
  • Filter: ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7,5}'::integer[]))
  • Heap Blocks: exact=267560
26. 546.165 546.165 ↓ 1.0 5,015,642 1

Bitmap Index Scan on idx_po_import_request_ts_status_last_updated_at_extraction (cost=0.00..103,941.46 rows=4,943,689 width=0) (actual time=546.163..546.165 rows=5,015,642 loops=1)

  • Index Cond: (((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
Planning time : 1.591 ms
Execution time : 24,427.042 ms