explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z1Ff

Settings
# exclusive inclusive rows x rows loops node
1. 0.116 5,846.879 ↑ 1.0 50 1

Limit (cost=106,640.11..106,640.24 rows=50 width=1,678) (actual time=5,846.712..5,846.879 rows=50 loops=1)

2.          

CTE infra_col_data

3. 167.983 2,409.061 ↓ 6.2 82,454 1

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

4. 222.611 2,241.078 ↓ 6.4 84,268 1

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

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

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

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

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

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

8. 114.374 843.596 ↓ 1.5 84,771 1

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

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

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

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

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

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=0.269..0.270 rows=53 loops=1)

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

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

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

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

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

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

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

16. 13.437 87.289 ↓ 1.8 12,498 1

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

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

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

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

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

19. 0.086 0.242 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.241..0.242 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.156 0.156 ↑ 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.028..0.156 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. 222.635 5,846.763 ↑ 17.6 50 1

Sort (cost=79,384.16..79,386.36 rows=882 width=1,678) (actual time=5,846.709..5,846.763 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 50kB
22. 568.906 5,624.128 ↓ 173.9 153,377 1

Merge Join (cost=79,230.52..79,354.86 rows=882 width=1,678) (actual time=4,622.534..5,624.128 rows=153,377 loops=1)

  • Merge Cond: (_req.id_infra_instan = infra_col_data.id_infra_instan)
23. 510.650 2,158.911 ↓ 20.2 198,759 1

Sort (cost=78,056.69..78,081.28 rows=9,837 width=106) (actual time=1,916.321..2,158.911 rows=198,759 loops=1)

  • Sort Key: _req.id_infra_instan
  • Sort Method: quicksort Memory: 41102kB
24. 491.549 1,648.261 ↓ 20.2 198,759 1

Bitmap Heap Scan on po_import_request _req (cost=40,127.09..77,404.30 rows=9,837 width=106) (actual time=1,166.428..1,648.261 rows=198,759 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) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
  • Heap Blocks: exact=36159
25. 83.343 1,156.712 ↓ 0.0 0 1

BitmapAnd (cost=40,127.09..40,127.09 rows=9,837 width=0) (actual time=1,156.710..1,156.712 rows=0 loops=1)

26. 613.445 613.445 ↓ 12.7 5,015,642 1

Bitmap Index Scan on idx_po_import_request_nu_extraction_status_ts_last_updated (cost=0.00..8,275.56 rows=393,499 width=0) (actual time=613.443..613.445 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))
27. 459.924 459.924 ↓ 1.8 3,481,992 1

Bitmap Index Scan on idx_po_import_request_nu_extraction_status (cost=0.00..31,846.36 rows=1,967,496 width=0) (actual time=459.922..459.924 rows=3,481,992 loops=1)

  • Index Cond: ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[]))
28. 308.245 2,896.311 ↓ 15.2 201,409 1

Sort (cost=1,173.83..1,207.00 rows=13,267 width=1,576) (actual time=2,696.663..2,896.311 rows=201,409 loops=1)

  • Sort Key: infra_col_data.id_infra_instan
  • Sort Method: quicksort Memory: 18720kB
29. 2,588.066 2,588.066 ↓ 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,140.212..2,588.066 rows=82,454 loops=1)

Planning time : 1.579 ms
Execution time : 5,861.390 ms