explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WQfN

Settings
# exclusive inclusive rows x rows loops node
1. 43.062 1,438.076 ↑ 24.5 7,237 1

Unique (cost=751,483.11..763,014.76 rows=177,410 width=4,325) (actual time=1,379.865..1,438.076 rows=7,237 loops=1)

2. 315.898 1,395.014 ↑ 6.7 26,286 1

Sort (cost=751,483.11..751,926.63 rows=177,410 width=4,325) (actual time=1,379.863..1,395.014 rows=26,286 loops=1)

  • Sort Key: "NonProfitOrganization".id, "NonProfitOrganization"."balanceId", "NonProfitOrganization"."totalDonation", "NonProfitOrganization".name, "NonProfitOrganization".ein, "NonProfitOrganization".site, "NonProfitOrganization"."logoId", "NonProfitOrganization"."coverPhotoId", "NonProfitOrganization"."contactPerson", "NonProfitOrganization"."contactPhone", "NonProfitOrganization"."contactEmail", "NonProfitOrganization".address1, "NonProfitOrganization".address2, "NonProfitOrganization"."cityId", "NonProfitOrganization"."zipCode", "NonProfitOrganization".description, "NonProfitOrganization"."draftStep", "NonProfitOrganization"."isSuspended", "NonProfitOrganization"."totalCloudSpaceMB", "NonProfitOrganization"."usedCloudSpaceMB", "NonProfitOrganization"."createdAt", "NonProfitOrganization"."updatedAt", "NonProfitOrganization"."referralCode", "NonProfitOrganization"."recommendedIndex", "NonProfitOrganization".keywords
  • Sort Method: external merge Disk: 19840kB
3. 3.154 1,079.116 ↑ 6.7 26,286 1

Append (cost=15,388.98..76,265.86 rows=177,410 width=4,325) (actual time=514.626..1,079.116 rows=26,286 loops=1)

4. 50.104 819.484 ↑ 4,877.5 31 1

Hash Join (cost=15,388.98..44,648.85 rows=151,201 width=533) (actual time=514.625..819.484 rows=31 loops=1)

  • Hash Cond: (locations."npoId" = "NonProfitOrganization".id)
5. 332.779 521.508 ↑ 4,877.5 31 1

