explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rUzM

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 471.851 ↑ 199.8 4 1

Nested Loop Left Join (cost=107.75..34,214.97 rows=799 width=394) (actual time=309.344..471.851 rows=4 loops=1)

  • Filter: CASE WHEN (ut.utility_invoice_id IS NULL) THEN ((hashed SubPlan 1) AND (ut.utility_transaction_type_id <> ALL ('{1,11,16}'::integer[]))) WHEN (ui.utility_invoice_type_id = 3) THEN (ut.utility_invoice_id = 5,739,043) ELSE NULL::boolean END
  • Rows Removed by Filter: 131,047
2. 340.894 340.894 ↓ 82.0 131,051 1

Index Scan using idx_utility_transactions_property_id on utility_transactions ut (cost=0.57..30,170.18 rows=1,598 width=394) (actual time=0.068..340.894 rows=131,051 loops=1)

  • Index Cond: (property_id = 533,638)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (cid = 10,357))
3. 131.051 131.051 ↓ 0.0 0 131,051

Index Scan using pk_utility_invoices on utility_invoices ui (cost=0.43..2.44 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=131,051)

  • Index Cond: (id = ut.utility_invoice_id)
  • Filter: ((cid = 10,357) AND (property_id = 533,638) AND (cid = ut.cid) AND (property_id = ut.property_id))
4.          

SubPlan (for Nested Loop Left Join)

5. 0.001 0.040 ↓ 4.0 4 1

Unique (cost=106.74..106.75 rows=1 width=4) (actual time=0.039..0.040 rows=4 loops=1)

6. 0.015 0.039 ↓ 4.0 4 1

Sort (cost=106.74..106.74 rows=1 width=4) (actual time=0.039..0.039 rows=4 loops=1)

  • Sort Key: utility_transactions.original_utility_transaction_id
  • Sort Method: quicksort Memory: 25kB
7. 0.024 0.024 ↓ 4.0 4 1

Index Scan using idx_utility_transactions_utility_invoice_id on utility_transactions (cost=0.57..106.73 rows=1 width=4) (actual time=0.018..0.024 rows=4 loops=1)

  • Index Cond: (utility_invoice_id = 5,739,043)
  • Filter: ((cid = 10,357) AND (property_id = 533,638))
Planning time : 1.369 ms
Execution time : 471.950 ms