#	exclusive	inclusive	rows x	rows	loops	node
1.	1,352.809	377,543.106	↓ 0.0	0	1	→ Hash Join (cost=8,922,136.28..15,041,940.52 rows=759,891 width=143) (actual time=377,543.105..377,543.106 rows=0 loops=1) Hash Cond: (ledger_posting.ledger_entry_id = ledger_entry.id)
2.	0.008	0.046	↑ 2,282,640.3	3	1	→ Nested Loop (cost=1,066,687.06..6,453,541.76 rows=6,847,921 width=37) (actual time=0.026..0.046 rows=3 loops=1)
3.	0.015	0.015	↑ 1.0	1	1	→ Index Scan using ledger_account_unique_account_name on ledger_account (cost=0.27..8.33 rows=1 width=29) (actual time=0.011..0.015 rows=1 loops=1) Index Cond: (account_name = 'FX_PROFIT_AND_LOSS'::text)
4.	0.014	0.023	↑ 17,032,009.3	3	1	→ Bitmap Heap Scan on ledger_posting (cost=1,066,686.79..5,942,573.14 rows=51,096,028 width=16) (actual time=0.013..0.023 rows=3 loops=1) Recheck Cond: (ledger_account_id = ledger_account.id) Heap Blocks: exact=3
5.	0.009	0.009	↑ 17,032,009.3	3	1	→ Bitmap Index Scan on idx_ledger_posting_ledger_account_id (cost=0.00..1,053,912.78 rows=51,096,028 width=0) (actual time=0.009..0.009 rows=3 loops=1) Index Cond: (ledger_account_id = ledger_account.id)
6.	14,473.938	376,190.251	↑ 1.3	17,039,295	1	→ Hash (cost=7,164,363.76..7,164,363.76 rows=22,253,797 width=122) (actual time=376,190.250..376,190.251 rows=17,039,295 loops=1) Buckets: 32768 Batches: 1024 Memory Usage: 2642kB
7.	59,981.855	361,716.313	↑ 1.3	17,039,295	1	→ Hash Left Join (cost=16.78..7,164,363.76 rows=22,253,797 width=122) (actual time=0.342..361,716.313 rows=17,039,295 loops=1) Hash Cond: (ledger_entry.id = posted_timestamp_override.ledger_entry_id) Filter: ((COALESCE(posted_timestamp_override.overriding_timestamp, ledger_entry.posted_timestamp) > '2019-11-01 00:00:00'::timestamp without time zone) AND (COALESCE(posted_timestamp_override.overriding_timestamp, ledger_entry.posted_timestamp) < '2019-11-20 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 183449703
8.	301,734.128	301,734.128	↓ 1.0	200,488,998	1	→ Seq Scan on ledger_entry (cost=0.00..6,638,601.00 rows=200,284,176 width=114) (actual time=0.005..301,734.128 rows=200,488,998 loops=1) Filter: ((currency)::text = 'GBP'::text) Rows Removed by Filter: 218085
9.	0.170	0.330	↓ 1.1	603	1	→ Hash (cost=9.68..9.68 rows=568 width=16) (actual time=0.330..0.330 rows=603 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 37kB
10.	0.160	0.160	↓ 1.1	603	1	→ Seq Scan on posted_timestamp_override (cost=0.00..9.68 rows=568 width=16) (actual time=0.005..0.160 rows=603 loops=1)

 Hash Join  (cost=8922136.28..15041940.52 rows=759891 width=143) (actual time=377543.105..377543.106 rows=0 loops=1)
   Hash Cond: (ledger_posting.ledger_entry_id = ledger_entry.id)
   ->  Nested Loop  (cost=1066687.06..6453541.76 rows=6847921 width=37) (actual time=0.026..0.046 rows=3 loops=1)
         ->  Index Scan using ledger_account_unique_account_name on ledger_account  (cost=0.27..8.33 rows=1 width=29) (actual time=0.011..0.015 rows=1 loops=1)
               Index Cond: (account_name = 'FX_PROFIT_AND_LOSS'::text)
         ->  Bitmap Heap Scan on ledger_posting  (cost=1066686.79..5942573.14 rows=51096028 width=16) (actual time=0.013..0.023 rows=3 loops=1)
               Recheck Cond: (ledger_account_id = ledger_account.id)
               Heap Blocks: exact=3
               ->  Bitmap Index Scan on idx_ledger_posting_ledger_account_id  (cost=0.00..1053912.78 rows=51096028 width=0) (actual time=0.009..0.009 rows=3 loops=1)
                     Index Cond: (ledger_account_id = ledger_account.id)
   ->  Hash  (cost=7164363.76..7164363.76 rows=22253797 width=122) (actual time=376190.250..376190.251 rows=17039295 loops=1)
         Buckets: 32768  Batches: 1024  Memory Usage: 2642kB
         ->  Hash Left Join  (cost=16.78..7164363.76 rows=22253797 width=122) (actual time=0.342..361716.313 rows=17039295 loops=1)
               Hash Cond: (ledger_entry.id = posted_timestamp_override.ledger_entry_id)
               Filter: ((COALESCE(posted_timestamp_override.overriding_timestamp, ledger_entry.posted_timestamp) > '2019-11-01 00:00:00'::timestamp without time zone) AND (COALESCE(posted_timestamp_override.overriding_timestamp, ledger_entry.posted_timestamp) < '2019-11-20 00:00:00'::timestamp without time zone))
               Rows Removed by Filter: 183449703
               ->  Seq Scan on ledger_entry  (cost=0.00..6638601.00 rows=200284176 width=114) (actual time=0.005..301734.128 rows=200488998 loops=1)
                     Filter: ((currency)::text = 'GBP'::text)
                     Rows Removed by Filter: 218085
               ->  Hash  (cost=9.68..9.68 rows=568 width=16) (actual time=0.330..0.330 rows=603 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 37kB
                     ->  Seq Scan on posted_timestamp_override  (cost=0.00..9.68 rows=568 width=16) (actual time=0.005..0.160 rows=603 loops=1)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.014 ms	0.0 %
Bitmap Index Scan	1	0.009 ms	0.0 %
Hash	2	14,474.108 ms	3.8 %
Hash Join	1	1,352.809 ms	0.4 %
Hash Left Join	1	59,981.855 ms	15.9 %
Index Scan	1	0.015 ms	0.0 %
Nested Loop	1	0.008 ms	0.0 %
Seq Scan	2	301,734.288 ms	79.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ledger_account	1	0.015 ms	0.0 %
Index Scan	1	0.015 ms	100.0 %
ledger_entry	1	301,734.128 ms	79.9 %
Seq Scan	1	301,734.128 ms	100.0 %
ledger_posting	1	0.014 ms	0.0 %
Bitmap Heap Scan	1	0.014 ms	100.0 %
posted_timestamp_override	1	0.160 ms	0.0 %
Seq Scan	1	0.160 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uklZ

Color mode:

Visible columns:

Per node type stats

Per table stats