explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7l4V

Settings
# exclusive inclusive rows x rows loops node
1. 706.242 19,154.686 ↓ 0.0 0 1

Hash Left Join (cost=214,418.95..2,056,430.44 rows=1,782,704 width=396) (actual time=19,154.686..19,154.686 rows=0 loops=1)

  • Hash Cond: ((ut.cid = ui.cid) AND (ut.property_id = ui.property_id) AND (ut.utility_invoice_id = ui.id))
  • 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 = 7,176,722) ELSE NULL::boolean END
  • Rows Removed by Filter: 3,656,881
2. 4,348.804 4,348.804 ↓ 1.0 3,656,881 1

Index Scan using idx_utility_transactions_cid on utility_transactions ut (cost=0.57..1,813,934.46 rows=3,565,409 width=396) (actual time=0.183..4,348.804 rows=3,656,881 loops=1)

  • Index Cond: (cid = 10,357)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
3. 161.326 14,098.466 ↓ 1.0 333,033 1

Hash (cost=208,584.92..208,584.92 rows=327,011 width=16) (actual time=14,098.466..14,098.466 rows=333,033 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 19,707kB
4. 13,937.140 13,937.140 ↓ 1.0 333,033 1

Index Scan using idx_utility_invoices_cid on utility_invoices ui (cost=0.43..208,584.92 rows=327,011 width=16) (actual time=1.412..13,937.140 rows=333,033 loops=1)

  • Index Cond: (cid = 10,357)
5.          

SubPlan (for Hash Left Join)

6. 0.000 1.174 ↓ 0.0 0 1

Unique (cost=110.65..110.73 rows=16 width=4) (actual time=1.174..1.174 rows=0 loops=1)

7. 0.048 1.174 ↓ 0.0 0 1

Sort (cost=110.65..110.69 rows=16 width=4) (actual time=1.173..1.174 rows=0 loops=1)

  • Sort Key: utility_transactions.original_utility_transaction_id
  • Sort Method: quicksort Memory: 25kB
8. 1.126 1.126 ↓ 0.0 0 1

Index Scan using idx_utility_transactions_utility_invoice_id on utility_transactions (cost=0.57..110.33 rows=16 width=4) (actual time=1.126..1.126 rows=0 loops=1)

  • Index Cond: (utility_invoice_id = 7,176,722)
  • Filter: (cid = 10,357)
Planning time : 5.454 ms
Execution time : 19,155.336 ms