explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YIcp

Settings
# exclusive inclusive rows x rows loops node
1. 0.521 129,522.635 ↓ 230.0 230 1

Nested Loop (cost=227,268.12..227,276.21 rows=1 width=180) (actual time=129,521.611..129,522.635 rows=230 loops=1)

2. 321.219 129,521.884 ↓ 230.0 230 1

HashAggregate (cost=227,267.98..227,268.00 rows=1 width=88) (actual time=129,521.579..129,521.884 rows=230 loops=1)

  • Group Key: a_leads_12.store_id
3. 71.047 129,200.665 ↓ 47,437.0 47,437 1

Nested Loop Semi Join (cost=195,392.68..227,267.86 rows=1 width=88) (actual time=800.640..129,200.665 rows=47,437 loops=1)

4. 56.325 128,937.390 ↓ 48,057.0 48,057 1

Nested Loop (cost=195,392.54..227,267.67 rows=1 width=88) (actual time=800.616..128,937.390 rows=48,057 loops=1)

5. 18,387.976 128,592.723 ↓ 48,057.0 48,057 1

Nested Loop (cost=195,392.11..227,266.27 rows=1 width=92) (actual time=800.594..128,592.723 rows=48,057 loops=1)

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

Hash Right Join (cost=195,280.41..226,994.88 rows=1 width=38) (actual time=798.879..971.186 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. 277.276 277.276 ↑ 1.0 1,002,037 1

Seq Scan on lead_assoc a_lead_assoc_115 (cost=0.00..15,440.17 rows=1,002,217 width=8) (actual time=0.006..277.276 rows=1,002,037 loops=1)

8. 45.513 128.735 ↓ 1.4 98,791 1

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

  • Buckets: 8192 Batches: 2 Memory Usage: 3549kB
9. 71.384 83.222 ↓ 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.842..83.222 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.838 11.838 ↓ 1.0 99,883 1

Bitmap Index Scan on leads_book_date_idx (cost=0.00..8,352.81 rows=99,238 width=0) (actual time=11.838..11.838 rows=99,883 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. 42,001.808 109,233.561 ↓ 1.0 1,723 48,057

Hash Join (cost=111.70..250.00 rows=1,711 width=66) (actual time=0.002..2.273 rows=1,723 loops=48,057)

  • Hash Cond: (s_1.service_type_id = t.id)
12. 46,950.379 67,231.743 ↓ 1.0 1,723 48,057

Hash Join (cost=110.50..225.27 rows=1,711 width=12) (actual time=0.002..1.399 rows=1,723 loops=48,057)

  • Hash Cond: (a_services_114.id = s_1.id)
13. 20,280.054 20,280.054 ↓ 1.0 1,723 48,057

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

14. 0.551 1.310 ↓ 1.0 1,723 1

Hash (cost=89.11..89.11 rows=1,711 width=8) (actual time=1.310..1.310 rows=1,723 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
15. 0.759 0.759 ↓ 1.0 1,723 1

Seq Scan on services s_1 (cost=0.00..89.11 rows=1,711 width=8) (actual time=0.002..0.759 rows=1,723 loops=1)

16. 0.005 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
17. 0.005 0.005 ↑ 1.0 9 1

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

18. 288.342 288.342 ↑ 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.006 rows=1 loops=48,057)

  • Index Cond: (lead_id = a_leads_12.id)
  • Filter: (main = 1)
  • Rows Removed by Filter: 0
19. 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
20. 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 : 2.626 ms
Execution time : 129,523.144 ms