explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H36v

Settings
# exclusive inclusive rows x rows loops node
1. 359.134 13,280.074 ↑ 1.5 404 1

HashAggregate (cost=1,597.67..613,343.33 rows=599 width=759) (actual time=1,773.512..13,280.074 rows=404 loops=1)

  • Group Key: c.contract_key, c.contract_id, c.contract_name, c.contract_name_opt, c.contract_type, c.issuing_company, c.customer, c.contract_manager, c.start_date, c.end_date, c.invoice_frequency, c.billing_type, c.restrict_demographics, c.base_currency, c.exchange_rate, c.avdata_key, s.code, c.invoice_suffix, c.vat_type, c.send_edi, c.print_format, c.external_code, c.sites, c.customer_reference
  • Buffers: shared hit=1,316,967
2. 354.177 1,384.316 ↓ 255.0 152,767 1

Nested Loop Left Join (cost=0.99..1,560.23 rows=599 width=759) (actual time=0.075..1,384.316 rows=152,767 loops=1)

  • Buffers: shared hit=764,599
3. 568.481 571.838 ↓ 255.0 152,767 1

Nested Loop Left Join (cost=0.57..1,238.53 rows=599 width=755) (actual time=0.064..571.838 rows=152,767 loops=1)

  • Join Filter: ((s.code)::text = (cb.site)::text)
  • Buffers: shared hit=162,550
  • -> Index Scan using contract_bill_contract_key on contract_bill cb (cost=0.43..186.04 rows=345 width=14) (actual time=0.062..0.934 rows=378loops=404)
4. 1.911 3.357 ↓ 67.3 404 1

Nested Loop (cost=0.14..96.43 rows=6 width=751) (actual time=0.048..3.357 rows=404 loops=1)

  • Join Filter: (((','::text || (c.sites)::text) || ','::text) ~~ (('%,'::text || (s.code)::text) || ',%'::text))
  • Rows Removed by Join Filter: 711
  • Buffers: shared hit=49
  • Index Cond: (c.contract_key = contract_key)
  • Filter: ((site)::text = 'NV122'::text)
  • Rows Removed by Filter: 495
  • Buffers: shared hit=162,501
5. 0.014 0.014 ↑ 1.0 1 1

Index Only Scan using sites_pkey on sites s (cost=0.14..2.16 rows=1 width=6) (actual time=0.012..0.014 rows=1 loops=1)

  • Index Cond: (code = 'NV122'::text)
  • Heap Fetches: 0
  • Buffers: shared hit=2
6. 1.432 1.432 ↓ 1.0 1,115 1

Seq Scan on contracts c (cost=0.00..69.32 rows=1,109 width=745) (actual time=0.011..1.432 rows=1,115 loops=1)

  • Filter: (((start_date IS NULL) OR (start_date <= '2019-09-30 00:00:00'::timestamp without time zone)) AND ((end_date IS NULL) OR (end_date >= '2019-09-30 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 386
  • Buffers: shared hit=47
7. 458.301 458.301 ↑ 1.0 1 152,767

Index Scan using invoices_pkey on invoices i (cost=0.42..0.53 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=152,767)

  • Index Cond: (invoice_key = cb.invoice_key)
  • Buffers: shared hit=602,049
8.          

SubPlan (for HashAggregate)

9. 4.040 5,780.028 ↑ 1.0 1 404

Aggregate (cost=510.62..510.63 rows=1 width=0) (actual time=14.306..14.307 rows=1 loops=404)

  • Buffers: shared hit=276,184
10. 119.988 5,775.988 ↓ 5.0 5 404

Bitmap Heap Scan on contract_bill (cost=415.30..510.62 rows=1 width=0) (actual time=14.275..14.297 rows=5 loops=404)

  • Recheck Cond: ((contract_key = c.contract_key) AND ((site)::text = (s.code)::text))
  • Filter: ((party IS NULL) AND ((deleted)::text = 'N'::text) AND ((invoiced)::text = 'N'::text))
  • Rows Removed by Filter: 372
  • Heap Blocks: exact=48,124
  • Buffers: shared hit=276,184
11. 18.778 5,656.000 ↓ 0.0 0 404

BitmapAnd (cost=415.30..415.30 rows=48 width=0) (actual time=14.000..14.000 rows=0 loops=404)

  • Buffers: shared hit=228,060
12. 38.380 38.380 ↑ 5.5 873 404

Bitmap Index Scan on contract_bill_contract_key (cost=0.00..78.25 rows=4,776 width=0) (actual time=0.095..0.095 rows=873 loops=404)

  • Index Cond: (contract_key = c.contract_key)
  • Buffers: shared hit=2,610
13. 5,598.842 5,598.842 ↓ 7.4 152,942 334

Bitmap Index Scan on contract_bill_idx01 (cost=0.00..336.80 rows=20,583 width=0) (actual time=16.763..16.763 rows=152,942 loops=334)

  • Index Cond: ((site)::text = (s.code)::text)
  • Buffers: shared hit=225,450
14. 4.444 5,756.596 ↑ 1.0 1 404

Aggregate (cost=510.62..510.63 rows=1 width=8) (actual time=14.249..14.249 rows=1 loops=404)

  • Buffers: shared hit=276,184
15. 107.868 5,752.152 ↓ 5.0 5 404

Bitmap Heap Scan on contract_bill contract_bill_1 (cost=415.30..510.62 rows=1 width=8) (actual time=14.218..14.238 rows=5 loops=404)

  • Recheck Cond: ((contract_key = c.contract_key) AND ((site)::text = (s.code)::text))
  • Filter: ((party IS NULL) AND ((deleted)::text = 'N'::text) AND ((invoiced)::text = 'N'::text))
  • Rows Removed by Filter: 372
  • Heap Blocks: exact=48,124
  • Buffers: shared hit=276,184
16. 18.434 5,644.284 ↓ 0.0 0 404

BitmapAnd (cost=415.30..415.30 rows=48 width=0) (actual time=13.971..13.971 rows=0 loops=404)

  • Buffers: shared hit=228,060
17. 36.360 36.360 ↑ 5.5 873 404

Bitmap Index Scan on contract_bill_contract_key (cost=0.00..78.25 rows=4,776 width=0) (actual time=0.090..0.090 rows=873 loops=404)

  • Index Cond: (contract_key = c.contract_key)
  • Buffers: shared hit=2,610
18. 5,589.490 5,589.490 ↓ 7.4 152,942 334

Bitmap Index Scan on contract_bill_idx01 (cost=0.00..336.80 rows=20,583 width=0) (actual time=16.735..16.735 rows=152,942 loops=334)

  • Index Cond: ((site)::text = (s.code)::text)
  • Buffers: shared hit=225,450
Planning time : 1.209 ms
Execution time : 13,280.704 ms