explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Spr7

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

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

2.          

CTE mainsearch

3. 0.001 844.902 ↓ 0.0 0 1

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

4. 0.004 844.901 ↓ 0.0 0 1

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

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

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

6. 6.160 846.653 ↓ 0.0 0 1

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

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

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

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

10. 0.001 0.063 ↓ 0.0 0 1

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

11. 0.041 0.062 ↓ 0.0 0 1

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

  • Sort Key: (COALESCE(a.tranzact_on_zayo_network_status__c, ''::character varying))
  • Sort Method: quicksort Memory: 25kB
12. 0.002 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.019 0.019 ↓ 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.019..0.019 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) = '%Arizona%'::text) AND (lower((COALESCE(postal_code__c, ''::character varying))::text) = '%8732%'::text))
14.          

CTE unionedsearch

15. 0.000 844.999 ↓ 0.0 0 1

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

16. 0.027 844.999 ↓ 0.0 0 1

Sort (cost=0.03..0.03 rows=2 width=549) (actual time=844.998..844.999 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.003 844.972 ↓ 0.0 0 1

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

18. 844.902 844.902 ↓ 0.0 0 1

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

19. 0.001 0.067 ↓ 0.0 0 1

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

20. 0.066 0.066 ↓ 0.0 0 1

CTE Scan on recentsearch (cost=0.01..0.01 rows=1 width=549) (actual time=0.066..0.066 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.004 845.004 ↓ 0.0 0 1

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

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

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

Planning time : 1.243 ms
Execution time : 847.002 ms