explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oVv1

Settings
# exclusive inclusive rows x rows loops node
1. 4,236.415 119,540.356 ↓ 2.1 10,019,768 1

Hash Left Join (cost=3,339,142.44..6,892,908.08 rows=4,697,571 width=457) (actual time=37,487.478..119,540.356 rows=10,019,768 loops=1)

  • Hash Cond: ((first(mouvementswithpool.id_account ORDER BY mouvementswithpool.montant_absolu DESC, mouvementswithpool.montant_signe DESC)) = plan_comptable.auto_id)
2. 1,549.608 115,303.553 ↓ 2.1 10,019,768 1

Merge Left Join (cost=3,339,072.51..6,769,526.92 rows=4,697,571 width=435) (actual time=37,487.073..115,303.553 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice2.ecritures_id)
3. 2,495.672 92,539.499 ↓ 2.1 10,019,768 1

Merge Left Join (cost=1,812,661.08..5,229,354.80 rows=4,697,571 width=371) (actual time=16,272.624..92,539.499 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
4. 2,164.156 84,735.248 ↓ 2.1 10,019,768 1

Merge Left Join (cost=1,361,007.92..4,735,558.12 rows=4,697,571 width=339) (actual time=11,814.792..84,735.248 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
5. 3,189.199 77,874.106 ↓ 2.1 10,019,768 1

Merge Left Join (cost=1,159,967.60..4,452,859.49 rows=4,697,571 width=239) (actual time=11,275.884..77,874.106 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool.ecritures_id)
6. 3,938.729 12,398.464 ↑ 1.0 4,697,489 1

Sort (cost=1,159,967.04..1,171,710.96 rows=4,697,571 width=187) (actual time=11,275.823..12,398.464 rows=4,697,489 loops=1)

  • Sort Key: ecrituresallinfo0digitfacture.id_ecritures
  • Sort Method: external merge Disk: 733176kB
7. 434.897 8,459.735 ↑ 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.041..8,459.735 rows=4,697,489 loops=1)

8. 8,024.838 8,024.838 ↑ 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.040..8,024.838 rows=4,697,489 loops=1)

9. 1,899.486 62,286.443 ↓ 3.7 10,006,584 1

Materialize (cost=0.56..3,235,303.38 rows=2,728,098 width=56) (actual time=0.055..62,286.443 rows=10,006,584 loops=1)

10. 49,643.558 60,386.957 ↓ 3.7 10,006,584 1

GroupAggregate (cost=0.56..3,201,202.15 rows=2,728,098 width=96) (actual time=0.053..60,386.957 rows=10,006,584 loops=1)

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

Index Scan using text_index2 on mouvementswithpool (cost=0.56..2,784,785.28 rows=25,942,393 width=36) (actual time=0.019..10,743.399 rows=25,909,859 loops=1)

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1371121
12. 319.574 4,696.986 ↓ 1.9 2,098,550 1

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

13. 3,647.677 4,377.412 ↓ 1.2 1,311,931 1

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

  • Group Key: mouvementswithpool_1.ecritures_id, mouvementswithpool_1.pool_name
14. 380.068 729.735 ↓ 1.0 1,371,121 1

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

  • Sort Key: mouvementswithpool_1.ecritures_id
  • Sort Method: quicksort Memory: 156271kB
15. 349.667 349.667 ↓ 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.016..349.667 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
16. 1,482.455 5,308.579 ↓ 3.2 6,399,642 1

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

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
17. 180.770 3,826.124 ↓ 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,488.191..3,826.124 rows=2,539,058 loops=1)

18. 3,012.087 3,645.354 ↓ 1.3 2,539,058 1

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

  • Group Key: mouvementswithpool_2.ecritures_id, mouvementswithpool_2.pool_name
19. 633.267 633.267 ↓ 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.020..633.267 rows=3,507,276 loops=1)

  • Index Cond: (pool_name = 'VAT'::text)
20. 0.007 21,214.446 ↓ 0.0 0 1

Sort (cost=1,526,411.43..1,526,747.56 rows=134,451 width=68) (actual time=21,214.446..21,214.446 rows=0 loops=1)

  • Sort Key: gap_order_invoice2.ecritures_id
  • Sort Method: quicksort Memory: 25kB
21. 0.000 21,214.439 ↓ 0.0 0 1

Subquery Scan on gap_order_invoice2 (cost=1,510,588.75..1,514,958.41 rows=134,451 width=68) (actual time=21,214.439..21,214.439 rows=0 loops=1)

22. 18,943.153 21,214.439 ↓ 0.0 0 1

HashAggregate (cost=1,510,588.75..1,513,613.90 rows=134,451 width=100) (actual time=21,214.439..21,214.439 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
23. 2,271.286 2,271.286 ↑ 1.0 27,280,980 1

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

24. 0.180 0.388 ↑ 1.0 1,819 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
25. 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.011..0.208 rows=1,819 loops=1)

Planning time : 1.606 ms