explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n4BS

Settings
# exclusive inclusive rows x rows loops node
1. 3,967.247 77,846.063 ↓ 2.1 10,019,768 1

Hash Left Join (cost=2,196,447.88..6,496,248.32 rows=4,697,489 width=457) (actual time=26,218.201..77,846.063 rows=10,019,768 loops=1)

  • Hash Cond: ((last(mouvementswithpool.id_account)) = plan_comptable.auto_id)
2. 1,399.532 73,878.416 ↓ 2.1 10,019,768 1

Merge Left Join (cost=2,196,377.96..6,372,869.31 rows=4,697,489 width=435) (actual time=26,217.781..73,878.416 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0.id_ecritures = gap_order_invoice2.ecritures_id)
3. 2,419.524 51,241.751 ↓ 2.1 10,019,768 1

Merge Left Join (cost=669,478.89..4,832,162.71 rows=4,697,489 width=371) (actual time=4,980.645..51,241.751 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0.id_ecritures = entriessumpooltva.ecritures_autoid)
4. 2,106.798 43,598.868 ↓ 2.1 10,019,768 1

Merge Left Join (cost=202,772.67..4,322,627.99 rows=4,697,489 width=339) (actual time=601.650..43,598.868 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0.id_ecritures = mouvementswithpool_1.ecritures_id)
5. 2,550.847 36,430.746 ↓ 2.1 10,019,768 1

Merge Left Join (cost=0.99..4,037,696.37 rows=4,697,489 width=239) (actual time=0.110..36,430.746 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0.id_ecritures = mouvementswithpool.ecritures_id)
6. 9,038.178 9,038.178 ↑ 1.0 4,697,489 1

Index Scan using test_index10 on ecrituresallinfo0 (cost=0.43..233,923.49 rows=4,697,489 width=187) (actual time=0.048..9,038.178 rows=4,697,489 loops=1)

7. 1,684.102 24,841.721 ↓ 3.9 10,006,584 1

Materialize (cost=0.56..3,712,636.88 rows=2,593,391 width=56) (actual time=0.057..24,841.721 rows=10,006,584 loops=1)

8. 9,650.202 23,157.619 ↓ 3.9 10,006,584 1

GroupAggregate (cost=0.56..3,680,219.49 rows=2,593,391 width=96) (actual time=0.054..23,157.619 rows=10,006,584 loops=1)

  • Group Key: mouvementswithpool.ecritures_id, mouvementswithpool.pool_name
9. 13,507.417 13,507.417 ↑ 1.0 25,909,859 1

Index Scan using mvmntswthpl_crtrs_dpl_nmmntnt_bslmntnt_sgn_ndx on mouvementswithpool (cost=0.56..3,005,937.91 rows=25,933,907 width=316) (actual time=0.039..13,507.417 rows=25,909,859 loops=1)

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1371121
10. 305.383 5,061.324 ↓ 1.9 2,098,550 1

Materialize (cost=202,771.68..259,617.98 rows=1,085,593 width=104) (actual time=601.538..5,061.324 rows=2,098,550 loops=1)

11. 3,967.851 4,755.941 ↓ 1.2 1,311,931 1

GroupAggregate (cost=202,771.68..246,048.07 rows=1,085,593 width=144) (actual time=601.536..4,755.941 rows=1,311,931 loops=1)

  • Group Key: mouvementswithpool_1.ecritures_id, mouvementswithpool_1.pool_name
12. 378.845 788.090 ↓ 1.0 1,371,121 1

Sort (cost=202,771.68..206,145.74 rows=1,349,625 width=33) (actual time=601.506..788.090 rows=1,371,121 loops=1)

  • Sort Key: mouvementswithpool_1.ecritures_id
  • Sort Method: quicksort Memory: 156271kB
13. 409.245 409.245 ↓ 1.0 1,371,121 1

