explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t7KF

Settings
# exclusive inclusive rows x rows loops node
1. 0.405 61,095.746 ↑ 1.0 100 1

Limit (cost=3,906.20..337,684.39 rows=100 width=10,638) (actual time=277.941..61,095.746 rows=100 loops=1)

2.          

CTE subquery

3. 17.042 166.131 ↑ 1.8 2,591 1

GroupAggregate (cost=3,709.05..3,905.49 rows=4,622 width=52) (actual time=143.822..166.131 rows=2,591 loops=1)

  • Group Key: f.pipeline_id, rf.licence_number, rf.pipeline_licence_line_no
4. 12.315 149.089 ↑ 1.2 3,716 1

Sort (cost=3,709.05..3,720.61 rows=4,622 width=532) (actual time=143.771..149.089 rows=3,716 loops=1)

  • Sort Key: f.pipeline_id, rf.licence_number, rf.pipeline_licence_line_no
  • Sort Method: quicksort Memory: 387kB
5. 11.824 136.774 ↑ 1.2 3,716 1

Hash Join (cost=202.27..3,427.71 rows=4,622 width=532) (actual time=16.163..136.774 rows=3,716 loops=1)

  • Hash Cond: (f.incident_type_id = it.id)
6. 59.353 124.900 ↓ 1.0 4,627 1

Hash Join (cost=201.00..3,410.87 rows=4,622 width=20) (actual time=16.101..124.900 rows=4,627 loops=1)

  • Hash Cond: (rf.id = f.regulatory_failure_id)
7. 49.479 49.479 ↑ 1.0 33,178 1

Seq Scan on regulatory_failures rf (cost=0.00..2,997.78 rows=33,178 width=12) (actual time=0.006..49.479 rows=33,178 loops=1)

8. 7.167 16.068 ↓ 1.0 4,627 1

Hash (cost=143.22..143.22 rows=4,622 width=16) (actual time=16.067..16.068 rows=4,627 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 278kB
9. 8.901 8.901 ↓ 1.0 4,629 1

Seq Scan on failures f (cost=0.00..143.22 rows=4,622 width=16) (actual time=0.008..8.901 rows=4,629 loops=1)

10. 0.025 0.050 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=520) (actual time=0.049..0.050 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.025 0.025 ↑ 1.0 12 1

Seq Scan on incident_types it (cost=0.00..1.12 rows=12 width=520) (actual time=0.007..0.025 rows=12 loops=1)

12. 0.947 61,095.341 ↑ 122.7 100 1

Nested Loop (cost=0.71..40,957,922.91 rows=12,271 width=10,638) (actual time=277.936..61,095.341 rows=100 loops=1)

  • Join Filter: (p.province_id = provinces.id)
13. 43,080.967 61,094.194 ↑ 122.7 100 1

Nested Loop (cost=0.71..40,945,206.06 rows=12,271 width=9,602) (actual time=277.903..61,094.194 rows=100 loops=1)

  • Join Filter: ((p.id = subquery.pipeline_id) OR ((ps.license_line_prev_1)::text = ((('AB'::text || lpad((subquery.licence_number)::text, 6, '0'::text)) || '-'::text) || lpad((subquery.pipeline_licence_line_no)::text, 4, '0'::text))) OR ((ps.license_line_prev_2)::text = ((('AB'::text || lpad((subquery.licence_number)::text, 6, '0'::text)) || '-'::text) || lpad((subquery.pipeline_licence_line_no)::text, 4, '0'::text))) OR ((ps.license_line_prev_3)::text = ((('AB'::text || lpad((subquery.licence_number)::text, 6, '0'::text)) || '-'::text) || lpad((subquery.pipeline_licence_line_no)::text, 4, '0'::text))))
  • Rows Removed by Join Filter: 12,375,632
14. 45.685 309.665 ↑ 19.0 4,777 1

Nested Loop (cost=0.71..118,525.42 rows=90,596 width=9,550) (actual time=0.059..309.665 rows=4,777 loops=1)

  • Join Filter: (incidents.pipeline_id = ps.pipeline_id)
15. 61.220 182.771 ↑ 21.8 4,777 1

Nested Loop (cost=0.29..54,841.65 rows=103,964 width=5,188) (actual time=0.040..182.771 rows=4,777 loops=1)

16. 15.811 15.811 ↑ 22.6 5,287 1

Seq Scan on pipelines p (cost=0.00..10,216.05 rows=119,305 width=3,480) (actual time=0.013..15.811 rows=5,287 loops=1)

17. 105.740 105.740 ↑ 1.0 1 5,287

Index Scan using incidents_pipeline_id_index on incidents (cost=0.29..0.36 rows=1 width=1,708) (actual time=0.018..0.020 rows=1 loops=5,287)

  • Index Cond: (pipeline_id = p.id)
18. 81.209 81.209 ↑ 1.0 1 4,777

Index Scan using pipeline_stats_pipeline_id_index on pipeline_stats ps (cost=0.42..0.60 rows=1 width=4,362) (actual time=0.012..0.017 rows=1 loops=4,777)

  • Index Cond: (pipeline_id = p.id)
19. 17,703.562 17,703.562 ↑ 1.8 2,591 4,777

CTE Scan on subquery (cost=0.00..92.44 rows=4,622 width=52) (actual time=0.032..3.706 rows=2,591 loops=4,777)

20. 0.183 0.200 ↑ 70.0 1 100

Materialize (cost=0.00..11.05 rows=70 width=1,036) (actual time=0.002..0.002 rows=1 loops=100)

21. 0.017 0.017 ↑ 70.0 1 1

Seq Scan on provinces (cost=0.00..10.70 rows=70 width=1,036) (actual time=0.016..0.017 rows=1 loops=1)

Planning time : 2.964 ms