explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U1ku

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 59,626.180 ↑ 1.0 100 1

Limit (cost=7,786,643.01..7,786,648.76 rows=100 width=117) (actual time=59,626.107..59,626.180 rows=100 loops=1)

2. 0.068 59,626.172 ↑ 156,134.2 100 1

WindowAgg (cost=7,786,643.01..8,684,414.83 rows=15,613,423 width=117) (actual time=59,626.106..59,626.172 rows=100 loops=1)

3. 5,666.527 59,626.104 ↑ 156,134.2 100 1

Sort (cost=7,786,643.01..7,825,676.57 rows=15,613,423 width=93) (actual time=59,626.094..59,626.104 rows=100 loops=1)

  • Sort Key: t.cid, t.property_id, t.ps_product_id, t.contract_chain_id, t.revenue_type_id, (CASE WHEN (sum(CASE WHEN (t.transaction_id IS NOT NULL) THEN '0'::numeric ELSE t.revenue_amount END) = '0'::numeric) THEN false ELSE true END) DESC, t.accounting_period
  • Sort Method: external merge Disk: 487744kB
4. 13,426.642 53,959.577 ↑ 1.6 9,745,299 1

Finalize GroupAggregate (cost=2,354,499.31..5,349,346.60 rows=15,613,423 width=93) (actual time=21,662.849..53,959.577 rows=9,745,299 loops=1)

  • Group Key: t.cid, t.property_id, t.ps_product_id, t.revenue_type_id, t.contract_chain_id, (CASE WHEN ((scp.cid IS NOT NULL) AND (scp.close_month <= t.accounting_period)) THEN scp.property_portfolio_type_id ELSE 3 END), t.accounting_period
5. 7,453.958 40,532.935 ↑ 1.1 12,213,263 1

Gather Merge (cost=2,354,499.31..4,262,912.57 rows=13,011,186 width=92) (actual time=21,662.824..40,532.935 rows=12,213,263 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 6,867.614 33,078.977 ↑ 1.6 4,071,088 3 / 3

Partial GroupAggregate (cost=2,353,499.28..2,760,098.85 rows=6,505,593 width=92) (actual time=21,573.533..33,078.977 rows=4,071,088 loops=3)

  • Group Key: t.cid, t.property_id, t.ps_product_id, t.revenue_type_id, t.contract_chain_id, (CASE WHEN ((scp.cid IS NOT NULL) AND (scp.close_month <= t.accounting_period)) THEN scp.property_portfolio_type_id ELSE 3 END), t.accounting_period
7. 17,953.109 26,211.363 ↓ 1.7 11,299,059 3 / 3

Sort (cost=2,353,499.28..2,369,763.27 rows=6,505,593 width=37) (actual time=21,573.518..26,211.363 rows=11,299,059 loops=3)

  • Sort Key: t.cid, t.property_id, t.ps_product_id, t.revenue_type_id, t.contract_chain_id, (CASE WHEN ((scp.cid IS NOT NULL) AND (scp.close_month <= t.accounting_period)) THEN scp.property_portfolio_type_id ELSE 3 END), t.accounting_period
  • Sort Method: external merge Disk: 552824kB
8. 2,433.926 8,258.254 ↓ 1.7 11,299,059 3 / 3

Hash Left Join (cost=1,544.78..1,490,223.25 rows=6,505,593 width=37) (actual time=6.298..8,258.254 rows=11,299,059 loops=3)

  • Hash Cond: ((t.cid = scp.cid) AND (t.property_id = scp.property_id))
9. 2,060.865 5,819.181 ↓ 1.7 11,299,059 3 / 3

Hash Join (cost=259.18..1,423,274.37 rows=6,505,593 width=33) (actual time=0.988..5,819.181 rows=11,299,059 loops=3)

  • Hash Cond: (t.cid = c.id)
10. 3,757.387 3,757.387 ↑ 1.3 11,355,819 3 / 3

Parallel Seq Scan on transactions t (cost=0.00..1,385,619.80 rows=14,205,953 width=33) (actual time=0.035..3,757.387 rows=11,355,819 loops=3)

  • Filter: (accounting_period >= '2003-01-01'::date)
11. 0.231 0.929 ↑ 1.0 1,933 3 / 3

Hash (cost=196.36..196.36 rows=1,933 width=4) (actual time=0.929..0.929 rows=1,933 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
12. 0.585 0.698 ↑ 1.0 1,933 3 / 3

Bitmap Heap Scan on clients c (cost=29.54..196.36 rows=1,933 width=4) (actual time=0.127..0.698 rows=1,933 loops=3)

  • Recheck Cond: (company_status_type_id = ANY ('{4,5}'::integer[]))
  • Heap Blocks: exact=102
13. 0.113 0.113 ↑ 1.0 1,933 3 / 3

Bitmap Index Scan on clients_idx_clients_company_status_ty (cost=0.00..29.06 rows=1,933 width=0) (actual time=0.113..0.113 rows=1,933 loops=3)

  • Index Cond: (company_status_type_id = ANY ('{4,5}'::integer[]))
14. 2.774 5.147 ↑ 1.0 18,163 3 / 3

Hash (cost=649.89..649.89 rows=18,163 width=16) (actual time=5.147..5.147 rows=18,163 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1108kB
15. 2.373 2.373 ↑ 1.0 18,163 3 / 3

Seq Scan on stats_core_properties scp (cost=0.00..649.89 rows=18,163 width=16) (actual time=0.020..2.373 rows=18,163 loops=3)