explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lt3N

Settings
# exclusive inclusive rows x rows loops node
1. 0.862 421.360 ↓ 0.0 0 1

Update on transaction (cost=619.98..628.06 rows=1 width=280) (actual time=421.360..421.360 rows=0 loops=1)

2.          

CTE backfill_stmt

3. 0.600 223.962 ↓ 3,145.0 3,145 1

Nested Loop (cost=50.41..570.00 rows=1 width=113) (actual time=104.877..223.962 rows=3,145 loops=1)

4. 0.705 112.168 ↓ 1,049.0 1,049 1

Nested Loop (cost=49.98..58.02 rows=1 width=113) (actual time=78.409..112.168 rows=1,049 loops=1)

5. 0.989 78.887 ↓ 1,018.0 1,018 1

HashAggregate (cost=49.42..49.43 rows=1 width=53) (actual time=78.350..78.887 rows=1,018 loops=1)

  • Group Key: (transaction_3.document_id)::text
6. 2.166 77.898 ↓ 1,092.0 1,092 1

Bitmap Heap Scan on transaction transaction_3 (cost=45.41..49.42 rows=1 width=53) (actual time=75.862..77.898 rows=1,092 loops=1)

  • Recheck Cond: (((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text) AND ((connection_id)::text = 'qbo:193514295928672'::text))
  • Filter: (document_id IS NOT NULL)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=813
7. 0.538 75.732 ↓ 0.0 0 1

BitmapAnd (cost=45.41..45.41 rows=1 width=0) (actual time=75.732..75.732 rows=0 loops=1)

8. 8.239 8.239 ↓ 13.2 2,173 1

Bitmap Index Scan on ix_transaction_deposit_account_id (cost=0.00..17.79 rows=165 width=0) (actual time=8.239..8.239 rows=2,173 loops=1)

  • Index Cond: ((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text)
9. 66.955 66.955 ↓ 1,198.2 468,483 1

Bitmap Index Scan on ix_transaction_connection_id (cost=0.00..27.36 rows=391 width=0) (actual time=66.955..66.955 rows=468,483 loops=1)

  • Index Cond: ((connection_id)::text = 'qbo:193514295928672'::text)
10. 32.576 32.576 ↑ 1.0 1 1,018

Index Scan using ix_transaction_document_id on transaction transaction_2 (cost=0.56..8.57 rows=1 width=113) (actual time=0.032..0.032 rows=1 loops=1,018)

  • Index Cond: ((document_id)::text = (transaction_3.document_id)::text)
  • Filter: ((deposit_account_id IS NOT NULL) AND ((connection_id)::text = 'qbo:193514295928672'::text))
  • Rows Removed by Filter: 0
11. 111.194 111.194 ↑ 1.3 3 1,049

Index Scan using ix_account_connection_id on account (cost=0.43..511.94 rows=4 width=0) (actual time=0.100..0.106 rows=3 loops=1,049)

  • Index Cond: ((connection_id)::text = 'qbo:193514295928672'::text)
  • Filter: ((type)::text = 'OTHER_BANK'::text)
  • Rows Removed by Filter: 79
12. 0.570 420.498 ↓ 3.0 3 1

Nested Loop (cost=49.98..58.06 rows=1 width=280) (actual time=269.860..420.498 rows=3 loops=1)

13. 0.978 84.773 ↓ 1,078.0 1,078 1

HashAggregate (cost=49.42..49.43 rows=1 width=59) (actual time=84.268..84.773 rows=1,078 loops=1)

  • Group Key: (transaction_1.document_id)::text
14. 2.413 83.795 ↓ 1,092.0 1,092 1

Bitmap Heap Scan on transaction transaction_1 (cost=45.41..49.42 rows=1 width=59) (actual time=81.504..83.795 rows=1,092 loops=1)

  • Recheck Cond: (((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text) AND ((connection_id)::text = 'qbo:193514295928672'::text))
  • Filter: (document_id IS NOT NULL)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=813
15. 0.466 81.382 ↓ 0.0 0 1

BitmapAnd (cost=45.41..45.41 rows=1 width=0) (actual time=81.382..81.382 rows=0 loops=1)

16. 10.406 10.406 ↓ 13.2 2,173 1

Bitmap Index Scan on ix_transaction_deposit_account_id (cost=0.00..17.79 rows=165 width=0) (actual time=10.406..10.406 rows=2,173 loops=1)

  • Index Cond: ((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text)
17. 70.510 70.510 ↓ 1,198.2 468,483 1

Bitmap Index Scan on ix_transaction_connection_id (cost=0.00..27.36 rows=391 width=0) (actual time=70.510..70.510 rows=468,483 loops=1)

  • Index Cond: ((connection_id)::text = 'qbo:193514295928672'::text)
18. 107.800 107.800 ↓ 0.0 0 1,078

Index Scan using ix_transaction_document_id on transaction (cost=0.56..8.57 rows=1 width=210) (actual time=0.100..0.100 rows=0 loops=1,078)

  • Index Cond: ((document_id)::text = (transaction_1.document_id)::text)
  • Filter: (deposit_account_id IS NULL)
  • Rows Removed by Filter: 1
19.          

SubPlan (forNested Loop)

20. 0.015 226.479 ↑ 1.0 1 3

Limit (cost=0.00..0.02 rows=1 width=32) (actual time=75.490..75.493 rows=1 loops=3)

21. 226.464 226.464 ↑ 1.0 1 3

CTE Scan on backfill_stmt (cost=0.00..0.02 rows=1 width=32) (actual time=75.488..75.488 rows=1 loops=3)

  • Filter: ((transaction.document_id)::text = (document_id)::text)
  • Rows Removed by Filter: 2350
22. 0.003 0.876 ↑ 1.0 1 3

Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.291..0.292 rows=1 loops=3)

23. 0.873 0.873 ↑ 1.0 1 3

CTE Scan on backfill_stmt backfill_stmt_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.291..0.291 rows=1 loops=3)

  • Filter: ((transaction.document_id)::text = (document_id)::text)
  • Rows Removed by Filter: 2350
Planning time : 11.284 ms
Execution time : 421.619 ms