explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DMCQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.118 3,400.612 ↑ 1.0 50 1

Limit (cost=625,401.87..625,401.99 rows=50 width=1,678) (actual time=3,400.446..3,400.612 rows=50 loops=1)

  • Buffers: shared hit=83 read=46876 written=13, temp read=4182 written=6101
2.          

CTE infra_col_data

3. 162.440 2,296.977 ↓ 6.2 82,454 1

Unique (cost=28,049.08..28,115.42 rows=13,267 width=174) (actual time=2,035.953..2,296.977 rows=82,454 loops=1)

  • Buffers: shared hit=80 read=14321, temp read=2297 written=2295
4. 231.630 2,134.537 ↓ 6.4 84,268 1

Sort (cost=28,049.08..28,082.25 rows=13,267 width=174) (actual time=2,035.948..2,134.537 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11512kB
  • Buffers: shared hit=80 read=14321, temp read=2297 written=2295
5. 208.655 1,902.907 ↓ 6.4 84,268 1

Hash Join (cost=10,514.34..27,140.59 rows=13,267 width=174) (actual time=991.063..1,902.907 rows=84,268 loops=1)

  • Hash Cond: (po_infra_instan.id_infra_instan = po_proj_sub_infra.id_infra_instan)
  • Buffers: shared hit=80 read=14321, temp read=854 written=852
6. 349.647 703.664 ↓ 1.5 84,771 1

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

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
  • Buffers: shared read=11231
7. 353.769 353.769 ↑ 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..353.769 rows=274,442 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
  • Buffers: shared read=11214
8. 0.055 0.248 ↑ 1.0 53 1

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=0.247..0.248 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared read=17
9. 0.193 0.193 ↑ 1.0 53 1

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

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
  • Buffers: shared read=17
10. 94.526 990.588 ↓ 1.3 84,697 1

Hash (cost=9,698.11..9,698.11 rows=63,631 width=16) (actual time=990.588..990.588 rows=84,697 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
  • Buffers: shared hit=80 read=3090, temp written=185
11. 357.317 896.062 ↓ 1.3 84,697 1

Hash Join (cost=3,538.13..9,698.11 rows=63,631 width=16) (actual time=273.092..896.062 rows=84,697 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
  • Buffers: shared hit=80 read=3090
12. 265.891 265.891 ↓ 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.013..265.891 rows=274,533 loops=1)

  • Buffers: shared read=1749
13. 10.221 272.854 ↑ 2.1 9,532 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 666kB
  • Buffers: shared hit=80 read=1341
14. 94.096 262.633 ↑ 2.1 9,532 1

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

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
  • Buffers: shared hit=80 read=1341
15. 84.463 84.463 ↓ 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.013..84.463 rows=85,261 loops=1)

  • Buffers: shared read=932
16. 12.879 84.074 ↓ 1.8 12,498 1

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
  • Buffers: shared hit=80 read=409
17. 41.492 71.195 ↓ 1.8 12,498 1

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

  • Hash Cond: (po_project.id_organization = org.id_organization)
  • Buffers: shared hit=80 read=409
18. 29.479 29.479 ↓ 1.0 29,343 1

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

  • Buffers: shared read=405
19. 0.073 0.224 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.223..0.224 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=80 read=4
20. 0.151 0.151 ↑ 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.023..0.151 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
  • Buffers: shared hit=80 read=4
21. 15.996 3,400.494 ↑ 340.0 50 1

Sort (cost=597,286.45..597,328.96 rows=17,002 width=1,678) (actual time=3,400.443..3,400.494 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 38kB
  • Buffers: shared hit=83 read=46876 written=13, temp read=4182 written=6101
22. 124.193 3,384.498 ↑ 1.6 10,756 1

Merge Join (cost=595,476.86..596,721.66 rows=17,002 width=1,678) (actual time=3,050.801..3,384.498 rows=10,756 loops=1)

  • Merge Cond: (_req.id_infra_instan = infra_col_data.id_infra_instan)
  • Buffers: shared hit=83 read=46876 written=13, temp read=4182 written=6101
23. 162.326 474.386 ↑ 3.4 55,941 1

Sort (cost=585,231.02..585,709.32 rows=191,321 width=106) (actual time=412.919..474.386 rows=55,941 loops=1)

  • Sort Key: _req.id_infra_instan
  • Sort Method: external merge Disk: 8248kB
  • Buffers: shared hit=3 read=32555 written=13, temp read=1034 written=1034
24. 301.834 312.060 ↑ 3.4 55,941 1

Bitmap Heap Scan on po_import_request _req (cost=5,557.05..557,327.28 rows=191,321 width=106) (actual time=16.433..312.060 rows=55,941 loops=1)

  • Recheck Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
  • Heap Blocks: exact=32332
  • Buffers: shared hit=3 read=32555 written=13
25. 10.226 10.226 ↑ 3.4 55,941 1

Bitmap Index Scan on idx_po_import_request_extraction_ts_update_id_infra (cost=0.00..5,509.22 rows=191,321 width=0) (actual time=10.224..10.226 rows=55,941 loops=1)

  • Index Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
  • Buffers: shared hit=3 read=223
26. 102.178 2,785.919 ↓ 4.6 60,426 1

Materialize (cost=10,245.83..10,312.17 rows=13,267 width=1,576) (actual time=2,628.027..2,785.919 rows=60,426 loops=1)

  • Buffers: shared hit=80 read=14321, temp read=3148 written=5067
27. 203.018 2,683.741 ↓ 3.8 50,025 1

Sort (cost=10,245.83..10,279.00 rows=13,267 width=1,576) (actual time=2,628.022..2,683.741 rows=50,025 loops=1)

  • Sort Key: infra_col_data.id_infra_instan
  • Sort Method: external sort Disk: 10880kB
  • Buffers: shared hit=80 read=14321, temp read=3148 written=5067
28. 2,480.723 2,480.723 ↓ 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,035.957..2,480.723 rows=82,454 loops=1)

  • Buffers: shared hit=80 read=14321, temp read=2297 written=3707
Planning time : 1.843 ms
Execution time : 3,410.892 ms