explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 65gk

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 830.238 ↓ 0.0 0 1

Limit (cost=98,892.20..98,892.21 rows=2 width=549) (actual time=830.238..830.238 rows=0 loops=1)

2.          

CTE mainsearch

3. 0.000 830.163 ↓ 0.0 0 1

Limit (cost=98,888.24..98,888.24 rows=1 width=322) (actual time=830.163..830.163 rows=0 loops=1)

4. 0.004 830.163 ↓ 0.0 0 1

Sort (cost=98,888.24..98,888.24 rows=1 width=322) (actual time=830.162..830.163 rows=0 loops=1)

  • Sort Key: search_address.tranzact_on_zayo_network_status__c
  • Sort Method: quicksort Memory: 25kB
5. 0.000 830.159 ↓ 0.0 0 1

WindowAgg (cost=1,000.00..98,888.23 rows=1 width=322) (actual time=830.159..830.159 rows=0 loops=1)

6. 19.948 845.217 ↓ 0.0 0 1

Gather (cost=1,000.00..98,888.23 rows=1 width=290) (actual time=830.157..845.217 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 825.269 825.269 ↓ 0.0 0 2

Parallel Seq Scan on search_address (cost=0.00..97,888.13 rows=1 width=290) (actual time=825.269..825.269 rows=0 loops=2)

  • Filter: ((lower((city__c)::text) ~~ '%45678iyd99cwwercni1%'::text) AND (lower((state__c)::text) ~~ '%Cali%'::text) AND (lower((postal_code__c)::text) ~~ '%8732%'::text))
  • Rows Removed by Filter: 994518
8.          

CTE recentsearch

9. 0.001 0.031 ↓ 0.0 0 1

Limit (cost=3.06..3.88 rows=1 width=514) (actual time=0.031..0.031 rows=0 loops=1)

10. 0.001 0.030 ↓ 0.0 0 1

Result (cost=3.06..3.88 rows=1 width=514) (actual time=0.030..0.030 rows=0 loops=1)

11. 0.008 0.029 ↓ 0.0 0 1

Sort (cost=3.06..3.06 rows=1 width=566) (actual time=0.029..0.029 rows=0 loops=1)

  • Sort Key: (COALESCE(a.tranzact_on_zayo_network_status__c, ''::character varying))
  • Sort Method: quicksort Memory: 25kB
12. 0.001 0.021 ↓ 0.0 0 1

WindowAgg (cost=0.09..3.06 rows=1 width=566) (actual time=0.021..0.021 rows=0 loops=1)

13. 0.020 0.020 ↓ 0.0 0 1

Index Scan using hc_idx_building__c_createddate on building__c a (cost=0.09..3.05 rows=1 width=218) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (createddate > (CURRENT_DATE - 2))
  • Filter: ((lower((COALESCE(city__c, ''::character varying))::text) ~~ '%45678iyd99cwwercni1%'::text) AND (lower((COALESCE(state__c, ''::character varying))::text) ~~ '%Cali%'::text) AND (lower((COALESCE(postal_code__c, ''::character varying))::text) ~~ '%8732%'::text))
14.          

CTE unionedsearch

15. 0.001 830.229 ↓ 0.0 0 1

Unique (cost=0.03..0.07 rows=2 width=549) (actual time=830.229..830.229 rows=0 loops=1)

16. 0.028 830.228 ↓ 0.0 0 1

Sort (cost=0.03..0.03 rows=2 width=549) (actual time=830.228..830.228 rows=0 loops=1)

  • Sort Key: mainsearch_1.record_count, mainsearch_1.address_full, mainsearch_1.building_riser_diagram__c, mainsearch_1.city__c, mainsearch_1.clli_code__c, mainsearch_1.country__c, mainsearch_1.entrance__c, mainsearch_1.exclude_from_mapping__c, mainsearch_1.floor_suite_room_cage__c, mainsearch_1.id, mainsearch_1.latitude__c, mainsearch_1.longitude__c, mainsearch_1.postal_code__c, mainsearch_1.state__c, mainsearch_1.street_address__c, mainsearch_1.tranzact_building_name_display__c, mainsearch_1.tranzact_on_zayo_network_status__c, mainsearch_1.type__c, mainsearch_1.search_document, mainsearch_1.searchrank, mainsearch_1.dark_fiber_point_to_point, mainsearch_1.dark_fiber_ring, mainsearch_1.ethernet_elan, mainsearch_1.ethernet_eline, mainsearch_1.ethernet_flexconnect, mainsearch_1.ethernet_pdn, mainsearch_1.ip_services_dia, mainsearch_1.ip_services_ip_vpn, mainsearch_1.live_video_4k, mainsearch_1.live_video_asi, mainsearch_1.live_video_ethernet, mainsearch_1.live_video_j2k, mainsearch_1.live_video_sdi, mainsearch_1.private_line, mainsearch_1.standard_wavelength, mainsearch_1.zcolo_cross_connects, mainsearch_1.zcolo_intra_building_riser, mainsearch_1.zcolo_space_power
  • Sort Method: quicksort Memory: 25kB
17. 0.001 830.200 ↓ 0.0 0 1

Append (cost=0.00..0.03 rows=2 width=549) (actual time=830.200..830.200 rows=0 loops=1)

18. 830.165 830.165 ↓ 0.0 0 1

CTE Scan on mainsearch mainsearch_1 (cost=0.00..0.01 rows=1 width=549) (actual time=830.165..830.165 rows=0 loops=1)

19. 0.002 0.034 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.01..0.02 rows=1 width=549) (actual time=0.034..0.034 rows=0 loops=1)

20. 0.032 0.032 ↓ 0.0 0 1

CTE Scan on recentsearch (cost=0.01..0.01 rows=1 width=549) (actual time=0.032..0.032 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 3))
21.          

SubPlan (forCTE Scan)

22. 0.000 0.000 ↓ 0.0 0

CTE Scan on mainsearch (cost=0.00..0.01 rows=1 width=54) (never executed)

23. 0.007 830.237 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=2 width=549) (actual time=830.237..830.237 rows=0 loops=1)

  • Sort Key: unionedsearch.tranzact_on_zayo_network_status__c
  • Sort Method: quicksort Memory: 25kB
24. 830.230 830.230 ↓ 0.0 0 1

CTE Scan on unionedsearch (cost=0.00..0.01 rows=2 width=549) (actual time=830.230..830.230 rows=0 loops=1)

Planning time : 1.234 ms
Execution time : 845.553 ms