explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QfK

Settings
# exclusive inclusive rows x rows loops node
1. 1.961 353,701.940 ↓ 39.1 2,929 1

Unique (cost=1,292,427.50..1,292,428.06 rows=75 width=104) (actual time=353,699.171..353,701.940 rows=2,929 loops=1)

2.          

CTE t

3. 20.323 142,418.066 ↓ 118.0 22,176 1

GroupAggregate (cost=1,106,811.85..1,106,816.55 rows=188 width=44) (actual time=142,387.246..142,418.066 rows=22,176 loops=1)

  • Group Key: "TurnoversRest".date, "TurnoversRest"."dateSupply", "TurnoversRest".endpoint, "TurnoversRest"."nomenclatureCode
4. 107.897 142,397.743 ↓ 163.0 30,643 1

Sort (cost=1,106,811.85..1,106,812.32 rows=188 width=44) (actual time=142,387.224..142,397.743 rows=30,643 loops=1)

  • Sort Key: "TurnoversRest".date, "TurnoversRest"."dateSupply", "TurnoversRest".endpoint, "TurnoversRest"."nomenclatureCode
  • Sort Method: quicksort Memory: 5078kB
5. 16.768 142,289.846 ↓ 163.0 30,643 1

Gather (cost=1,005.41..1,106,804.75 rows=188 width=44) (actual time=499.668..142,289.846 rows=30,643 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 3,570.647 142,273.078 ↓ 130.4 6,129 5

Nested Loop (cost=5.41..1,105,785.95 rows=47 width=44) (actual time=41,431.120..142,273.078 rows=6,129 loops=5)

7. 1,400.401 138,702.404 ↑ 2.0 128,262 5

Hash Semi Join (cost=4.84..920,856.90 rows=260,507 width=59) (actual time=2,950.135..138,702.404 rows=128,262 loops=5)

  • Hash Cond: ("Turnovers_1".endpoint = "Endpoints_1".id)
8. 137,301.675 137,301.675 ↓ 1.1 7,271,280 5

Parallel Seq Scan on "Turnovers" "Turnovers_1" (cost=0.00..901,007.73 rows=6,455,695 width=43) (actual time=0.235..137,301.675 rows=7,271,280 loops=5)

  • Filter: ("docType" = 3)
  • Rows Removed by Filter: 30176535
9. 0.017 0.328 ↑ 1.1 28 5

Hash (cost=4.44..4.44 rows=32 width=16) (actual time=0.328..0.328 rows=28 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.311 0.311 ↑ 1.1 28 5

Index Scan using "Endpoints_client_idx" on "Endpoints" "Endpoints_1" (cost=0.28..4.44 rows=32 width=16) (actual time=0.052..0.311 rows=28 loops=5)

  • Index Cond: (client = '38ecbc48-663c-4416-8002-710cdb64693f'::uuid)
11. 0.027 0.027 ↓ 0.0 0 641,312

Index Scan using "TurnoversRest_endpoint_nomenclatureCode_idx" on "TurnoversRest" (cost=0.57..0.70 rows=1 width=56) (actual time=0.027..0.027 rows=0 loops=641,312)

  • Index Cond: ((endpoint = "Turnovers_1".endpoint) AND (("nomenclatureCode")::text = ("Turnovers_1"."nomenclatureCode")::text))
  • Filter: ("Turnovers_1".import = import)
  • Rows Removed by Filter: 16
12.          

CTE tr

13. 534.886 144,649.422 ↓ 1,303.8 109,518 1

Sort (cost=204.25..204.46 rows=84 width=120) (actual time=144,581.094..144,649.422 rows=109,518 loops=1)

  • Sort Key: t.endpoint, t."nomenclatureCode", "Imports".date DESC
  • Sort Method: quicksort Memory: 18473kB
14. 112.694 144,114.536 ↓ 1,303.8 109,518 1

WindowAgg (cost=199.05..201.57 rows=84 width=120) (actual time=143,976.868..144,114.536 rows=109,518 loops=1)

15. 395.979 144,001.842 ↓ 1,303.8 109,518 1

Sort (cost=199.05..199.26 rows=84 width=104) (actual time=143,976.849..144,001.842 rows=109,518 loops=1)

  • Sort Key: "TurnoversRest_1"."nomenclatureCode", "TurnoversRest_1".endpoint, "Imports".date
  • Sort Method: quicksort Memory: 18473kB
16. 94.672 143,605.863 ↓ 1,303.8 109,518 1

Nested Loop (cost=1.00..196.36 rows=84 width=104) (actual time=142,389.252..143,605.863 rows=109,518 loops=1)

17. 39.356 143,182.637 ↓ 1,303.8 109,518 1

Nested Loop (cost=0.57..157.92 rows=84 width=112) (actual time=142,387.423..143,182.637 rows=109,518 loops=1)

18. 142,433.649 142,433.649 ↓ 118.0 22,176 1

CTE Scan on t (cost=0.00..3.76 rows=188 width=60) (actual time=142,387.251..142,433.649 rows=22,176 loops=1)

19. 709.632 709.632 ↓ 5.0 5 22,176

Index Scan using "TurnoversRest_endpoint_nomenclatureCode_idx" on "TurnoversRest" "TurnoversRest_1" (cost=0.57..0.81 rows=1 width=52) (actual time=0.026..0.032 rows=5 loops=22,176)

  • Index Cond: ((endpoint = t.endpoint) AND (("nomenclatureCode")::text = (t."nomenclatureCode")::text))
  • Filter: (date <= t."rDate")
  • Rows Removed by Filter: 12
20. 328.554 328.554 ↑ 1.0 1 109,518

Index Scan using "Imports_pkey" on "Imports" (cost=0.43..0.45 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=109,518)

  • Index Cond: (id = "TurnoversRest_1".import)
21. 44.610 353,699.979 ↓ 115.7 8,676 1

Sort (cost=185,406.49..185,406.68 rows=75 width=104) (actual time=353,699.169..353,699.979 rows=8,676 loops=1)

  • Sort Key: "Endpoints".client, tr."nomenclatureCode
  • Sort Method: quicksort Memory: 1605kB
22. 20.587 353,655.369 ↓ 115.7 8,676 1

Nested Loop (cost=0.85..185,404.15 rows=75 width=104) (actual time=144,583.140..353,655.369 rows=8,676 loops=1)

23. 35.173 353,591.402 ↓ 115.7 8,676 1

Nested Loop Anti Join (cost=0.57..185,369.80 rows=75 width=88) (actual time=144,583.124..353,591.402 rows=8,676 loops=1)

24. 144,711.247 144,711.247 ↓ 344.8 28,962 1

CTE Scan on tr (cost=0.00..1.89 rows=84 width=88) (actual time=144,581.109..144,711.247 rows=28,962 loops=1)

  • Filter: (quantity <> prev)
  • Rows Removed by Filter: 80556
25. 208,844.982 208,844.982 ↑ 1.0 1 28,962

Index Scan using "Turnovers_endpoint_docDate_idx" on "Turnovers" (cost=0.57..2,294.13 rows=1 width=31) (actual time=7.211..7.211 rows=1 loops=28,962)

  • Index Cond: ((endpoint = tr.endpoint) AND ("docDate" >= tr.prev_date) AND ("docDate" <= tr.date))
  • Filter: (("docType" <> 11) AND (("nomenclatureCode")::text = (tr."nomenclatureCode")::text))
  • Rows Removed by Filter: 7953
26. 43.380 43.380 ↑ 1.0 1 8,676

Index Scan using "Endpoints_pkey" on "Endpoints" (cost=0.28..0.45 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=8,676)

  • Index Cond: (id = tr.endpoint)