#	exclusive	inclusive	rows x	rows	loops	node
1.	0.029	855.945	↑ 4,614.5	24	1	→ Sort (cost=364,321.58..364,598.44 rows=110,747 width=377) (actual time=855.944..855.945 rows=24 loops=1) Sort Key: ((((((s.income + s.expenses) * s.split_amount) + s.transfers) + s.payouts) + s.previous_balance)) DESC Sort Method: quicksort Memory: 31kB
2.						CTE datetime_range
3.	0.001	0.001	↑ 1.0	1	1	→ Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)
4.						CTE summary
5.	59.043	854.407	↑ 4,614.5	24	1	→ GroupAggregate (cost=318,036.34..329,111.04 rows=110,747 width=204) (actual time=763.001..854.407 rows=24 loops=1) Group Key: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id)), s_1.split_amount, s_1.ledger_id, rg.id
6.						Initplan (for GroupAggregate)
7.	0.003	0.003	↑ 1.0	1	1	→ CTE Scan on datetime_range (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
8.	93.258	795.361	↑ 1.2	96,014	1	→ Sort (cost=318,036.32..318,313.19 rows=110,747 width=120) (actual time=759.245..795.361 rows=96,014 loops=1) Sort Key: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id)), s_1.split_amount, s_1.ledger_id, rg.id Sort Method: external merge Disk: 6936kB
9.	74.805	702.103	↑ 1.2	96,014	1	→ Hash Left Join (cost=7,801.78..301,942.93 rows=110,747 width=120) (actual time=72.393..702.103 rows=96,014 loops=1) Hash Cond: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id) = s_1.contract_id) Filter: ((((t.transaction_type)::text = 'transfer'::text) AND (t.payee_id = 1305)) OR (((t.transaction_type)::text = ANY ('{income,expense}'::text[])) AND (s_1.contract_id IS NOT NULL))) Rows Removed by Filter: 7310
10.	103.901	627.091	↑ 1.1	103,324	1	→ Hash Left Join (cost=7,778.06..301,414.16 rows=111,304 width=100) (actual time=72.177..627.091 rows=103,324 loops=1) Hash Cond: ((SubPlan 3) = rg.id)
11.	0.000	109.444	↑ 1.1	103,324	1	→ Gather (cost=7,633.47..254,407.30 rows=111,304 width=96) (actual time=71.670..109.444 rows=103,324 loops=1) Workers Planned: 2 Workers Launched: 2
12.	11.835	180.130	↑ 1.3	34,441	3	→ Hash Left Join (cost=6,633.47..242,276.90 rows=46,377 width=96) (actual time=63.698..180.130 rows=34,441 loops=3) Hash Cond: (r_1.track_id = tr.id)
13.	18.959	165.336	↑ 1.3	34,441	3	→ Parallel Hash Left Join (cost=6,361.14..241,882.79 rows=46,377 width=96) (actual time=60.710..165.336 rows=34,441 loops=3) Hash Cond: (t.release_id = r_1.id)
14.	12.136	113.077	↑ 1.3	34,441	3	→ Hash Left Join (cost=2,197.26..237,597.15 rows=46,377 width=88) (actual time=21.312..113.077 rows=34,441 loops=3) Hash Cond: (t.customer_id = cu.id)
15.	86.711	100.846	↑ 1.3	34,441	3	→ Parallel Bitmap Heap Scan on royalties_transaction t (cost=2,195.04..236,957.24 rows=46,377 width=54) (actual time=21.192..100.846 rows=34,441 loops=3) Recheck Cond: (customer_id = 1) Heap Blocks: exact=129
16.	14.135	14.135	↑ 1.1	103,656	1	→ Bitmap Index Scan on royalties_transaction_customer_id_2afc02dd (cost=0.00..2,167.21 rows=111,304 width=0) (actual time=14.135..14.135 rows=103,656 loops=1) Index Cond: (customer_id = 1)
17.	0.005	0.095	↑ 1.0	1	3	→ Hash (cost=2.21..2.21 rows=1 width=42) (actual time=0.095..0.095 rows=1 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 9kB
18.	0.090	0.090	↑ 1.0	1	3	→ Seq Scan on royalties_customer cu (cost=0.00..2.21 rows=1 width=42) (actual time=0.086..0.090 rows=1 loops=3) Filter: (id = 1) Rows Removed by Filter: 20
19.	7.654	33.300	↑ 1.2	9,413	3	→ Parallel Hash (cost=4,018.39..4,018.39 rows=11,639 width=12) (actual time=33.300..33.300 rows=9,413 loops=3) Buckets: 32768 Batches: 1 Memory Usage: 1408kB
20.	25.646	25.646	↑ 1.2	9,413	3	→ Parallel Seq Scan on royalties_release r_1 (cost=0.00..4,018.39 rows=11,639 width=12) (actual time=0.028..25.646 rows=9,413 loops=3)
21.	1.200	2.959	↓ 1.0	6,898	3	→ Hash (cost=188.26..188.26 rows=6,726 width=8) (actual time=2.959..2.959 rows=6,898 loops=3) Buckets: 8192 Batches: 1 Memory Usage: 307kB
22.	1.759	1.759	↓ 1.0	6,898	3	→ Seq Scan on royalties_track tr (cost=0.00..188.26 rows=6,726 width=8) (actual time=0.012..1.759 rows=6,898 loops=3)
23.	0.156	0.476	↑ 1.1	1,413	1	→ Hash (cost=125.93..125.93 rows=1,493 width=8) (actual time=0.476..0.476 rows=1,413 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 69kB
24.	0.320	0.320	↑ 1.1	1,413	1	→ Seq Scan on royalties_releasegroup rg (cost=0.00..125.93 rows=1,493 width=8) (actual time=0.006..0.320 rows=1,413 loops=1)
25.						SubPlan (for Hash Left Join)
26.	206.635	413.270	↑ 1.0	1	206,635	→ Aggregate (cost=8.31..8.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=206,635)
27.	206.635	206.635	↑ 1.0	1	206,635	→ Index Scan using royalties_releasegroup_releases_release_id_708c93d5 on royalties_releasegroup_releases (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=206,635) Index Cond: (release_id = t.release_id)
28.	0.005	0.207	↓ 1.4	13	1	→ Hash (cost=23.61..23.61 rows=9 width=40) (actual time=0.206..0.207 rows=13 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
29.	0.003	0.202	↓ 1.4	13	1	→ Subquery Scan on s_1 (cost=23.31..23.61 rows=9 width=40) (actual time=0.191..0.202 rows=13 loops=1)
30.	0.014	0.199	↓ 1.4	13	1	→ GroupAggregate (cost=23.31..23.52 rows=9 width=40) (actual time=0.190..0.199 rows=13 loops=1) Group Key: s_2.contract_id, s_2.balance_group_id
31.	0.010	0.185	↓ 1.4	13	1	→ Sort (cost=23.31..23.34 rows=9 width=13) (actual time=0.184..0.185 rows=13 loops=1) Sort Key: s_2.contract_id, s_2.balance_group_id Sort Method: quicksort Memory: 25kB
32.	0.090	0.175	↓ 1.4	13	1	→ Bitmap Heap Scan on royalties_split s_2 (cost=4.35..23.17 rows=9 width=13) (actual time=0.142..0.175 rows=13 loops=1) Recheck Cond: (payee_id = 1305) Filter: (split_amount > '0'::numeric) Heap Blocks: exact=6
33.	0.085	0.085	↓ 1.6	14	1	→ Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.35 rows=9 width=0) (actual time=0.085..0.085 rows=14 loops=1) Index Cond: (payee_id = 1305)
34.	0.066	855.916	↑ 4,614.5	24	1	→ Hash Left Join (cost=212.90..6,625.64 rows=110,747 width=377) (actual time=764.384..855.916 rows=24 loops=1) Hash Cond: (s.ledger_id = l.id)
35.	0.021	855.655	↑ 4,614.5	24	1	→ Hash Left Join (cost=187.63..2,985.74 rows=110,747 width=241) (actual time=764.179..855.655 rows=24 loops=1) Hash Cond: (s.release_id = r.id)
36.	0.033	854.856	↑ 4,614.5	24	1	→ Hash Left Join (cost=43.04..2,549.61 rows=110,747 width=217) (actual time=763.396..854.856 rows=24 loops=1) Hash Cond: (s.contract_id = c.id)
37.	854.442	854.442	↑ 4,614.5	24	1	→ CTE Scan on summary s (cost=0.00..2,214.94 rows=110,747 width=204) (actual time=763.004..854.442 rows=24 loops=1)
38.	0.176	0.381	↓ 1.0	1,340	1	→ Hash (cost=26.35..26.35 rows=1,335 width=17) (actual time=0.381..0.381 rows=1,340 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 83kB
39.	0.205	0.205	↓ 1.0	1,340	1	→ Seq Scan on royalties_contract c (cost=0.00..26.35 rows=1,335 width=17) (actual time=0.026..0.205 rows=1,340 loops=1)
40.	0.221	0.778	↑ 1.1	1,413	1	→ Hash (cost=125.93..125.93 rows=1,493 width=28) (actual time=0.778..0.778 rows=1,413 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 104kB
41.	0.557	0.557	↑ 1.1	1,413	1	→ Seq Scan on royalties_releasegroup r (cost=0.00..125.93 rows=1,493 width=28) (actual time=0.022..0.557 rows=1,413 loops=1)
42.	0.094	0.195	↓ 1.0	814	1	→ Hash (cost=15.12..15.12 rows=812 width=12) (actual time=0.195..0.195 rows=814 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 45kB
43.	0.101	0.101	↓ 1.0	814	1	→ Seq Scan on royalties_splitbalancegroup l (cost=0.00..15.12 rows=812 width=12) (actual time=0.009..0.101 rows=814 loops=1)

Planning time	:	4.639 ms
Execution time	:	861.422 ms

Sort  (cost=364321.58..364598.44 rows=110747 width=377) (actual time=855.944..855.945 rows=24 loops=1)
  Sort Key: ((((((s.income + s.expenses) * s.split_amount) + s.transfers) + s.payouts) + s.previous_balance)) DESC
  Sort Method: quicksort  Memory: 31kB
  CTE datetime_range
    ->  Result  (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)
  CTE summary
    ->  GroupAggregate  (cost=318036.34..329111.04 rows=110747 width=204) (actual time=763.001..854.407 rows=24 loops=1)
          Group Key: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id)), s_1.split_amount, s_1.ledger_id, rg.id
          InitPlan 2 (returns $1)
            ->  CTE Scan on datetime_range  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          ->  Sort  (cost=318036.32..318313.19 rows=110747 width=120) (actual time=759.245..795.361 rows=96014 loops=1)
                Sort Key: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id)), s_1.split_amount, s_1.ledger_id, rg.id
                Sort Method: external merge  Disk: 6936kB
                ->  Hash Left Join  (cost=7801.78..301942.93 rows=110747 width=120) (actual time=72.393..702.103 rows=96014 loops=1)
                      Hash Cond: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id) = s_1.contract_id)
                      Filter: ((((t.transaction_type)::text = 'transfer'::text) AND (t.payee_id = 1305)) OR (((t.transaction_type)::text = ANY ('{income,expense}'::text[])) AND (s_1.contract_id IS NOT NULL)))
                      Rows Removed by Filter: 7310
                      ->  Hash Left Join  (cost=7778.06..301414.16 rows=111304 width=100) (actual time=72.177..627.091 rows=103324 loops=1)
                            Hash Cond: ((SubPlan 3) = rg.id)
                            ->  Gather  (cost=7633.47..254407.30 rows=111304 width=96) (actual time=71.670..109.444 rows=103324 loops=1)
                                  Workers Planned: 2
                                  Workers Launched: 2
                                  ->  Hash Left Join  (cost=6633.47..242276.90 rows=46377 width=96) (actual time=63.698..180.130 rows=34441 loops=3)
                                        Hash Cond: (r_1.track_id = tr.id)
                                        ->  Parallel Hash Left Join  (cost=6361.14..241882.79 rows=46377 width=96) (actual time=60.710..165.336 rows=34441 loops=3)
                                              Hash Cond: (t.release_id = r_1.id)
                                              ->  Hash Left Join  (cost=2197.26..237597.15 rows=46377 width=88) (actual time=21.312..113.077 rows=34441 loops=3)
                                                    Hash Cond: (t.customer_id = cu.id)
                                                    ->  Parallel Bitmap Heap Scan on royalties_transaction t  (cost=2195.04..236957.24 rows=46377 width=54) (actual time=21.192..100.846 rows=34441 loops=3)
                                                          Recheck Cond: (customer_id = 1)
                                                          Heap Blocks: exact=129
                                                          ->  Bitmap Index Scan on royalties_transaction_customer_id_2afc02dd  (cost=0.00..2167.21 rows=111304 width=0) (actual time=14.135..14.135 rows=103656 loops=1)
                                                                Index Cond: (customer_id = 1)
                                                    ->  Hash  (cost=2.21..2.21 rows=1 width=42) (actual time=0.095..0.095 rows=1 loops=3)
                                                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                          ->  Seq Scan on royalties_customer cu  (cost=0.00..2.21 rows=1 width=42) (actual time=0.086..0.090 rows=1 loops=3)
                                                                Filter: (id = 1)
                                                                Rows Removed by Filter: 20
                                              ->  Parallel Hash  (cost=4018.39..4018.39 rows=11639 width=12) (actual time=33.300..33.300 rows=9413 loops=3)
                                                    Buckets: 32768  Batches: 1  Memory Usage: 1408kB
                                                    ->  Parallel Seq Scan on royalties_release r_1  (cost=0.00..4018.39 rows=11639 width=12) (actual time=0.028..25.646 rows=9413 loops=3)
                                        ->  Hash  (cost=188.26..188.26 rows=6726 width=8) (actual time=2.959..2.959 rows=6898 loops=3)
                                              Buckets: 8192  Batches: 1  Memory Usage: 307kB
                                              ->  Seq Scan on royalties_track tr  (cost=0.00..188.26 rows=6726 width=8) (actual time=0.012..1.759 rows=6898 loops=3)
                            ->  Hash  (cost=125.93..125.93 rows=1493 width=8) (actual time=0.476..0.476 rows=1413 loops=1)
                                  Buckets: 2048  Batches: 1  Memory Usage: 69kB
                                  ->  Seq Scan on royalties_releasegroup rg  (cost=0.00..125.93 rows=1493 width=8) (actual time=0.006..0.320 rows=1413 loops=1)
                            SubPlan 3
                              ->  Aggregate  (cost=8.31..8.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=206635)
                                    ->  Index Scan using royalties_releasegroup_releases_release_id_708c93d5 on royalties_releasegroup_releases  (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=206635)
                                          Index Cond: (release_id = t.release_id)
                      ->  Hash  (cost=23.61..23.61 rows=9 width=40) (actual time=0.206..0.207 rows=13 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                            ->  Subquery Scan on s_1  (cost=23.31..23.61 rows=9 width=40) (actual time=0.191..0.202 rows=13 loops=1)
                                  ->  GroupAggregate  (cost=23.31..23.52 rows=9 width=40) (actual time=0.190..0.199 rows=13 loops=1)
                                        Group Key: s_2.contract_id, s_2.balance_group_id
                                        ->  Sort  (cost=23.31..23.34 rows=9 width=13) (actual time=0.184..0.185 rows=13 loops=1)
                                              Sort Key: s_2.contract_id, s_2.balance_group_id
                                              Sort Method: quicksort  Memory: 25kB
                                              ->  Bitmap Heap Scan on royalties_split s_2  (cost=4.35..23.17 rows=9 width=13) (actual time=0.142..0.175 rows=13 loops=1)
                                                    Recheck Cond: (payee_id = 1305)
                                                    Filter: (split_amount > '0'::numeric)
                                                    Heap Blocks: exact=6
                                                    ->  Bitmap Index Scan on royalties_split_payee_id_729ebb89  (cost=0.00..4.35 rows=9 width=0) (actual time=0.085..0.085 rows=14 loops=1)
                                                          Index Cond: (payee_id = 1305)
  ->  Hash Left Join  (cost=212.90..6625.64 rows=110747 width=377) (actual time=764.384..855.916 rows=24 loops=1)
        Hash Cond: (s.ledger_id = l.id)
        ->  Hash Left Join  (cost=187.63..2985.74 rows=110747 width=241) (actual time=764.179..855.655 rows=24 loops=1)
              Hash Cond: (s.release_id = r.id)
              ->  Hash Left Join  (cost=43.04..2549.61 rows=110747 width=217) (actual time=763.396..854.856 rows=24 loops=1)
                    Hash Cond: (s.contract_id = c.id)
                    ->  CTE Scan on summary s  (cost=0.00..2214.94 rows=110747 width=204) (actual time=763.004..854.442 rows=24 loops=1)
                    ->  Hash  (cost=26.35..26.35 rows=1335 width=17) (actual time=0.381..0.381 rows=1340 loops=1)
                          Buckets: 2048  Batches: 1  Memory Usage: 83kB
                          ->  Seq Scan on royalties_contract c  (cost=0.00..26.35 rows=1335 width=17) (actual time=0.026..0.205 rows=1340 loops=1)
              ->  Hash  (cost=125.93..125.93 rows=1493 width=28) (actual time=0.778..0.778 rows=1413 loops=1)
                    Buckets: 2048  Batches: 1  Memory Usage: 104kB
                    ->  Seq Scan on royalties_releasegroup r  (cost=0.00..125.93 rows=1493 width=28) (actual time=0.022..0.557 rows=1413 loops=1)
        ->  Hash  (cost=15.12..15.12 rows=812 width=12) (actual time=0.195..0.195 rows=814 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 45kB
              ->  Seq Scan on royalties_splitbalancegroup l  (cost=0.00..15.12 rows=812 width=12) (actual time=0.009..0.101 rows=814 loops=1)
Planning Time: 4.639 ms
Execution Time: 861.422 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	206.635 ms	24.1 %
Bitmap Heap Scan	1	0.090 ms	0.0 %
Bitmap Index Scan	2	14.220 ms	1.7 %
CTE Scan	2	854.445 ms	99.8 %
Gather	1	0.000 ms	0.0 %
GroupAggregate	2	59.057 ms	6.9 %
Hash	7	1.857 ms	0.2 %
Hash Left Join	7	202.797 ms	23.7 %
Index Scan	1	206.635 ms	24.1 %
Parallel Bitmap Heap Scan	1	86.711 ms	10.1 %
Parallel Hash	1	7.654 ms	0.9 %
Parallel Hash Left Join	1	18.959 ms	2.2 %
Parallel Seq Scan	1	25.646 ms	3.0 %
Result	1	0.001 ms	0.0 %
Seq Scan	6	3.032 ms	0.4 %
Sort	3	93.297 ms	10.9 %
Subquery Scan	1	0.003 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
royalties_contract	1	0.205 ms	0.0 %
Seq Scan	1	0.205 ms	100.0 %
royalties_customer	1	0.090 ms	0.0 %
Seq Scan	1	0.090 ms	100.0 %
royalties_release	1	25.646 ms	3.0 %
Parallel Seq Scan	1	25.646 ms	100.0 %
royalties_releasegroup	2	0.877 ms	0.1 %
Seq Scan	2	0.877 ms	100.0 %
royalties_releasegroup_releases	1	206.635 ms	24.1 %
Index Scan	1	206.635 ms	100.0 %
royalties_split	1	0.090 ms	0.0 %
Bitmap Heap Scan	1	0.090 ms	100.0 %
royalties_splitbalancegroup	1	0.101 ms	0.0 %
Seq Scan	1	0.101 ms	100.0 %
royalties_track	1	1.759 ms	0.2 %
Seq Scan	1	1.759 ms	100.0 %
royalties_transaction	1	86.711 ms	10.1 %
Parallel Bitmap Heap Scan	1	86.711 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mVfF

Color mode:

Visible columns:

Per node type stats

Per table stats