explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YW8

Settings
# exclusive inclusive rows x rows loops node
1. 2,643.901 87,085.147 ↓ 2.1 10,019,768 1

Hash Left Join (cost=5,643,898.19..9,255,628.80 rows=4,697,571 width=457) (actual time=49,965.755..87,085.147 rows=10,019,768 loops=1)

  • Hash Cond: ((first(mouvementswithpool.id_account)) = plan_comptable.auto_id)
2. 1,603.408 84,440.859 ↓ 2.1 10,019,768 1

Merge Left Join (cost=5,643,828.27..9,190,967.27 rows=4,697,571 width=435) (actual time=49,965.357..84,440.859 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice.ecritures_id)
3. 2,172.502 65,842.344 ↓ 2.1 10,019,768 1

Merge Left Join (cost=5,643,827.70..5,862,458.95 rows=4,697,571 width=371) (actual time=49,965.292..65,842.344 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
4. 1,912.535 58,416.356 ↓ 2.1 10,019,768 1

Merge Left Join (cost=5,192,174.54..5,368,662.28 rows=4,697,571 width=339) (actual time=45,506.913..58,416.356 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
5. 2,991.578 51,896.073 ↓ 2.1 10,019,768 1

Merge Left Join (cost=4,991,134.22..5,085,963.65 rows=4,697,571 width=239) (actual time=44,954.243..51,896.073 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool.ecritures_id)
6. 3,707.401 11,828.079 ↑ 1.0 4,697,489 1

Sort (cost=1,159,967.04..1,171,710.96 rows=4,697,571 width=187) (actual time=10,837.535..11,828.079 rows=4,697,489 loops=1)

  • Sort Key: ecrituresallinfo0digitfacture.id_ecritures
  • Sort Method: external merge Disk: 733,176kB
7. 415.565 8,120.678 ↑ 1.0 4,697,489 1

Subquery Scan on ecrituresallinfo0digitfacture (cost=0.00..205,877.35 rows=4,697,571 width=187) (actual time=0.039..8,120.678 rows=4,697,489 loops=1)

8. 7,705.113 7,705.113 ↑ 1.0 4,697,489 1

Seq Scan on ecrituresallinfo0 (cost=0.00..158,901.64 rows=4,697,571 width=187) (actual time=0.038..7,705.113 rows=4,697,489 loops=1)

9. 922.706 37,076.416 ↓ 3.9 10,006,584 1

Materialize (cost=3,831,167.19..3,870,080.77 rows=2,594,239 width=56) (actual time=34,116.700..37,076.416 rows=10,006,584 loops=1)

10. 8,312.390 36,153.710 ↓ 3.9 10,006,584 1

Sort (cost=3,831,167.19..3,837,652.78 rows=2,594,239 width=96) (actual time=34,116.697..36,153.710 rows=10,006,584 loops=1)

  • Sort Key: mouvementswithpool.ecritures_id, mouvementswithpool.pool_name
  • Sort Method: external merge Disk: 538,968kB
11. 16,571.197 27,841.320 ↓ 3.9 10,006,584 1

HashAggregate (cost=3,528,849.10..3,554,791.49 rows=2,594,239 width=96) (actual time=22,779.232..27,841.320 rows=10,006,584 loops=1)

  • Group Key: mouvementswithpool.ecritures_id, mouvementswithpool.pool_name
12. 11,270.123 11,270.123 ↑ 1.0 25,909,859 1

Index Scan Backward using test_index5 on mouvementswithpool (cost=0.56..2,880,289.28 rows=25,942,393 width=316) (actual time=0.022..11,270.123 rows=25,909,859 loops=1)

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1,371,121
13. 279.786 4,607.748 ↓ 1.9 2,098,550 1

Materialize (cost=201,040.32..257,472.62 rows=1,078,566 width=104) (actual time=552.668..4,607.748 rows=2,098,550 loops=1)

14. 3,626.312 4,327.962 ↓ 1.2 1,311,931 1

GroupAggregate (cost=201,040.32..243,990.55 rows=1,078,566 width=144) (actual time=552.665..4,327.962 rows=1,311,931 loops=1)

  • Group Key: mouvementswithpool_1.ecritures_id, mouvementswithpool_1.pool_name
15. 355.882 701.650 ↓ 1.0 1,371,121 1

Sort (cost=201,040.32..204,386.79 rows=1,338,587 width=33) (actual time=552.639..701.650 rows=1,371,121 loops=1)

  • Sort Key: mouvementswithpool_1.ecritures_id
  • Sort Method: quicksort Memory: 156,271kB
16. 345.768 345.768 ↓ 1.0 1,371,121 1

Index Scan using test_index on mouvementswithpool mouvementswithpool_1 (cost=0.56..64,823.83 rows=1,338,587 width=33) (actual time=0.023..345.768 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
17. 1,422.773 5,253.486 ↓ 3.2 6,399,642 1

Sort (cost=451,653.16..456,719.76 rows=2,026,639 width=36) (actual time=4,458.374..5,253.486 rows=6,399,642 loops=1)

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217,323kB
18. 178.354 3,830.713 ↓ 1.3 2,539,058 1

Subquery Scan on entriessumpooltva (cost=193,756.69..239,356.07 rows=2,026,639 width=36) (actual time=2,485.766..3,830.713 rows=2,539,058 loops=1)

19. 3,017.024 3,652.359 ↓ 1.3 2,539,058 1

HashAggregate (cost=193,756.69..219,089.67 rows=2,026,639 width=44) (actual time=2,485.765..3,652.359 rows=2,539,058 loops=1)

  • Group Key: mouvementswithpool_2.ecritures_id, mouvementswithpool_2.pool_name
20. 635.335 635.335 ↓ 1.0 3,507,276 1

Index Scan using test_index on mouvementswithpool mouvementswithpool_2 (cost=0.56..167,771.55 rows=3,464,685 width=18) (actual time=0.018..635.335 rows=3,507,276 loops=1)

  • Index Cond: (pool_name = 'VAT'::text)
21. 39.700 16,995.107 ↑ 10.4 263,432 1

Materialize (cost=0.56..3,282,663.16 rows=2,728,098 width=68) (actual time=0.065..16,995.107 rows=263,432 loops=1)

22. 171.676 16,955.407 ↑ 11.1 246,593 1

Subquery Scan on gap_order_invoice (cost=0.56..3,275,842.92 rows=2,728,098 width=68) (actual time=0.060..16,955.407 rows=246,593 loops=1)

23. 10,463.687 16,783.731 ↑ 11.1 246,593 1

GroupAggregate (cost=0.56..3,180,359.49 rows=2,728,098 width=44) (actual time=0.053..16,783.731 rows=246,593 loops=1)

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

Index Scan using text_index2 on mouvementswithpool mouvementswithpool_3 (cost=0.56..2,716,582.83 rows=27,280,980 width=18) (actual time=0.016..6,320.044 rows=27,280,980 loops=1)

25. 0.197 0.387 ↑ 1.0 1,819 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 125kB
26. 0.190 0.190 ↑ 1.0 1,819 1

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