explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kPbf

Settings
# exclusive inclusive rows x rows loops node
1. 819.398 3,914.660 ↑ 445,676.0 4 1

Hash Left Join (cost=214,418.95..2,056,430.44 rows=1,782,704 width=396) (actual time=1,906.015..3,914.660 rows=4 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 = 5,739,043) ELSE NULL::boolean END
  • Rows Removed by Filter: 3,656,877
2. 2,860.817 2,860.817 ↓ 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.023..2,860.817 rows=3,656,881 loops=1)

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

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 19,707kB
4. 140.258 140.258 ↓ 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=0.034..140.258 rows=333,033 loops=1)

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

SubPlan (for Hash Left Join)

6. 0.001 0.048 ↑ 4.0 4 1

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

7. 0.019 0.047 ↑ 4.0 4 1

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

  • Sort Key: utility_transactions.original_utility_transaction_id
  • Sort Method: quicksort Memory: 25kB
8. 0.028 0.028 ↑ 4.0 4 1

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

  • Index Cond: (utility_invoice_id = 5,739,043)
  • Filter: (cid = 10,357)
Planning time : 0.568 ms
Execution time : 3,914.851 ms