#	exclusive	inclusive	rows x	rows	loops	node
1.	0.567	824.533	↑ 126.8	724	1	→ Hash Left Join (cost=152,675.70..175,335.56 rows=91,809 width=191) (actual time=677.042..824.533 rows=724 loops=1) Hash Cond: (site.sid = rgf.site_sid)
2.						CTE _resource_group_sites
3.	0.001	0.001	↓ 0.0	0	1	→ HashAggregate (cost=0.01..0.02 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
4.	0.000	0.000	↓ 0.0	0	1	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1) One-Time Filter: false
5.						CTE _common_entities_geographic_site
6.	7.670	23.241	↑ 138.9	724	1	→ Hash Join (cost=3,638.42..71,791.18 rows=100,576 width=67) (actual time=14.265..23.241 rows=724 loops=1) Hash Cond: (site_1.organization_sid = sorg_sids.parameter)
7.	9.059	15.536	↓ 1.0	88,860	1	→ Bitmap Heap Scan on common_entities_geographic_site site_1 (cost=3,615.67..59,721.68 rows=86,596 width=75) (actual time=6.827..15.536 rows=88,860 loops=1) Recheck Cond: ((company_identity)::text = '0f0ebbee-79ab-4b43-9ef5-ba26942a0076'::text)
8.	6.477	6.477	↓ 1.0	88,862	1	→ Bitmap Index Scan on ix_common_entities_geographic_site_01 (cost=0.00..3,594.02 rows=86,596 width=0) (actual time=6.477..6.477 rows=88,862 loops=1) Index Cond: ((company_identity)::text = '0f0ebbee-79ab-4b43-9ef5-ba26942a0076'::text)
9.	0.000	0.035	↑ 250.0	4	1	→ Hash (cost=10.25..10.25 rows=1,000 width=8) (actual time=0.035..0.035 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
10.	0.035	0.035	↑ 250.0	4	1	→ Function Scan on fn_split_bigints sorg_sids (cost=0.25..10.25 rows=1,000 width=8) (actual time=0.034..0.035 rows=4 loops=1)
11.	140.344	823.963	↑ 126.8	724	1	→ Hash Join (cost=80,884.47..102,047.84 rows=91,809 width=191) (actual time=677.025..823.963 rows=724 loops=1) Hash Cond: (site.address_sid = address.sid)
12.	23.512	23.512	↑ 138.9	724	1	→ CTE Scan on _common_entities_geographic_site site (cost=0.00..2,011.52 rows=100,576 width=152) (actual time=14.269..23.512 rows=724 loops=1)
13.	323.483	660.107	↓ 1.0	1,470,295	1	→ Hash (cost=49,624.38..49,624.38 rows=1,468,327 width=47) (actual time=660.107..660.107 rows=1,470,295 loops=1) Buckets: 2048 Batches: 128 Memory Usage: 937kB
14.	336.624	336.624	↓ 1.0	1,470,295	1	→ Seq Scan on common_entities_address address (cost=0.00..49,624.38 rows=1,468,327 width=47) (actual time=0.033..336.624 rows=1,470,295 loops=1) Filter: (NOT mark_for_delete) Rows Removed by Filter: 138312
15.	0.000	0.003	↓ 0.0	0	1	→ Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 0kB
16.	0.003	0.003	↓ 0.0	0	1	→ CTE Scan on _resource_group_sites rgf (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

Hash Left Join  (cost=152675.70..175335.56 rows=91809 width=191) (actual time=677.042..824.533 rows=724 loops=1)
  Hash Cond: (site.sid = rgf.site_sid)
  CTE _resource_group_sites
    ->  HashAggregate  (cost=0.01..0.02 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
                One-Time Filter: false
  CTE _common_entities_geographic_site
    ->  Hash Join  (cost=3638.42..71791.18 rows=100576 width=67) (actual time=14.265..23.241 rows=724 loops=1)
          Hash Cond: (site_1.organization_sid = sorg_sids.parameter)
          ->  Bitmap Heap Scan on common_entities_geographic_site site_1  (cost=3615.67..59721.68 rows=86596 width=75) (actual time=6.827..15.536 rows=88860 loops=1)
                Recheck Cond: ((company_identity)::text = '0f0ebbee-79ab-4b43-9ef5-ba26942a0076'::text)
                ->  Bitmap Index Scan on ix_common_entities_geographic_site_01  (cost=0.00..3594.02 rows=86596 width=0) (actual time=6.477..6.477 rows=88862 loops=1)
                      Index Cond: ((company_identity)::text = '0f0ebbee-79ab-4b43-9ef5-ba26942a0076'::text)
          ->  Hash  (cost=10.25..10.25 rows=1000 width=8) (actual time=0.035..0.035 rows=4 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                ->  Function Scan on fn_split_bigints sorg_sids  (cost=0.25..10.25 rows=1000 width=8) (actual time=0.034..0.035 rows=4 loops=1)
  ->  Hash Join  (cost=80884.47..102047.84 rows=91809 width=191) (actual time=677.025..823.963 rows=724 loops=1)
        Hash Cond: (site.address_sid = address.sid)
        ->  CTE Scan on _common_entities_geographic_site site  (cost=0.00..2011.52 rows=100576 width=152) (actual time=14.269..23.512 rows=724 loops=1)
        ->  Hash  (cost=49624.38..49624.38 rows=1468327 width=47) (actual time=660.107..660.107 rows=1470295 loops=1)
              Buckets: 2048  Batches: 128  Memory Usage: 937kB
              ->  Seq Scan on common_entities_address address  (cost=0.00..49624.38 rows=1468327 width=47) (actual time=0.033..336.624 rows=1470295 loops=1)
                    Filter: (NOT mark_for_delete)
                    Rows Removed by Filter: 138312
  ->  Hash  (cost=0.02..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 0kB
        ->  CTE Scan on _resource_group_sites rgf  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
Total runtime: 824.670 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	9.059 ms	1.1 %
Bitmap Index Scan	1	6.477 ms	0.8 %
CTE Scan	2	23.515 ms	2.9 %
Function Scan	1	0.035 ms	0.0 %
Hash	3	323.483 ms	39.2 %
Hash Join	2	148.014 ms	18.0 %
Hash Left Join	1	0.567 ms	0.1 %
HashAggregate	1	0.001 ms	0.0 %
Result	1	0.000 ms	0.0 %
Seq Scan	1	336.624 ms	40.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
common_entities_address	1	336.624 ms	40.8 %
Seq Scan	1	336.624 ms	100.0 %
common_entities_geographic_site	1	9.059 ms	1.1 %
Bitmap Heap Scan	1	9.059 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kBQ7 : core fleetview

Color mode:

Visible columns:

Per node type stats

Per table stats