#	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: 82,754,154
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: 50,734
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: 8,192 Batches: 2 Memory Usage: 3,549kB
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=12,567
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: 1,024 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: 48,057
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

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=227138.27..227146.35 rows=1 width=180) (actual time=91094.559..91095.594 rows=230 loops=1)
   ->  HashAggregate  (cost=227138.12..227138.14 rows=1 width=88) (actual time=91094.533..91094.842 rows=230 loops=1)
         Group Key: a_leads_12.store_id
         ->  Nested Loop Semi Join  (cost=195282.18..227138.00 rows=1 width=88) (actual time=824.890..90802.356 rows=47437 loops=1)
               ->  Nested Loop  (cost=195282.04..227137.81 rows=1 width=88) (actual time=824.868..90555.858 rows=48057 loops=1)
                     ->  Nested Loop  (cost=195281.61..227136.41 rows=1 width=92) (actual time=824.851..90231.888 rows=48057 loops=1)
                           Join Filter: (a_leads_12.service_id = a_services_114.id)
                           Rows Removed by Join Filter: 82754154
                           ->  Hash Right Join  (cost=195280.41..227001.18 rows=1 width=38) (actual time=824.564..990.287 rows=48057 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
                                 ->  Seq Scan on lead_assoc a_lead_assoc_115  (cost=0.00..15443.02 rows=1002402 width=8) (actual time=0.027..282.785 rows=1002087 loops=1)
                                 ->  Hash  (cost=193838.58..193838.58 rows=70946 width=38) (actual time=125.922..125.922 rows=98791 loops=1)
                                       Buckets: 8192  Batches: 2  Memory Usage: 3549kB
                                       ->  Bitmap Heap Scan on leads a_leads_12  (cost=8370.54..193838.58 rows=70946 width=38) (actual time=13.131..80.651 rows=98791 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
                                             ->  Bitmap Index Scan on leads_book_date_idx  (cost=0.00..8352.81 rows=99238 width=0) (actual time=11.141..11.141 rows=99891 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))
                           ->  Hash Join  (cost=1.20..113.84 rows=1711 width=62) (actual time=0.002..1.474 rows=1723 loops=48057)
                                 Hash Cond: (a_services_114.service_type_id = t.id)
                                 ->  Seq Scan on services a_services_114  (cost=0.00..89.11 rows=1711 width=8) (actual time=0.001..0.418 rows=1723 loops=48057)
                                 ->  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
                                       ->  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)
                     ->  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=48057)
                           Index Cond: (lead_id = a_leads_12.id)
                           Filter: (main = 1)
                           Rows Removed by Filter: 0
               ->  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=48057)
                     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
   ->  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: 91096.086 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	69.510 ms	0.1 %
Bitmap Index Scan	1	11.141 ms	0.0 %
Hash	2	45.275 ms	0.0 %
Hash Join	1	50,748.182 ms	55.7 %
Hash Right Join	1	581.580 ms	0.6 %
HashAggregate	1	292.486 ms	0.3 %
Index Only Scan	1	192.228 ms	0.2 %
Index Scan	2	240.515 ms	0.3 %
Nested Loop	3	18,489.790 ms	20.3 %
Nested Loop Semi Join	1	54.270 ms	0.1 %
Seq Scan	3	20,370.617 ms	22.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
lead_assoc	1	282.785 ms	0.3 %
Seq Scan	1	282.785 ms	100.0 %
lead_owner	1	240.285 ms	0.3 %
Index Scan	1	240.285 ms	100.0 %
lead_statuses	1	192.228 ms	0.2 %
Index Only Scan	1	192.228 ms	100.0 %
leads	1	69.510 ms	0.1 %
Bitmap Heap Scan	1	69.510 ms	100.0 %
service_types	1	0.006 ms	0.0 %
Seq Scan	1	0.006 ms	100.0 %
services	1	20,087.826 ms	22.1 %
Seq Scan	1	20,087.826 ms	100.0 %
stores	1	0.230 ms	0.0 %
Index Scan	1	0.230 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rGax

Color mode:

Visible columns:

Per node type stats

Per table stats