Index Scan using mvmntswthpl_pl_nm_ndx on mouvementswithpool mouvementswithpool_1 (cost=0.56..65,352.00 rows=1,349,625 width=33) (actual time=0.035..409.245 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
14. 1,431.897 5,223.359 ↓ 3.1 6,399,642 1

Sort (cost=466,706.22..471,887.02 rows=2,072,319 width=36) (actual time=4,378.992..5,223.359 rows=6,399,642 loops=1)

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
15. 176.512 3,791.462 ↓ 1.2 2,539,058 1

Subquery Scan on entriessumpooltva (cost=202,663.61..249,290.79 rows=2,072,319 width=36) (actual time=2,483.587..3,791.462 rows=2,539,058 loops=1)

16. 2,940.528 3,614.950 ↓ 1.2 2,539,058 1

HashAggregate (cost=202,663.61..228,567.60 rows=2,072,319 width=44) (actual time=2,483.585..3,614.950 rows=2,539,058 loops=1)

  • Group Key: mouvementswithpool_2.ecritures_id, mouvementswithpool_2.pool_name
17. 674.422 674.422 ↑ 1.0 3,507,276 1

Index Scan using mvmntswthpl_pl_nm_ndx on mouvementswithpool mouvementswithpool_2 (cost=0.56..175,482.40 rows=3,624,162 width=18) (actual time=0.022..674.422 rows=3,507,276 loops=1)

  • Index Cond: (pool_name = 'VAT'::text)
18. 0.008 21,237.133 ↓ 0.0 0 1

Sort (cost=1,526,899.07..1,527,243.03 rows=137,587 width=68) (actual time=21,237.133..21,237.133 rows=0 loops=1)

  • Sort Key: gap_order_invoice2.ecritures_id
  • Sort Method: quicksort Memory: 25kB
19. 0.001 21,237.125 ↓ 0.0 0 1

Subquery Scan on gap_order_invoice2 (cost=1,510,684.45..1,515,156.03 rows=137,587 width=68) (actual time=21,237.125..21,237.125 rows=0 loops=1)

20. 18,849.642 21,237.124 ↓ 0.0 0 1

HashAggregate (cost=1,510,684.45..1,513,780.16 rows=137,587 width=100) (actual time=21,237.124..21,237.124 rows=0 loops=1)

  • Group Key: mouvementswithpool_3.ecritures_id
  • Filter: ((COALESCE(sum(CASE WHEN (mouvementswithpool_3.pool_name = 'Accounts payable'::text) THEN mouvementswithpool_3.montant_signe ELSE NULL::numeric END), '0'::numeric) IS DISTINCT FROM '0'::numeric) AND (COALESCE(sum(CASE WHEN (mouvementswithpool_3.pool_name = 'Suppliers - Accrued invoices'::text) THEN mouvementswithpool_3.montant_signe ELSE NULL::numeric END), '0'::numeric) IS DISTINCT FROM '0'::numeric) AND (COALESCE(sum(CASE WHEN (mouvementswithpool_3.pool_name = 'VAT'::text) THEN mouvementswithpool_3.montant_signe ELSE NULL::numeric END), '0'::numeric) = '0'::numeric))
  • Rows Removed by Filter: 4697489
21. 2,387.482 2,387.482 ↑ 1.0 27,280,980 1

Seq Scan on mouvementswithpool mouvementswithpool_3 (cost=0.00..760,387.32 rows=27,283,532 width=18) (actual time=0.008..2,387.482 rows=27,280,980 loops=1)

22. 0.192 0.400 ↑ 1.0 1,819 1

Hash (cost=47.19..47.19 rows=1,819 width=26) (actual time=0.400..0.400 rows=1,819 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
23. 0.208 0.208 ↑ 1.0 1,819 1

Seq Scan on plan_comptable (cost=0.00..47.19 rows=1,819 width=26) (actual time=0.012..0.208 rows=1,819 loops=1)

Planning time : 1.885 ms