explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8tOK

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 299,601.642 ↓ 0.0 0 1

Limit (cost=85,919.24..104,344.73 rows=20 width=207) (actual time=299,601.642..299,601.642 rows=0 loops=1)

2.          

CTE foundVehicles

3. 520.455 520.455 ↑ 455.0 1 1

Seq Scan on vehicles (cost=0.00..85,903.50 rows=455 width=19) (actual time=254.234..520.455 rows=1 loops=1)

  • Filter: (vrp ~~ '%A123AA45%'::text)
  • Rows Removed by Filter: 4,545,691
4. 0.012 299,601.637 ↓ 0.0 0 1

GroupAggregate (cost=15.73..6,858,905.47 rows=7,445 width=207) (actual time=299,601.637..299,601.637 rows=0 loops=1)

  • Group Key: grants.id
5. 16,711.744 299,601.625 ↓ 0.0 0 1

Nested Loop (cost=15.73..6,834,560.32 rows=7,445 width=180) (actual time=299,601.625..299,601.625 rows=0 loops=1)

  • Join Filter: ("foundVehicles_1"."vehicleId" = "grantToVehicle"."vehicleId")
  • Rows Removed by Join Filter: 72,604,793
6. 9,797.480 282,889.881 ↓ 55.2 72,604,793 1

Nested Loop (cost=1.00..2,335,533.34 rows=1,315,496 width=120) (actual time=3.548..282,889.881 rows=72,604,793 loops=1)

7. 2,190.536 9,813.231 ↓ 55.8 2,393,447 1

Nested Loop (cost=0.43..304,322.26 rows=42,915 width=107) (actual time=1.890..9,813.231 rows=2,393,447 loops=1)

  • Join Filter: (grants."benefitId" = benefits.id)
  • Rows Removed by Join Filter: 12,051,501
8. 7,622.695 7,622.695 ↓ 1.0 2,406,170 1

Index Scan Backward using grants_pkey on grants (cost=0.43..268,268.92 rows=2,403,242 width=42) (actual time=1.045..7,622.695 rows=2,406,170 loops=1)

9. 0.000 0.000 ↓ 6.0 6 2,406,170

Materialize (cost=0.00..4.71 rows=1 width=69) (actual time=0.000..0.000 rows=6 loops=2,406,170)

10. 0.797 0.797 ↓ 12.0 12 1

Seq Scan on benefits (cost=0.00..4.70 rows=1 width=69) (actual time=0.451..0.797 rows=12 loops=1)

  • Filter: (target = 'benefits'::text)
  • Rows Removed by Filter: 38
11. 263,279.170 263,279.170 ↑ 55.2 30 2,393,447

Index Scan using "grantToVehicle_grantId" on "grantToVehicle" (cost=0.57..30.78 rows=1,655 width=17) (actual time=0.031..0.110 rows=30 loops=2,393,447)

  • Index Cond: ("grantId" = grants.id)
12. 0.000 0.000 ↑ 228.0 1 72,604,793

Materialize (cost=14.74..31.23 rows=228 width=72) (actual time=0.000..0.000 rows=1 loops=72,604,793)

13. 0.054 520.582 ↑ 228.0 1 1

Hash Join (cost=14.74..30.09 rows=228 width=72) (actual time=520.579..520.582 rows=1 loops=1)

  • Hash Cond: ("foundVehicles"."vehicleId" = "foundVehicles_1"."vehicleId")
14. 254.242 254.242 ↑ 455.0 1 1

CTE Scan on "foundVehicles" (cost=0.00..9.10 rows=455 width=68) (actual time=254.241..254.242 rows=1 loops=1)

15. 0.036 266.286 ↑ 200.0 1 1

Hash (cost=12.24..12.24 rows=200 width=4) (actual time=266.286..266.286 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.026 266.250 ↑ 200.0 1 1

HashAggregate (cost=10.24..12.24 rows=200 width=4) (actual time=266.249..266.250 rows=1 loops=1)

  • Group Key: "foundVehicles_1"."vehicleId
17. 266.224 266.224 ↑ 455.0 1 1

CTE Scan on "foundVehicles" "foundVehicles_1" (cost=0.00..9.10 rows=455 width=4) (actual time=0.002..266.224 rows=1 loops=1)

Planning time : 1.786 ms
Execution time : 299,601.977 ms