explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yLKe

Settings
# exclusive inclusive rows x rows loops node
1. 9.335 588.819 ↑ 21.2 7,237 1

Unique (cost=632,195.56..642,169.42 rows=153,444 width=4,325) (actual time=575.236..588.819 rows=7,237 loops=1)

2. 28.550 579.484 ↑ 21.2 7,238 1

Sort (cost=632,195.56..632,579.17 rows=153,444 width=4,325) (actual time=575.235..579.484 rows=7,238 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 sort Disk: 5448kB
3. 0.831 550.934 ↑ 21.2 7,238 1

Append (cost=14,101.56..48,352.39 rows=153,444 width=4,325) (actual time=383.585..550.934 rows=7,238 loops=1)

4. 47.741 534.311 ↑ 4,718.6 31 1

Hash Join (cost=14,101.56..37,044.56 rows=146,277 width=533) (actual time=383.584..534.311 rows=31 loops=1)

  • Hash Cond: (locations."npoId" = "NonProfitOrganization".id)
5. 170.426 249.820 ↑ 4,718.6 31 1

Seq Scan on "npoLocations" locations (cost=1,134.56..15,617.83 rows=146,277 width=4) (actual time=86.881..249.820 rows=31 loops=1)

  • Filter: ((("zipCode")::text = '20000'::text) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 292520
6.          

SubPlan (forSeq Scan)

7. 74.798 79.394 ↑ 99.5 2 1

Hash Left Join (cost=2.33..1,134.06 rows=199 width=4) (actual time=22.093..79.394 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
8. 4.550 4.550 ↑ 1.0 29,910 1

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

9. 0.026 0.046 ↑ 1.0 59 1

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

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

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

11. 160.845 236.750 ↑ 1.0 80,000 1

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

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

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

  • Filter: (NOT "isSuspended")
13. 12.681 15.792 ↓ 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=4.117..15.792 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
14. 0.000 3.111 ↓ 0.0 0 1

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

15. 3.081 3.081 ↓ 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=3.081..3.081 rows=7,207 loops=1)

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

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

  • Index Cond: (("zipCode")::text = '20000'::text)
Planning time : 1.635 ms
Execution time : 593.446 ms