explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SnxH

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

Limit (cost=98,891.79..98,891.80 rows=2 width=545) (actual time=827.655..827.655 rows=0 loops=1)

2.          

CTE mainsearch

3. 0.001 827.542 ↓ 0.0 0 1

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

4. 0.002 827.541 ↓ 0.0 0 1

Sort (cost=98,888.24..98,888.24 rows=1 width=318) (actual time=827.541..827.541 rows=0 loops=1)

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

WindowAgg (cost=1,000.00..98,888.23 rows=1 width=318) (actual time=827.538..827.539 rows=0 loops=1)

6. 17.046 841.411 ↓ 0.0 0 1

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

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

Parallel Seq Scan on search_address (cost=0.00..97,888.13 rows=1 width=290) (actual time=824.365..824.365 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.002 0.076 ↓ 0.0 0 1

Limit (cost=3.06..3.48 rows=1 width=510) (actual time=0.075..0.076 rows=0 loops=1)

10. 0.000 0.074 ↓ 0.0 0 1

Result (cost=3.06..3.48 rows=1 width=510) (actual time=0.074..0.074 rows=0 loops=1)

11. 0.008 0.074 ↓ 0.0 0 1

Sort (cost=3.06..3.06 rows=1 width=566) (actual time=0.074..0.074 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.066 ↓ 0.0 0 1

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

13. 0.065 0.065 ↓ 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.065..0.065 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.000 827.649 ↓ 0.0 0 1

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

16. 0.026 827.649 ↓ 0.0 0 1

Sort (cost=0.03..0.03 rows=2 width=545) (actual time=827.649..827.649 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.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.003 827.623 ↓ 0.0 0 1

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

18. 827.543 827.543 ↓ 0.0 0 1

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

19. 0.000 0.077 ↓ 0.0 0 1

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

20. 0.077 0.077 ↓ 0.0 0 1

CTE Scan on recentsearch (cost=0.01..0.01 rows=1 width=545) (actual time=0.076..0.077 rows=0 loops=1)

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

SubPlan (for CTE 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.003 827.654 ↓ 0.0 0 1

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

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

CTE Scan on unionedsearch (cost=0.00..0.01 rows=2 width=545) (actual time=827.650..827.651 rows=0 loops=1)

Planning time : 0.984 ms
Execution time : 841.773 ms