explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nc7v : xxx

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 34.056 2,592.450 ↑ 3.5 19,369 1

Merge Semi Join (cost=55,886.20..144,915.61 rows=68,270 width=47) (actual time=443.435..2,592.450 rows=19,369 loops=1)

  • Merge Cond: (auditpacka0_.id = bia.audit_package_item_id)
2. 90.834 2,508.670 ↑ 39.7 19,369 1

Merge Join (cost=55,879.54..119,493.31 rows=768,839 width=55) (actual time=434.824..2,508.670 rows=19,369 loops=1)

  • Merge Cond: (billitemsa3_.audit_package_item_id = auditpacka0_.id)
3. 2,066.256 2,066.256 ↑ 28.3 153,187 1

Index Scan using bill_item_audit_audit_package_item_id_ix on bill_item_audit billitemsa3_ (cost=0.43..2,405,615.70 rows=4,342,016 width=8) (actual time=3.538..2,066.256 rows=153,187 loops=1)

  • Filter: (subscription_id = ANY ('{158498,12096,158499,158496,158497,111433,111434,400,157526,157524,157525,12094,350}'::bigint[]))
  • Rows Removed by Filter: 676604
4. 22.587 351.580 ↓ 1.2 21,788 1

Sort (cost=55,879.10..55,923.18 rows=17,634 width=47) (actual time=347.091..351.580 rows=21,788 loops=1)

  • Sort Key: auditpacka0_.id
  • Sort Method: quicksort Memory: 1791kB
5. 11.020 328.993 ↑ 1.2 14,179 1

Nested Loop (cost=9.95..54,635.37 rows=17,634 width=47) (actual time=3.617..328.993 rows=14,179 loops=1)

6. 93.097 147.825 ↑ 1.2 14,179 1

Hash Join (cost=9.52..5,546.47 rows=17,634 width=39) (actual time=3.447..147.825 rows=14,179 loops=1)

  • Hash Cond: (auditpacka0_.audit_package_id = auditpacka2_.id)
  • Join Filter: (((auditpacka2_.shared_type)::text = 'SHARED'::text) OR (auditpacka0_.audit_package_id = ANY ('{587272,585865,587371,587373,587375,583713,587364,585894,585912,585880,581694,585694,582704,584692,585909,587351}'::bigint[])))
  • Rows Removed by Join Filter: 85409
7. 54.505 54.505 ↑ 1.0 99,588 1

Seq Scan on audit_package_item auditpacka0_ (cost=0.00..1,926.88 rows=99,588 width=39) (actual time=0.009..54.505 rows=99,588 loops=1)

8. 0.111 0.223 ↓ 1.0 211 1

Hash (cost=7.01..7.01 rows=201 width=18) (actual time=0.223..0.223 rows=211 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
9. 0.112 0.112 ↓ 1.0 211 1

Seq Scan on audit_package auditpacka2_ (cost=0.00..7.01 rows=201 width=18) (actual time=0.008..0.112 rows=211 loops=1)

10. 170.148 170.148 ↑ 1.0 1 14,179

Index Scan using line_pkey on line line1_ (cost=0.43..2.77 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=14,179)

  • Index Cond: (id = auditpacka0_.line_id)
11. 49.724 49.724 ↑ 32.1 171,839 1

Index Only Scan using bill_item_audit_audit_package_item_id_ix on bill_item_audit bia (cost=0.43..456,694.37 rows=5,523,613 width=8) (actual time=0.428..49.724 rows=171,839 loops=1)

  • Heap Fetches: 13393