Hash Left Join (cost=2,421.98..23,121.95 rows=151,201 width=4) (actual time=128.743..521.508 rows=31 loops=1)

  • Hash Cond: (locations."cityId" = "locations->city".id)
  • Filter: (((locations."zipCode")::text = '20000'::text) OR (("locations->city".name)::text = '%test%'::text) OR (("locations->city->state".name)::text = '%test%'::text) OR (("locations->city->state".code)::text = '%test%'::text) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 292520
6. 76.819 76.819 ↑ 1.0 292,551 1

Seq Scan on "npoLocations" locations (cost=0.00..13,020.51 rows=292,551 width=14) (actual time=0.089..76.819 rows=292,551 loops=1)

7. 12.731 34.499 ↑ 1.0 29,910 1

Hash (cost=913.54..913.54 rows=29,910 width=26) (actual time=34.499..34.499 rows=29,910 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1949kB
8. 17.123 21.768 ↑ 1.0 29,910 1

Hash Left Join (cost=2.33..913.54 rows=29,910 width=26) (actual time=0.065..21.768 rows=29,910 loops=1)

  • Hash Cond: ("locations->city"."stateId" = "locations->city->state".id)
9. 4.598 4.598 ↑ 1.0 29,910 1

Seq Scan on cities "locations->city" (cost=0.00..538.10 rows=29,910 width=17) (actual time=0.009..4.598 rows=29,910 loops=1)

10. 0.023 0.047 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=17) (actual time=0.047..0.047 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
11. 0.024 0.024 ↑ 1.0 59 1

Seq Scan on states "locations->city->state" (cost=0.00..1.59 rows=59 width=17) (actual time=0.006..0.024 rows=59 loops=1)

12.          

SubPlan (forHash Left Join)

13. 73.181 77.411 ↑ 99.5 2 1

Hash Left Join (cost=2.33..1,134.06 rows=199 width=4) (actual time=21.899..77.411 rows=2 loops=1)

  • Hash Cond: (cities."stateId" = states.id)
  • Filter: (((cities.name)::text ~~* '%test%'::text) OR ((states.name)::text ~~* '%test%'::text) OR ((states.code)::text ~~* '%test%'::text))
  • Rows Removed by Filter: 29908
14. 4.183 4.183 ↑ 1.0 29,910 1

Seq Scan on cities (cost=0.00..538.10 rows=29,910 width=17) (actual time=0.016..4.183 rows=29,910 loops=1)

15. 0.020 0.047 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=17) (actual time=0.047..0.047 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.027 0.027 ↑ 1.0 59 1

Seq Scan on states (cost=0.00..1.59 rows=59 width=17) (actual time=0.009..0.027 rows=59 loops=1)

17. 167.382 247.872 ↑ 1.0 80,000 1

Hash (cost=6,498.00..6,498.00 rows=80,000 width=533) (actual time=247.872..247.872 rows=80,000 loops=1)

  • Buckets: 8192 Batches: 16 Memory Usage: 2963kB
18. 80.490 80.490 ↑ 1.0 80,000 1

Seq Scan on "nonProfitOrganizations" "NonProfitOrganization" (cost=0.00..6,498.00 rows=80,000 width=533) (actual time=0.012..80.490 rows=80,000 loops=1)

  • Filter: (NOT "isSuspended")
19. 132.882 256.478 ↓ 1.0 26,255 1

Hash Right Join (cost=10,352.98..29,842.91 rows=26,209 width=533) (actual time=29.767..256.478 rows=26,255 loops=1)

  • Hash Cond: (locations_1."npoId" = "NonProfitOrganization_1".id)
20. 94.010 94.010 ↑ 1.0 292,551 1

Seq Scan on "npoLocations" locations_1 (cost=0.00..13,020.51 rows=292,551 width=4) (actual time=0.031..94.010 rows=292,551 loops=1)

21. 13.880 29.586 ↓ 1.0 7,207 1

Hash (cost=9,773.39..9,773.39 rows=7,167 width=533) (actual time=29.586..29.586 rows=7,207 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 2871kB
22. 12.738 15.706 ↓ 1.0 7,207 1

Bitmap Heap Scan on "nonProfitOrganizations" "NonProfitOrganization_1" (cost=81.88..9,773.39 rows=7,167 width=533) (actual time=3.970..15.706 rows=7,207 loops=1)

  • Recheck Cond: ((npo_make_tsvector("NonProfitOrganization_1".*) @@ plainto_tsquery('Iure'::text)) OR (("zipCode")::text = '20000'::text))
  • Filter: (NOT "isSuspended")
  • Heap Blocks: exact=4265
23. 0.001 2.968 ↓ 0.0 0 1

BitmapOr (cost=81.88..81.88 rows=7,167 width=0) (actual time=2.968..2.968 rows=0 loops=1)

24. 2.938 2.938 ↓ 1.0 7,207 1

Bitmap Index Scan on "nonProfitOrganizations-full-text-vector" (cost=0.00..73.99 rows=7,165 width=0) (actual time=2.938..2.938 rows=7,207 loops=1)

  • Index Cond: (npo_make_tsvector("NonProfitOrganization_1".*) @@ plainto_tsquery('Iure'::text))
25. 0.029 0.029 ↓ 0.0 0 1

Bitmap Index Scan on "nonProfitOrganizations_zipCode_idx" (cost=0.00..4.31 rows=2 width=0) (actual time=0.029..0.029 rows=0 loops=1)

  • Index Cond: (("zipCode")::text = '20000'::text)
Planning time : 2.547 ms
Execution time : 1,441.720 ms