explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M125

Settings
# exclusive inclusive rows x rows loops node
1. 1.297 12,054.500 ↑ 1.0 500 1

Limit (cost=135,855.02..144,153.77 rows=500 width=35,039) (actual time=12,047.374..12,054.500 rows=500 loops=1)

2. 8.008 12,053.203 ↑ 3.5 500 1

Result (cost=135,855.02..164,568.70 rows=1,730 width=35,039) (actual time=12,047.369..12,053.203 rows=500 loops=1)

3. 33.252 12,042.079 ↑ 3.5 500 1

Sort (cost=135,855.02..135,859.35 rows=1,730 width=35,037) (actual time=12,041.374..12,042.079 rows=500 loops=1)

  • Sort Key: pipelines.license_line_num
  • Sort Method: top-N heapsort Memory: 1,656kB
4. 1,299.645 12,008.827 ↓ 1.3 2,180 1

GroupAggregate (cost=109,515.78..109,844.48 rows=1,730 width=35,037) (actual time=10,422.147..12,008.827 rows=2,180 loops=1)

  • Group Key: pipelines.id, risers.id, operating_conditions.id, internal_mitigations.id, risk_assessments.id, external_mitigations.id, monitorings.id, inspections.id, incidents.id, validations.id, pipeline_stats.id
5. 2,123.773 10,709.182 ↓ 40.4 69,960 1

Sort (cost=109,515.78..109,520.10 rows=1,730 width=36,683) (actual time=10,421.989..10,709.182 rows=69,960 loops=1)

  • Sort Key: pipelines.id, risers.id, operating_conditions.id, internal_mitigations.id, risk_assessments.id, external_mitigations.id, monitorings.id, inspections.id, incidents.id, validations.id, pipeline_stats.id
  • Sort Method: external sort Disk: 171,808kB
6. 766.897 8,585.409 ↓ 40.4 69,960 1

Nested Loop Left Join (cost=16,231.20..55,151.73 rows=1,730 width=36,683) (actual time=689.686..8,585.409 rows=69,960 loops=1)

7. 618.060 7,678.592 ↓ 40.4 69,960 1

Hash Left Join (cost=16,231.07..54,887.41 rows=1,730 width=36,677) (actual time=689.661..7,678.592 rows=69,960 loops=1)

  • Hash Cond: (pipelines.id = mismatches.pipeline_id)
8. 1,201.339 7,031.724 ↓ 40.4 69,915 1

Merge Left Join (cost=15,703.10..54,295.43 rows=1,730 width=36,673) (actual time=660.803..7,031.724 rows=69,915 loops=1)

  • Merge Cond: (pipelines.id = risk_assessments.pipeline_id)
  • Join Filter: (risk_assessments.created_at = (SubPlan 5))
9. 1,619.332 4,388.269 ↓ 40.4 69,915 1

