explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gOt6

Settings
# exclusive inclusive rows x rows loops node
1. 0.144 3,212.859 ↑ 1.0 50 1

Limit (cost=522,950.03..522,950.15 rows=50 width=1,680) (actual time=3,212.646..3,212.859 rows=50 loops=1)

2.          

CTE infra_col_data

3. 206.911 2,764.156 ↓ 6.2 82,453 1

Unique (cost=27,175.54..27,241.59 rows=13,211 width=174) (actual time=2,438.061..2,764.156 rows=82,453 loops=1)

4. 251.107 2,557.245 ↓ 6.4 84,267 1

Sort (cost=27,175.54..27,208.57 rows=13,211 width=174) (actual time=2,438.056..2,557.245 rows=84,267 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: quicksort Memory: 20177kB
5. 191.323 2,306.138 ↓ 6.4 84,267 1

Hash Join (cost=19,831.80..26,271.28 rows=13,211 width=174) (actual time=1,294.822..2,306.138 rows=84,267 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
6. 490.373 1,728.516 ↓ 1.5 84,267 1

Hash Join (cost=16,293.66..22,387.30 rows=56,997 width=166) (actual time=908.477..1,728.516 rows=84,267 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_infra_instan = po_infra_instan.id_infra_instan)
7. 330.398 330.398 ↓ 1.0 274,532 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=0.017..330.398 rows=274,532 loops=1)

8. 121.489 907.745 ↓ 1.5 84,770 1

Hash (cost=15,580.81..15,580.81 rows=57,028 width=162) (actual time=907.744..907.745 rows=84,770 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12693kB
9. 385.233 786.256 ↓ 1.5 84,770 1

Hash Join (cost=20.85..15,580.81 rows=57,028 width=162) (actual time=14.057..786.256 rows=84,770 loops=1)

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

Seq Scan on po_infra_instan (cost=0.00..13,960.75 rows=274,382 width=162) (actual time=0.045..400.717 rows=274,441 loops=1)

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

Hash (cost=20.19..20.19 rows=53 width=4) (actual time=0.305..0.306 rows=53 loops=1)

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

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

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

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

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

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

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

16. 18.478 119.326 ↓ 1.8 12,498 1

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

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

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

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

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

19. 0.108 0.297 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.295..0.297 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.189 0.189 ↑ 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.008..0.189 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.185 3,212.715 ↑ 1,019.6 50 1

Sort (cost=495,708.43..495,835.88 rows=50,980 width=1,680) (actual time=3,212.641..3,212.715 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 39kB
22. 42.006 3,209.530 ↑ 47.8 1,066 1

Merge Join (cost=492,368.41..494,014.91 rows=50,980 width=1,680) (actual time=3,131.223..3,209.530 rows=1,066 loops=1)

  • Merge Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
23. 175.837 3,162.436 ↓ 2.6 34,010 1

Sort (cost=1,168.48..1,201.50 rows=13,211 width=1,576) (actual time=3,124.699..3,162.436 rows=34,010 loops=1)

  • Sort Key: infra_col_data.id_infra_instan
  • Sort Method: quicksort Memory: 18720kB
24. 2,986.599 2,986.599 ↓ 6.2 82,453 1

CTE Scan on infra_col_data (cost=0.00..264.22 rows=13,211 width=1,576) (actual time=2,438.066..2,986.599 rows=82,453 loops=1)

25. 3.067 5.088 ↑ 200.9 1,066 1

Sort (cost=491,199.94..491,735.26 rows=214,129 width=108) (actual time=3.507..5.088 rows=1,066 loops=1)

  • Sort Key: _req.id_infra_instan
  • Sort Method: quicksort Memory: 328kB
26. 1.867 2.021 ↑ 200.9 1,066 1

Bitmap Heap Scan on po_import_request_83 _req (cost=6,076.29..472,240.82 rows=214,129 width=108) (actual time=0.215..2.021 rows=1,066 loops=1)

  • Recheck Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
  • Heap Blocks: exact=380
27. 0.154 0.154 ↑ 200.9 1,066 1

Bitmap Index Scan on idx_po_import_request_83_extraction_ts_update_id_infra (cost=0.00..6,022.76 rows=214,129 width=0) (actual time=0.152..0.154 rows=1,066 loops=1)

  • Index Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
Planning time : 2.033 ms
Execution time : 3,222.919 ms