explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M90

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 134.606 ↓ 0.0 0 1

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

2.          

CTE backfill_stmt

3. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=50.41..570.00 rows=1 width=113) (never executed)

4. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=49.98..58.02 rows=1 width=113) (never executed)

5. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=49.42..49.43 rows=1 width=53) (never executed)

  • Group Key: (transaction_3.document_id)::text
6. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on transaction transaction_3 (cost=45.41..49.42 rows=1 width=53) (never executed)

  • Recheck Cond: (((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text) AND ((connection_id)::text = 'qbo:193514295928672'::text))
  • Filter: (document_id IS NOT NULL)
7. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=45.41..45.41 rows=1 width=0) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_transaction_deposit_account_id (cost=0.00..17.79 rows=165 width=0) (never executed)

  • Index Cond: ((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text)
9. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_transaction_connection_id (cost=0.00..27.36 rows=391 width=0) (never executed)

  • Index Cond: ((connection_id)::text = 'qbo:193514295928672'::text)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_transaction_document_id on transaction transaction_2 (cost=0.56..8.57 rows=1 width=113) (never executed)

  • Index Cond: ((document_id)::text = (transaction_3.document_id)::text)
  • Filter: ((deposit_account_id IS NOT NULL) AND ((connection_id)::text = 'qbo:193514295928672'::text))
11. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_account_connection_id on account (cost=0.43..511.94 rows=4 width=0) (never executed)

  • Index Cond: ((connection_id)::text = 'qbo:193514295928672'::text)
  • Filter: ((type)::text = 'OTHER_BANK'::text)
12. 0.000 134.603 ↓ 0.0 0 1

Nested Loop (cost=49.98..58.06 rows=1 width=280) (actual time=134.603..134.603 rows=0 loops=1)

13. 0.753 101.322 ↓ 1,078.0 1,078 1

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

  • Group Key: (transaction_1.document_id)::text
14. 9.371 100.569 ↓ 1,095.0 1,095 1

Bitmap Heap Scan on transaction transaction_1 (cost=45.41..49.42 rows=1 width=59) (actual time=92.683..100.569 rows=1,095 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=817
15. 0.503 91.198 ↓ 0.0 0 1

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

16. 12.594 12.594 ↓ 13.3 2,190 1

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

  • Index Cond: ((deposit_account_id)::text = 'qbo:193514295928672:account:56'::text)
17. 78.101 78.101 ↓ 1,201.0 469,578 1

Bitmap Index Scan on ix_transaction_connection_id (cost=0.00..27.36 rows=391 width=0) (actual time=78.101..78.101 rows=469,578 loops=1)

  • Index Cond: ((connection_id)::text = 'qbo:193514295928672'::text)
18. 33.418 33.418 ↓ 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.031..0.031 rows=0 loops=1,078)

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

SubPlan (forNested Loop)

20. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=32) (never executed)

21. 0.000 0.000 ↓ 0.0 0

CTE Scan on backfill_stmt (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: ((transaction.document_id)::text = (document_id)::text)
22. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=32) (never executed)

23. 0.000 0.000 ↓ 0.0 0

CTE Scan on backfill_stmt backfill_stmt_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: ((transaction.document_id)::text = (document_id)::text)
Planning time : 11.394 ms
Execution time : 134.777 ms