explain.depesz.com

PostgreSQL's explain analyze made readable

Result: icpu

Settings
# exclusive inclusive rows x rows loops node
1. 27.828 22,915.818 ↑ 84.9 1,000 1

GroupAggregate (cost=1,631,224.91..1,633,853.88 rows=84,873 width=16) (actual time=22,869.111..22,915.818 rows=1,000 loops=1)

  • Group Key: tb_as_posi_day.id_portfolio
2. 79.003 22,887.990 ↑ 1.1 157,310 1

Sort (cost=1,631,224.91..1,631,669.97 rows=178,024 width=12) (actual time=22,869.080..22,887.990 rows=157,310 loops=1)

  • Sort Key: tb_as_posi_day.id_portfolio
  • Sort Method: external merge Disk: 3392kB
3. 30.105 22,808.987 ↑ 1.1 157,310 1

Nested Loop (cost=1,317,832.51..1,615,699.69 rows=178,024 width=12) (actual time=21,614.430..22,808.987 rows=157,310 loops=1)

4. 1.445 21,614.882 ↓ 2.5 1,000 1

HashAggregate (cost=1,317,831.95..1,317,836.02 rows=407 width=10) (actual time=21,613.872..21,614.882 rows=1,000 loops=1)

  • Group Key: "ANY_subquery".id_contract, "ANY_subquery".id_portfolio
5. 0.141 21,613.437 ↓ 2.4 1,000 1

Subquery Scan on ANY_subquery (cost=1,267,655.50..1,317,829.90 rows=410 width=10) (actual time=21,604.265..21,613.437 rows=1,000 loops=1)

  • Filter: ("ANY_subquery".id_contract = 2020)
6. 0.097 21,613.296 ↓ 2.4 1,000 1

Limit (cost=1,267,655.50..1,317,824.75 rows=412 width=10) (actual time=21,604.263..21,613.296 rows=1,000 loops=1)

7. 2.663 21,613.199 ↓ 2.4 1,000 1

Nested Loop Anti Join (cost=1,267,655.50..1,317,824.75 rows=412 width=10) (actual time=21,604.261..21,613.199 rows=1,000 loops=1)

  • -> Index Only Scan using tb_as_profitability_custodian_reprocess_pkey on tb_as_profitability_custodian_reprocess (cost=0.29..0.47 rows=1 width=8) (actual time=0.002..0.00
8. 3.575 21,610.536 ↓ 2.4 1,000 1

Hash Join (cost=1,267,655.22..1,317,625.15 rows=422 width=10) (actual time=21,603.625..21,610.536 rows=1,000 loops=1)

  • Hash Cond: (tb_as_portfolio.id_portfolio = tb_as_posi_day_1.id_portfolio)
  • Index Cond: ((id_contract = tb_as_portfolio.id_contract) AND (id_portfolio = tb_as_portfolio.id_portfolio))
  • Heap Fetches: 0
9. 3.781 3.781 ↓ 1.7 8,426 1

Seq Scan on tb_as_portfolio (cost=0.00..49,957.10 rows=4,890 width=10) (actual time=0.026..3.781 rows=8,426 loops=1)

  • Filter: ((id_portfolio < 997643) AND (id_asset = 97) AND ((id_contract)::integer = 2020))
  • Rows Removed by Filter: 2899
10. 67.581 21,603.180 ↓ 2.7 232,228 1

Hash (cost=1,266,594.30..1,266,594.30 rows=84,873 width=8) (actual time=21,603.180..21,603.180 rows=232,228 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 2 (originally 1) Memory Usage: 8193kB
11. 11,724.742 21,535.599 ↓ 2.7 232,228 1

HashAggregate (cost=1,264,896.84..1,265,745.57 rows=84,873 width=8) (actual time=21,470.592..21,535.599 rows=232,228 loops=1)

  • Group Key: tb_as_posi_day_1.id_portfolio
12. 9,810.857 9,810.857 ↑ 1.0 36,872,600 1

Seq Scan on tb_as_posi_day tb_as_posi_day_1 (cost=0.00..1,172,540.70 rows=36,942,457 width=8) (actual time=0.024..9,810.857 rows=36,872,600 loops=1)

  • Filter: (id_contract = 2020)
  • Rows Removed by Filter: 1708036
13. 1,164.000 1,164.000 ↑ 2.8 157 1,000

Index Scan using tb_as_posi_day_id_contract_id_portfolio_idx on tb_as_posi_day (cost=0.56..727.50 rows=435 width=14) (actual time=0.116..1.164 rows=157 loops=1,000)

  • Index Cond: ((id_contract = 2020) AND (id_portfolio = "ANY_subquery".id_portfolio))
Planning time : 1.364 ms
Execution time : 22,921.985 ms