explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qQv

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 55.218 ↓ 5.0 5 1

Limit (cost=10.28..556.85 rows=1 width=247) (actual time=2.452..55.218 rows=5 loops=1)

2.          

CTE accountids

3. 0.182 0.182 ↑ 1.0 763 1

Values Scan on "*VALUES*" (cost=0.00..2.67 rows=763 width=32) (actual time=0.002..0.182 rows=763 loops=1)

4. 0.011 55.215 ↓ 5.0 5 1

Nested Loop Semi Join (cost=7.61..554.18 rows=1 width=247) (actual time=2.450..55.215 rows=5 loops=1)

5. 1.571 45.598 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.25..48.24 rows=1 width=248) (actual time=0.269..45.598 rows=6 loops=1)

  • Filter: (CASE WHEN ("case".location__c IS NULL) THEN ("case".unlisted_location__c)::text ELSE concat(b.street_address__c, ', ', b.city__c, ', ', b.state__c, ', ', b.postal_code__c) END ~~ 'Salmon%'::text)
  • Rows Removed by Filter: 4098
6. 7.091 7.091 ↓ 4,104.0 4,104 1

Index Scan Backward using case_multi_column_idx on "case" (cost=0.08..43.95 rows=1 width=197) (actual time=0.014..7.091 rows=4,104 loops=1)

  • Index Cond: (primary_date__c >= '2019-02-22'::date)
  • Filter: ((CASE WHEN ((closure_code__c)::text = 'Cancelled'::text) THEN closure_code__c ELSE status END)::text = ANY ('{Scheduled,"Maintenance Started","Maintenance Extended","Maintenance Stopped","Maintenance Completed",Closed,Cancelled}'::text[]))
  • Rows Removed by Filter: 14
7. 4.361 36.936 ↓ 0.0 0 4,104

Nested Loop Left Join (cost=0.17..4.29 rows=1 width=70) (actual time=0.009..0.009 rows=0 loops=4,104)

8. 16.416 16.416 ↓ 0.0 0 4,104

Index Scan using hcu_idx_location__c_sfid on location__c l (cost=0.09..4.09 rows=1 width=38) (actual time=0.004..0.004 rows=0 loops=4,104)

  • Index Cond: (("case".location__c)::text = (sfid)::text)
9. 16.159 16.159 ↑ 1.0 1 1,469

Index Scan using hcu_idx_building__c_sfid on building__c b (cost=0.09..0.20 rows=1 width=70) (actual time=0.011..0.011 rows=1 loops=1,469)

  • Index Cond: ((l.building__c)::text = (sfid)::text)
10. 0.022 9.606 ↑ 73.0 1 6

Hash Semi Join (cost=7.36..505.72 rows=73 width=19) (actual time=1.601..1.601 rows=1 loops=6)

  • Hash Cond: ((maintenance_impact__c.account__c)::text = accountids.accountid)
11. 9.132 9.132 ↑ 246.0 1 6

Index Scan using maintenance_impact__c_multi_column_idx on maintenance_impact__c (cost=0.11..496.74 rows=246 width=38) (actual time=1.521..1.522 rows=1 loops=6)

  • Index Cond: ((case__c)::text = ("case".sfid)::text)
  • Filter: (((account__c IS NOT NULL) OR (corrected_account_name__c IS NOT NULL)) AND (NOT isdeleted))
12. 0.106 0.452 ↑ 1.0 763 1

Hash (cost=4.58..4.58 rows=763 width=32) (actual time=0.452..0.452 rows=763 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
13. 0.346 0.346 ↑ 1.0 763 1

CTE Scan on accountids (cost=0.00..4.58 rows=763 width=32) (actual time=0.004..0.346 rows=763 loops=1)

Planning time : 2.154 ms