explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Znqs : exists billItemAudit

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 53.917 491.161 ↑ 3.3 13,058 1

Merge Semi Join (cost=50,881.29..136,220.96 rows=43,141 width=47) (actual time=406.494..491.161 rows=13,058 loops=1)

  • Merge Cond: (auditpacka0_.id = bia.audit_package_item_id)
2. 102.635 379.774 ↑ 25.0 13,058 1

Merge Join (cost=50,700.20..116,603.08 rows=326,838 width=55) (actual time=332.883..379.774 rows=13,058 loops=1)

  • Merge Cond: (billitemsa3_.audit_package_item_id = auditpacka0_.id)
3. 213.042 213.042 ↑ 31.8 153,187 1

Index Scan using bill_item_audit_audit_package_item_id_ix on bill_item_audit billitemsa3_ (cost=0.43..2,445,124.57 rows=4,863,886 width=8) (actual time=1.287..213.042 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: 35621
4. 11.783 64.097 ↓ 2.0 13,057 1

Sort (cost=50,699.66..50,716.39 rows=6,692 width=47) (actual time=58.665..64.097 rows=13,057 loops=1)

  • Sort Key: auditpacka0_.id
  • Sort Method: quicksort Memory: 998kB
5. 3.064 52.314 ↑ 1.0 6,486 1

Merge Join (cost=0.99..50,274.45 rows=6,692 width=47) (actual time=0.148..52.314 rows=6,486 loops=1)

  • Merge Cond: (auditpacka2_.id = auditpacka0_.audit_package_id)
6. 0.097 0.097 ↑ 1.0 211 1

Index Only Scan using audit_package_pkey on audit_package auditpacka2_ (cost=0.14..23.31 rows=211 width=8) (actual time=0.013..0.097 rows=211 loops=1)

  • Heap Fetches: 151
7. 4.550 49.153 ↑ 1.0 6,486 1

Materialize (cost=0.85..53,637.47 rows=6,692 width=47) (actual time=0.047..49.153 rows=6,486 loops=1)

8. 9.143 44.603 ↑ 1.0 6,486 1

Nested Loop (cost=0.85..53,620.74 rows=6,692 width=47) (actual time=0.039..44.603 rows=6,486 loops=1)

9. 3.030 3.030 ↑ 1.0 6,486 1

Index Scan using audit_package_item_audit_package_id_ix on audit_package_item auditpacka0_ (cost=0.42..4,723.15 rows=6,692 width=39) (actual time=0.026..3.030 rows=6,486 loops=1)

  • Index Cond: (audit_package_id = ANY ('{587272,585865,587371,587373,587375,583713,587364,585894,585912,585880,581694,585694,582704,584692,585909,587351}'::bigint[]))
10. 32.430 32.430 ↑ 1.0 1 6,486

Index Scan using line_pkey on line line1_ (cost=0.43..7.30 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=6,486)

  • Index Cond: (id = auditpacka0_.line_id)
11. 57.470 57.470 ↑ 33.0 188,806 1

Index Only Scan using bill_item_audit_audit_package_item_id_ix on bill_item_audit bia (cost=0.43..482,050.79 rows=6,238,573 width=8) (actual time=2.759..57.470 rows=188,806 loops=1)

  • Heap Fetches: 30035