explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GCF3

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 2,381.095 ↑ 1.0 500 1

Limit (cost=85.61..85.86 rows=500 width=549) (actual time=2,381.038..2,381.095 rows=500 loops=1)

2.          

CTE mainsearch

3. 0.035 2,378.839 ↑ 1.0 500 1

Limit (cost=0.09..51.46 rows=500 width=322) (actual time=2,378.354..2,378.839 rows=500 loops=1)

4. 1,248.405 2,378.804 ↑ 3,978.1 500 1

WindowAgg (cost=0.09..204,360.88 rows=1,989,037 width=322) (actual time=2,378.353..2,378.804 rows=500 loops=1)

5. 1,130.399 1,130.399 ↑ 1.0 1,989,037 1

Index Scan using search_address_tranzact_on_zayo_network_status__c_idx on search_address (cost=0.09..196,404.73 rows=1,989,037 width=290) (actual time=1.315..1,130.399 rows=1,989,037 loops=1)

6.          

CTE recentsearch

7. 0.001 0.027 ↓ 0.0 0 1

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

8. 0.000 0.026 ↓ 0.0 0 1

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

9. 0.009 0.026 ↓ 0.0 0 1

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

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

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

11. 0.016 0.016 ↓ 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.016..0.016 rows=0 loops=1)

  • Index Cond: (createddate > (CURRENT_DATE - 2))
12.          

CTE unionedsearch

13. 0.427 2,380.681 ↑ 1.0 500 1

Unique (cost=13.00..22.77 rows=501 width=549) (actual time=2,380.225..2,380.681 rows=500 loops=1)

14. 0.799 2,380.254 ↑ 1.0 500 1

Sort (cost=13.00..13.25 rows=501 width=549) (actual time=2,380.224..2,380.254 rows=500 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: 282kB
15. 0.039 2,379.455 ↑ 1.0 500 1

Append (cost=0.00..8.51 rows=501 width=549) (actual time=2,378.359..2,379.455 rows=500 loops=1)

16. 2,379.387 2,379.387 ↑ 1.0 500 1

CTE Scan on mainsearch mainsearch_1 (cost=0.00..3.00 rows=500 width=549) (actual time=2,378.358..2,379.387 rows=500 loops=1)

17. 0.001 0.029 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=3.25..3.26 rows=1 width=549) (actual time=0.029..0.029 rows=0 loops=1)

18. 0.028 0.028 ↓ 0.0 0 1

CTE Scan on recentsearch (cost=3.25..3.26 rows=1 width=549) (actual time=0.028..0.028 rows=0 loops=1)

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

SubPlan (forCTE Scan)

20. 0.000 0.000 ↓ 0.0 0

CTE Scan on mainsearch (cost=0.00..3.00 rows=500 width=54) (never executed)

21. 0.265 2,381.063 ↑ 1.0 500 1

Sort (cost=7.50..7.75 rows=501 width=549) (actual time=2,381.037..2,381.063 rows=500 loops=1)

  • Sort Key: unionedsearch.tranzact_on_zayo_network_status__c
  • Sort Method: quicksort Memory: 282kB
22. 2,380.798 2,380.798 ↑ 1.0 500 1

CTE Scan on unionedsearch (cost=0.00..3.01 rows=501 width=549) (actual time=2,380.227..2,380.798 rows=500 loops=1)

Planning time : 1.055 ms
Execution time : 2,484.001 ms