explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8qo9 : Second query : without rank

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 21,642.701 ↑ 1.0 100 1

Limit (cost=2,354,499.31..2,354,517.57 rows=100 width=108) (actual time=21,642.377..21,642.701 rows=100 loops=1)

2. 0.000 21,642.693 ↑ 156,134.2 100 1

Finalize GroupAggregate (cost=2,354,499.31..5,206,223.55 rows=15,613,423 width=108) (actual time=21,642.376..21,642.693 rows=100 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
3. 186.755 21,802.385 ↑ 94,284.0 138 1

Gather Merge (cost=2,354,499.31..4,262,912.57 rows=13,011,186 width=92) (actual time=21,642.363..21,802.385 rows=138 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.319 21,615.630 ↑ 27,683.4 235 3 / 3

Partial GroupAggregate (cost=2,353,499.28..2,760,098.85 rows=6,505,593 width=92) (actual time=21,615.187..21,615.630 rows=235 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
5. 13,352.529 21,615.311 ↑ 16,023.6 406 3 / 3

Sort (cost=2,353,499.28..2,369,763.27 rows=6,505,593 width=37) (actual time=21,615.173..21,615.311 rows=406 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: 555872kB
6. 2,437.377 8,262.782 ↓ 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.267..8,262.782 rows=11,299,059 loops=3)

  • Hash Cond: ((t.cid = scp.cid) AND (t.property_id = scp.property_id))
7. 2,055.956 5,820.311 ↓ 1.7 11,299,059 3 / 3

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

  • Hash Cond: (t.cid = c.id)
8. 3,763.410 3,763.410 ↑ 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.036..3,763.410 rows=11,355,819 loops=3)

  • Filter: (accounting_period >= '2003-01-01'::date)
9. 0.233 0.945 ↑ 1.0 1,933 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
10. 0.600 0.712 ↑ 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.712 rows=1,933 loops=3)

  • Recheck Cond: (company_status_type_id = ANY ('{4,5}'::integer[]))
  • Heap Blocks: exact=102
11. 0.112 0.112 ↑ 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.112..0.112 rows=1,933 loops=3)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1108kB
13. 2.339 2.339 ↑ 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.021..2.339 rows=18,163 loops=3)