explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2dyx

Settings
# exclusive inclusive rows x rows loops node
1. 0.105 6,585.389 ↑ 1.0 1 1

Limit (cost=548,927.39..548,927.39 rows=1 width=155) (actual time=6,585.285..6,585.389 rows=1 loops=1)

2. 0.033 6,585.284 ↑ 5,371.0 1 1

Sort (cost=548,927.39..548,940.82 rows=5,371 width=155) (actual time=6,585.284..6,585.284 rows=1 loops=1)

  • Sort Key: (((((((CASE WHEN (a.street1 IS NOT NULL) THEN 3 ELSE 0 END + CASE WHEN (a.postcode IS NOT NULL) THEN 2 ELSE 0 END) + CASE WHEN (a.city IS NOT NULL) THEN 2 ELSE 0 END) + CASE WHEN (a.state IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.country IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.type IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.type = ANY ('{headquarters,headquarter}'::text[])) THEN 3 ELSE 0 END)) DESC, (count(*)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 5.450 6,585.251 ↑ 86.6 62 1

GroupAggregate (cost=548,591.70..548,900.53 rows=5,371 width=155) (actual time=6,578.601..6,585.251 rows=62 loops=1)

  • Group Key: (((((((CASE WHEN (a.street1 IS NOT NULL) THEN 3 ELSE 0 END + CASE WHEN (a.postcode IS NOT NULL) THEN 2 ELSE 0 END) + CASE WHEN (a.city IS NOT NULL) THEN 2 ELSE 0 END) + CASE WHEN (a.state IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.country IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.type IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.type = ANY ('{headquarters,headquarter}'::text[])) THEN 3 ELSE 0 END)), a.street1, a.street2, a.street3, a.city, a.state, a.postcode, a.county, a.country, a.fullstring
4. 16.999 6,579.801 ↓ 3.5 18,964 1

Sort (cost=548,591.70..548,605.13 rows=5,371 width=147) (actual time=6,578.253..6,579.801 rows=18,964 loops=1)

  • Sort Key: (((((((CASE WHEN (a.street1 IS NOT NULL) THEN 3 ELSE 0 END + CASE WHEN (a.postcode IS NOT NULL) THEN 2 ELSE 0 END) + CASE WHEN (a.city IS NOT NULL) THEN 2 ELSE 0 END) + CASE WHEN (a.state IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.country IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.type IS NOT NULL) THEN 1 ELSE 0 END) + CASE WHEN (a.type = ANY ('{headquarters,headquarter}'::text[])) THEN 3 ELSE 0 END)) DESC, a.street1, a.street2, a.street3, a.city, a.state, a.postcode, a.county, a.country, a.fullstring
  • Sort Method: quicksort Memory: 2919kB
5. 50.689 6,562.802 ↓ 3.5 18,964 1

Gather (cost=284,742.97..548,258.94 rows=5,371 width=147) (actual time=4,854.759..6,562.802 rows=18,964 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 22.771 6,512.113 ↓ 2.8 3,793 5

Nested Loop (cost=283,742.97..546,651.35 rows=1,343 width=147) (actual time=4,862.826..6,512.113 rows=3,793 loops=5)

7. 1,291.330 6,489.338 ↓ 2.8 3,793 5

Hash Join (cost=283,742.53..545,950.69 rows=1,343 width=8) (actual time=4,862.774..6,489.338 rows=3,793 loops=5)

  • Hash Cond: (ca.companyid = co.id)
8. 336.934 336.934 ↑ 1.2 4,619,719 5

Parallel Seq Scan on companyaddress ca (cost=0.00..240,613.08 rows=5,755,108 width=16) (actual time=0.013..336.934 rows=4,619,719 loops=5)

9. 78.664 4,861.074 ↓ 1.6 310,688 5

Hash (cost=281,361.34..281,361.34 rows=190,495 width=4) (actual time=4,861.074..4,861.074 rows=310,688 loops=5)

  • Buckets: 262144 (originally 262144) Batches: 2 (originally 1) Memory Usage: 8193kB
10. 4,689.263 4,782.410 ↓ 1.6 310,688 5

Bitmap Heap Scan on company co (cost=3,356.54..281,361.34 rows=190,495 width=4) (actual time=117.622..4,782.410 rows=310,688 loops=5)

  • Recheck Cond: (lower(name) = 'bank of america'::text)
  • Rows Removed by Index Recheck: 6902302
  • Heap Blocks: exact=79232 lossy=171092
11. 93.147 93.147 ↓ 1.6 311,337 5

Bitmap Index Scan on idx_company_name_lower (cost=0.00..3,308.91 rows=190,495 width=0) (actual time=93.147..93.147 rows=311,337 loops=5)

  • Index Cond: (lower(name) = 'bank of america'::text)
12. 0.004 0.004 ↑ 1.0 1 18,964

Index Scan using address_pkey on address a (cost=0.44..0.49 rows=1 width=162) (actual time=0.004..0.004 rows=1 loops=18,964)

  • Index Cond: (id = ca.addressid)
Planning time : 0.849 ms
Execution time : 6,587.143 ms