explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 82Dx

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 88,247.189 ↓ 0.0 0 1

Merge Join (cost=12,431,070.46..12,757,151.50 rows=21,724,501 width=207) (actual time=88,247.189..88,247.189 rows=0 loops=1)

  • Merge Cond: ("foundGrants".id = "grantToVehicle"."grantId")
2.          

CTE foundVehicles

3. 398.846 398.846 ↑ 455.0 1 1

Seq Scan on vehicles (cost=0.00..85,906.46 rows=455 width=19) (actual time=195.579..398.846 rows=1 loops=1)

  • Filter: (vrp ~~ '%A123AA45%'::text)
  • Rows Removed by Filter: 4,545,710
4.          

CTE foundGrants

5. 1,450.519 2,272.885 ↓ 55.8 2,393,460 1

Sort (cost=54,604.98..54,712.27 rows=42,915 width=107) (actual time=1,915.394..2,272.885 rows=2,393,460 loops=1)

  • Sort Key: grants.id DESC
  • Sort Method: external merge Disk: 300,744kB
6. 298.894 822.366 ↓ 55.8 2,393,460 1

Nested Loop (cost=5,922.80..51,302.84 rows=42,915 width=107) (actual time=47.820..822.366 rows=2,393,460 loops=1)

7. 0.164 0.164 ↓ 12.0 12 1

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

  • Filter: (target = 'benefits'::text)
  • Rows Removed by Filter: 38
8. 391.944 523.308 ↓ 1.2 199,455 12

Bitmap Heap Scan on grants (cost=5,922.80..49,581.54 rows=171,660 width=42) (actual time=11.961..43.609 rows=199,455 loops=12)

  • Recheck Cond: ("benefitId" = benefits.id)
  • Heap Blocks: exact=88,600
9. 131.364 131.364 ↓ 1.3 222,192 12

Bitmap Index Scan on "grants_benefitId" (cost=0.00..5,879.88 rows=171,660 width=0) (actual time=10.947..10.947 rows=222,192 loops=12)

  • Index Cond: ("benefitId" = benefits.id)
10. 831.902 3,686.246 ↑ 42,915.0 1 1

Sort (cost=4,160.44..4,267.72 rows=42,915 width=107) (actual time=3,686.246..3,686.246 rows=1 loops=1)

  • Sort Key: "foundGrants".id
  • Sort Method: external merge Disk: 300,736kB
11. 2,854.344 2,854.344 ↓ 55.8 2,393,460 1

CTE Scan on "foundGrants" (cost=0.00..858.30 rows=42,915 width=107) (actual time=1,915.398..2,854.344 rows=2,393,460 loops=1)

12. 0.011 84,560.938 ↓ 0.0 0 1

Sort (cost=12,286,291.29..12,286,544.40 rows=101,244 width=104) (actual time=84,560.938..84,560.938 rows=0 loops=1)

  • Sort Key: "grantToVehicle"."grantId
  • Sort Method: quicksort Memory: 25kB
13. 0.008 84,560.927 ↓ 0.0 0 1

Hash Join (cost=2,926,373.79..12,277,874.13 rows=101,244 width=104) (actual time=84,560.927..84,560.927 rows=0 loops=1)

  • Hash Cond: ((first("grantToVehicle"."vehicleId")) = "foundVehicles"."vehicleId")
14. 0.002 84,162.060 ↓ 0.0 0 1

GroupAggregate (cost=2,926,359.00..12,273,731.69 rows=44,503 width=44) (actual time=84,162.060..84,162.060 rows=0 loops=1)

  • Group Key: "grantToVehicle"."grantId
15. 0.012 84,162.058 ↓ 0.0 0 1

Sort (cost=2,926,359.00..2,972,401.88 rows=18,417,153 width=17) (actual time=84,162.058..84,162.058 rows=0 loops=1)

  • Sort Key: "grantToVehicle"."grantId
  • Sort Method: quicksort Memory: 25kB
16. 3,978.161 84,162.046 ↓ 0.0 0 1

Hash Join (cost=980.89..326,211.80 rows=18,417,153 width=17) (actual time=84,162.046..84,162.046 rows=0 loops=1)

  • Hash Cond: ("grantToVehicle"."vehicleId" = "foundVehicles_1"."vehicleId")
17. 8,780.454 80,183.877 ↓ 2.0 72,605,065 1

Nested Loop (cost=966.16..24,616.19 rows=36,834,306 width=17) (actual time=1,149.611..80,183.877 rows=72,605,065 loops=1)

18. 1,711.846 1,993.083 ↓ 11,967.3 2,393,460 1

HashAggregate (cost=965.59..967.59 rows=200 width=4) (actual time=1,148.624..1,993.083 rows=2,393,460 loops=1)

  • Group Key: "foundGrants_1".id
19. 281.237 281.237 ↓ 55.8 2,393,460 1

CTE Scan on "foundGrants" "foundGrants_1" (cost=0.00..858.30 rows=42,915 width=4) (actual time=0.035..281.237 rows=2,393,460 loops=1)

20. 69,410.340 69,410.340 ↑ 55.2 30 2,393,460

Index Scan using "grantToVehicle_grantId" on "grantToVehicle" (cost=0.57..101.69 rows=1,655 width=17) (actual time=0.014..0.029 rows=30 loops=2,393,460)

  • Index Cond: ("grantId" = "foundGrants_1".id)
21. 0.001 0.008 ↑ 200.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.005 0.007 ↑ 200.0 1 1

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

  • Group Key: "foundVehicles_1"."vehicleId
23. 0.002 0.002 ↑ 455.0 1 1

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

24. 0.007 398.859 ↑ 455.0 1 1

Hash (cost=9.10..9.10 rows=455 width=68) (actual time=398.859..398.859 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 398.852 398.852 ↑ 455.0 1 1

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