Gather Merge (cost=15,702.82..51,981.21 rows=1,730 width=33,029) (actual time=660.684..4,388.269 rows=69,915 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 210.355 2,768.937 ↓ 32.3 23,305 3 / 3

Merge Left Join (cost=14,702.79..50,781.50 rows=721 width=33,029) (actual time=568.897..2,768.937 rows=23,305 loops=3)

  • Merge Cond: (pipelines.id = cathodic_protections.pipeline_id)
11. 200.323 2,429.462 ↓ 19.7 14,225 3 / 3

Merge Left Join (cost=14,702.50..48,695.62 rows=721 width=32,960) (actual time=568.723..2,429.462 rows=14,225 loops=3)

  • Merge Cond: (pipelines.id = validations.pipeline_id)
12. 155.515 2,149.841 ↓ 19.7 14,225 3 / 3

Nested Loop Left Join (cost=14,702.05..47,262.48 rows=721 width=32,316) (actual time=504.220..2,149.841 rows=14,225 loops=3)

13. 154.827 1,852.079 ↓ 19.7 14,225 3 / 3

Nested Loop Left Join (cost=14,701.75..43,635.20 rows=721 width=30,608) (actual time=504.154..1,852.079 rows=14,225 loops=3)

14. 167.853 1,526.556 ↓ 19.7 14,225 3 / 3

Merge Left Join (cost=14,701.34..39,063.54 rows=721 width=24,267) (actual time=504.088..1,526.556 rows=14,225 loops=3)

  • Merge Cond: (pipelines.id = monitorings.pipeline_id)
15. 95.114 1,269.939 ↓ 19.7 14,225 3 / 3

Merge Left Join (cost=14,700.89..37,711.31 rows=721 width=23,813) (actual time=431.106..1,269.939 rows=14,225 loops=3)

  • Merge Cond: (pipelines.id = pigging_run_pipeline.pipeline_id)
16. 74.316 1,133.333 ↓ 12.6 9,118 3 / 3

Nested Loop Left Join (cost=14,547.47..37,549.35 rows=721 width=23,809) (actual time=410.463..1,133.333 rows=9,118 loops=3)

17. 61.272 972.077 ↓ 19.0 6,688 3 / 3

Nested Loop Left Join (cost=14,547.05..23,745.88 rows=352 width=20,941) (actual time=410.386..972.077 rows=6,688 loops=3)

18. 72.894 857.304 ↓ 19.0 6,688 3 / 3

Nested Loop Left Join (cost=14,546.76..22,941.01 rows=352 width=18,751) (actual time=410.314..857.304 rows=6,688 loops=3)

19. 65.864 730.909 ↓ 19.0 6,688 3 / 3

Nested Loop Left Join (cost=14,546.48..21,982.66 rows=352 width=17,558) (actual time=410.279..730.909 rows=6,688 loops=3)

20. 36.364 584.793 ↓ 19.0 6,688 3 / 3

Nested Loop Left Join (cost=14,546.06..19,527.17 rows=352 width=13,196) (actual time=410.185..584.793 rows=6,688 loops=3)

21. 8.288 519.362 ↓ 2.1 727 3 / 3

Nested Loop Left Join (cost=14,545.64..17,546.85 rows=352 width=13,175) (actual time=410.101..519.362 rows=727 loops=3)

22. 54.132 500.174 ↓ 2.1 727 3 / 3

Merge Left Join (cost=14,545.22..14,739.37 rows=352 width=3,663) (actual time=409.988..500.174 rows=727 loops=3)

  • Merge Cond: (pipelines.id = operating_conditions.pipeline_id)
23. 6.425 126.315 ↓ 2.1 727 3 / 3

Sort (cost=9,783.54..9,784.42 rows=352 width=3,480) (actual time=124.846..126.315 rows=727 loops=3)

  • Sort Key: pipelines.id
  • Sort Method: quicksort Memory: 436kB
24. 119.890 119.890 ↓ 2.1 727 3 / 3

Parallel Seq Scan on pipelines (cost=0.00..9,768.66 rows=352 width=3,480) (actual time=0.299..119.890 rows=727 loops=3)

  • Filter: ((deleted_at IS NULL) AND (lower((client_business_name)::text) = ANY ('{"karve energy","karve energy inc."}'::text[])))
  • Rows Removed by Filter: 39,042
25. 99.920 319.727 ↑ 1.0 25,503 3 / 3

Materialize (cost=4,761.66..4,889.43 rows=25,553 width=183) (actual time=165.782..319.727 rows=25,503 loops=3)

26. 159.212 219.807 ↑ 1.0 25,503 3 / 3

Sort (cost=4,761.66..4,825.55 rows=25,553 width=183) (actual time=165.763..219.807 rows=25,503 loops=3)

  • Sort Key: operating_conditions.pipeline_id
  • Sort Method: external sort Disk: 2,152kB
27. 60.595 60.595 ↑ 1.0 25,503 3 / 3

Seq Scan on operating_conditions (cost=0.00..619.53 rows=25,553 width=183) (actual time=0.046..60.595 rows=25,503 loops=3)

28. 10.900 10.900 ↑ 1.0 1 2,180 / 3

Index Scan using risers_pipeline_id_index on risers (cost=0.42..7.97 rows=1 width=9,512) (actual time=0.013..0.015 rows=1 loops=2,180)

  • Index Cond: (pipeline_id = pipelines.id)
29. 29.067 29.067 ↑ 2.3 9 2,180 / 3

Index Only Scan using wells_name_pipeline_id_unique on wells (cost=0.42..5.42 rows=21 width=25) (actual time=0.013..0.040 rows=9 loops=2,180)

  • Index Cond: (pipeline_id = pipelines.id)
  • Heap Fetches: 6,624
30. 80.252 80.252 ↑ 1.0 1 20,063 / 3

Index Scan using pipeline_stats_pipeline_id_index on pipeline_stats (cost=0.42..6.97 rows=1 width=4,362) (actual time=0.010..0.012 rows=1 loops=20,063)

  • Index Cond: (pipeline_id = pipelines.id)
31. 53.501 53.501 ↑ 1.0 1 20,063 / 3

Index Scan using internal_mitigations_pipeline_id_index on internal_mitigations (cost=0.29..2.71 rows=1 width=1,193) (actual time=0.006..0.008 rows=1 loops=20,063)

  • Index Cond: (pipeline_id = pipelines.id)
32. 53.501 53.501 ↑ 1.0 1 20,063 / 3

Index Scan using external_mitigations_pipeline_id_index on external_mitigations (cost=0.29..2.28 rows=1 width=2,190) (actual time=0.006..0.008 rows=1 loops=20,063)

  • Index Cond: (pipeline_id = pipelines.id)
33. 86.940 86.940 ↑ 14.0 1 20,063 / 3

Index Scan using segments_pipeline_id_index on segments (cost=0.42..39.07 rows=14 width=2,872) (actual time=0.009..0.013 rows=1 loops=20,063)

  • Index Cond: (pipeline_id = pipelines.id)
34. 25.935 41.492 ↓ 8.5 11,280 3 / 3

Sort (cost=153.42..156.75 rows=1,333 width=8) (actual time=20.627..41.492 rows=11,280 loops=3)

  • Sort Key: pigging_run_pipeline.pipeline_id
  • Sort Method: quicksort Memory: 111kB
35. 9.861 15.557 ↑ 1.0 1,333 3 / 3

Hash Left Join (cost=61.39..84.23 rows=1,333 width=8) (actual time=3.788..15.557 rows=1,333 loops=3)

  • Hash Cond: (pigging_run_pipeline.pigging_run_id = pigging_runs.id)
36. 2.145 2.145 ↑ 1.0 1,333 3 / 3

Seq Scan on pigging_run_pipeline (cost=0.00..19.33 rows=1,333 width=8) (actual time=0.049..2.145 rows=1,333 loops=3)

37. 1.523 3.551 ↑ 1.0 947 3 / 3

Hash (cost=49.53..49.53 rows=949 width=8) (actual time=3.550..3.551 rows=947 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
38. 2.028 2.028 ↑ 1.0 947 3 / 3

Seq Scan on pigging_runs (cost=0.00..49.53 rows=949 width=8) (actual time=0.022..2.028 rows=947 loops=3)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 6
39. 88.764 88.764 ↓ 1.5 39,001 3 / 3

Index Scan using monitorings_pipeline_id_index on monitorings (cost=0.29..1,285.13 rows=25,499 width=454) (actual time=0.062..88.764 rows=39,001 loops=3)

40. 170.696 170.696 ↑ 1.0 1 42,674 / 3

Index Scan using inspections_pipeline_id_index on inspections (cost=0.42..6.33 rows=1 width=6,341) (actual time=0.010..0.012 rows=1 loops=42,674)

  • Index Cond: (pipeline_id = pipelines.id)
41. 142.247 142.247 ↑ 1.0 1 42,674 / 3

Index Scan using incidents_pipeline_id_index on incidents (cost=0.29..5.02 rows=1 width=1,708) (actual time=0.007..0.010 rows=1 loops=42,674)

  • Index Cond: (pipeline_id = pipelines.id)
42. 79.298 79.298 ↓ 1.5 39,001 3 / 3

Index Scan using validations_pipeline_id_index on validations (cost=0.29..1,366.09 rows=25,483 width=644) (actual time=0.033..79.298 rows=39,001 loops=3)

43. 129.120 129.120 ↓ 1.7 22,647 3 / 3

Index Scan using cathodic_protections_pipeline_id_index on cathodic_protections (cost=0.29..6,803.25 rows=13,618 width=73) (actual time=0.087..129.120 rows=22,647 loops=3)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 34,259
44. 190.990 190.990 ↓ 12.9 73,085 1

Index Scan using risk_assessments_pipeline_id_index on risk_assessments (cost=0.28..1,613.03 rows=5,685 width=3,644) (actual time=0.021..190.990 rows=73,085 loops=1)

45.          

SubPlan (for Merge Left Join)

46. 556.056 1,251.126 ↑ 1.0 1 69,507

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=69,507)

47. 695.070 695.070 ↑ 1.0 1 69,507

Index Scan using risk_assessments_pipeline_id_index on risk_assessments ra (cost=0.28..8.30 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=69,507)

  • Index Cond: (pipeline_id = pipelines.id)
48. 12.948 28.808 ↑ 1.1 8,187 1

Hash (cost=416.33..416.33 rows=8,931 width=8) (actual time=28.807..28.808 rows=8,187 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 448kB
49. 15.860 15.860 ↑ 1.1 8,187 1

Seq Scan on mismatches (cost=0.00..416.33 rows=8,931 width=8) (actual time=0.063..15.860 rows=8,187 loops=1)

  • Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
  • Rows Removed by Filter: 6,646
50. 139.920 139.920 ↓ 0.0 0 69,960

Index Scan using mismatch_types_pkey on mismatch_types (cost=0.14..0.15 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=69,960)

  • Index Cond: (mismatches.mismatch_type_id = id)
51.          

SubPlan (for Result)

52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pigging_runs_launcher_index on pigging_runs pigging_runs_1 (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: (launcher = (pipelines.license_line_num)::text)
  • Heap Fetches: 0
53. 1.628 1.628 ↑ 1.0 953 1

Seq Scan on pigging_runs pigging_runs_2 (cost=0.00..49.53 rows=953 width=32) (actual time=0.027..1.628 rows=953 loops=1)

54. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pigging_runs_receiver_index on pigging_runs pigging_runs_3 (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: (receiver = (pipelines.license_line_num)::text)
  • Heap Fetches: 0
55. 1.488 1.488 ↑ 1.0 953 1

Seq Scan on pigging_runs pigging_runs_4 (cost=0.00..49.53 rows=953 width=32) (actual time=0.008..1.488 rows=953 loops=1)

Planning time : 69.913 ms