explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rGax

Settings
# exclusive inclusive rows x rows loops node
1. 0.522 91,095.594 ↓ 230.0 230 1

Nested Loop (cost=227,138.27..227,146.35 rows=1 width=180) (actual time=91,094.559..91,095.594 rows=230 loops=1)

2. 292.486 91,094.842 ↓ 230.0 230 1

HashAggregate (cost=227,138.12..227,138.14 rows=1 width=88) (actual time=91,094.533..91,094.842 rows=230 loops=1)

  • Group Key: a_leads_12.store_id
3. 54.270 90,802.356 ↓ 47,437.0 47,437 1

Nested Loop Semi Join (cost=195,282.18..227,138.00 rows=1 width=88) (actual time=824.890..90,802.356 rows=47,437 loops=1)

4. 83.685 90,555.858 ↓ 48,057.0 48,057 1

Nested Loop (cost=195,282.04..227,137.81 rows=1 width=88) (actual time=824.868..90,555.858 rows=48,057 loops=1)

5. 18,405.583 90,231.888 ↓ 48,057.0 48,057 1

Nested Loop (cost=195,281.61..227,136.41 rows=1 width=92) (actual time=824.851..90,231.888 rows=48,057 loops=1)

  • Join Filter: (a_leads_12.service_id = a_services_114.id)
  • Rows Removed by Join Filter: 82754154
6. 581.580 990.287 ↓ 48,057.0 48,057 1

Hash Right Join (cost=195,280.41..227,001.18 rows=1 width=38) (actual time=824.564..990.287 rows=48,057 loops=1)

  • Hash Cond: (a_lead_assoc_115.child_lead_id = a_leads_12.id)
  • Filter: (a_lead_assoc_115.parent_lead_id IS NULL)
  • Rows Removed by Filter: 50734
7. 282.785 282.785 ↑ 1.0 1,002,087 1

Seq Scan on lead_assoc a_lead_assoc_115 (cost=0.00..15,443.02 rows=1,002,402 width=8) (actual time=0.027..282.785 rows=1,002,087 loops=1)

8. 45.271 125.922 ↓ 1.4 98,791 1

Hash (cost=193,838.58..193,838.58 rows=70,946 width=38) (actual time=125.922..125.922 rows=98,791 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 3549kB
9. 69.510 80.651 ↓ 1.4 98,791 1

Bitmap Heap Scan on leads a_leads_12 (cost=8,370.54..193,838.58 rows=70,946 width=38) (actual time=13.131..80.651 rows=98,791 loops=1)

  • Recheck Cond: ((book_date >= '2019-09-01 00:00:00-04'::timestamp with time zone) AND (book_date <= '2019-10-31 23:59:59-04'::timestamp with time zone))
  • Filter: (client_id = ANY ('{1,2,1826,1863,1864,1865,1906}'::integer[]))
  • Heap Blocks: exact=12567
10. 11.141 11.141 ↓ 1.0 99,891 1

Bitmap Index Scan on leads_book_date_idx (cost=0.00..8,352.81 rows=99,238 width=0) (actual time=11.141..11.141 rows=99,891 loops=1)

  • Index Cond: ((book_date >= '2019-09-01 00:00:00-04'::timestamp with time zone) AND (book_date <= '2019-10-31 23:59:59-04'::timestamp with time zone))
11. 50,748.182 70,836.018 ↓ 1.0 1,723 48,057

Hash Join (cost=1.20..113.84 rows=1,711 width=62) (actual time=0.002..1.474 rows=1,723 loops=48,057)

  • Hash Cond: (a_services_114.service_type_id = t.id)
12. 20,087.826 20,087.826 ↓ 1.0 1,723 48,057

Seq Scan on services a_services_114 (cost=0.00..89.11 rows=1,711 width=8) (actual time=0.001..0.418 rows=1,723 loops=48,057)

13. 0.004 0.010 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=62) (actual time=0.010..0.010 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
14. 0.006 0.006 ↑ 1.0 9 1

Seq Scan on service_types t (cost=0.00..1.09 rows=9 width=62) (actual time=0.003..0.006 rows=9 loops=1)

15. 240.285 240.285 ↑ 1.0 1 48,057

Index Scan using lead_owner_idx on lead_owner a_lead_owner_13 (cost=0.43..1.39 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=48,057)

  • Index Cond: (lead_id = a_leads_12.id)
  • Filter: (main = 1)
  • Rows Removed by Filter: 0
16. 192.228 192.228 ↑ 1.0 1 48,057

Index Only Scan using lead_statuses_name_red_flag_idx on lead_statuses a_lead_statuses_2 (cost=0.14..0.18 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=48,057)

  • Index Cond: (name = (a_leads_12.status)::text)
  • Filter: (((name)::text <> 'DRAFT'::text) AND ((name)::text <> ALL ('{CANCELED_BF_ACK,CONFIRMATION}'::text[])) AND ((name)::text <> ALL ('{CCCANCEL,CCABANDONED,UNKNOWN}'::text[])) AND ((name)::text <> ALL ('{PENDING_CONFIRMATION,PENDING_RESCHEDULE,DELIVERY_NOT_SET,DELIVERY_ESTIMATED,WORK_NOT_SCHEDULED,IN_PROGRESS_INST,NOT_SCHEDULED_INST}'::text[])))
  • Rows Removed by Filter: 0
  • Heap Fetches: 48057
17. 0.230 0.230 ↑ 1.0 1 230

Index Scan using stores_pkey on stores s (cost=0.14..8.16 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=230)

  • Index Cond: (id = a_leads_12.store_id)
Planning time : 1.685 ms
Execution time : 91,096.086 ms