explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S3CE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 5,814.237 ↓ 0.0 0 1

Gather Merge (cost=467,052.73..467,141.40 rows=760 width=4) (actual time=5,814.149..5,814.237 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.213 16,492.854 ↓ 0.0 0 3

Sort (cost=466,052.71..466,053.66 rows=380 width=4) (actual time=5,497.618..5,497.618 rows=0 loops=3)

  • Sort Key: sales.id
  • Sort Method: quicksort Memory: 25kB
3. 0.081 16,492.641 ↓ 0.0 0 3

Hash Join (cost=352,953.86..466,036.43 rows=380 width=4) (actual time=5,497.547..5,497.547 rows=0 loops=3)

  • Hash Cond: (sales.filial_id = filial.idfilial)
4. 260.334 16,492.455 ↓ 0.0 0 3

Hash Left Join (cost=352,952.77..466,033.28 rows=380 width=8) (actual time=5,497.485..5,497.485 rows=0 loops=3)

  • Hash Cond: (sales.id = pac.ctrc_id)
  • Filter: (CASE WHEN (sales.is_cancelado AND (pac.numero IS NOT NULL)) THEN 'L'::"char" WHEN ((NOT sales.is_cancelado) AND (pac.numero IS NOT NULL)) THEN 'C'::"char" ELSE COALESCE(((array_agg(crc.status ORDER BY crc.id DESC))[1]), 'P'::"char") END = ANY ('{P}'::"char"[]))
  • Rows Removed by Filter: 121817
5. 1,312.131 15,640.392 ↓ 1.6 121,817 3

Hash Left Join (cost=319,664.31..426,666.58 rows=76,028 width=10) (actual time=4,424.952..5,213.464 rows=121,817 loops=3)

  • Hash Cond: (sales.id = crc.ctrc_id)
  • Filter: CASE WHEN ((COALESCE(((array_agg(crc.status ORDER BY crc.id DESC))[1]), 'P'::"char") = 'N'::"char") AND (((array_agg(crc.cod_status_cte ORDER BY crc.id DESC))[1]) <> 223)) THEN (((sales.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((sales.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((sales.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone)) OR ((rem.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((rem.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((rem.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone)) OR ((dest.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((dest.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((dest.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone)) OR ((consig.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((consig.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((consig.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone)) OR ((ctrcs.expedidor_id IS NOT NULL) AND (((exp.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((exp.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((exp.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone)))) OR ((ctrcs.recebedor_id IS NOT NULL) AND (((rec.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((rec.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((rec.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone)))) OR ((ctrcs.redespacho_id IS NOT NULL) AND (((red.last_updated)::date > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) OR (((red.last_updated)::date >= (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::date) AND ((red.last_updated)::time without time zone > (((array_agg(rc.data_hora_retorno ORDER BY rc.id DESC))[1]))::time without time zone))))) ELSE true END
6. 46.860 4,000.044 ↑ 1.2 121,817 3

Hash Left Join (cost=156,460.40..257,026.52 rows=152,056 width=77) (actual time=979.582..1,333.348 rows=121,817 loops=3)

  • Hash Cond: (ctrcs.redespacho_id = red.idcliente)
7. 44.847 3,715.149 ↑ 1.2 121,817 3

Hash Left Join (cost=137,435.89..233,111.86 rows=152,056 width=69) (actual time=899.893..1,238.383 rows=121,817 loops=3)

  • Hash Cond: (ctrcs.recebedor_id = rec.idcliente)
8. 47.565 3,428.898 ↑ 1.2 121,817 3

Hash Left Join (cost=118,411.37..209,493.21 rows=152,056 width=61) (actual time=819.071..1,142.966 rows=121,817 loops=3)

  • Hash Cond: (ctrcs.expedidor_id = exp.idcliente)
9. 213.411 3,133.167 ↑ 1.2 121,817 3

Hash Join (cost=99,386.86..186,172.55 rows=152,056 width=53) (actual time=736.006..1,044.389 rows=121,817 loops=3)

  • Hash Cond: (ctrcs.destinatario_id = dest.idcliente)
10. 64.608 2,635.086 ↑ 1.2 121,817 3

Hash Join (cost=80,362.35..162,851.88 rows=152,056 width=49) (actual time=623.129..878.362 rows=121,817 loops=3)

  • Hash Cond: (ctrcs.remetente_id = rem.idcliente)
11. 61.254 2,315.055 ↑ 1.2 121,817 3

Hash Join (cost=61,337.84..139,827.21 rows=152,056 width=45) (actual time=537.641..771.685 rows=121,817 loops=3)

  • Hash Cond: (sales.consignatario_id = consig.idcliente)
12. 357.339 1,971.387 ↑ 1.2 121,817 3

Hash Join (cost=42,313.33..116,802.54 rows=152,056 width=41) (actual time=443.153..657.129 rows=121,817 loops=3)

  • Hash Cond: (ctrcs.sale_id = sales.id)
13. 517.398 517.398 ↑ 1.2 286,746 3

Parallel Seq Scan on ctrcs (cost=0.00..67,207.32 rows=358,432 width=24) (actual time=0.021..172.466 rows=286,746 loops=3)

14. 165.504 1,096.650 ↓ 1.0 365,452 3

Hash (cost=35,612.46..35,612.46 rows=364,950 width=21) (actual time=365.550..365.550 rows=365,452 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 3010kB
15. 931.146 931.146 ↓ 1.0 365,452 3

Seq Scan on sales (cost=0.00..35,612.46 rows=364,950 width=21) (actual time=0.013..310.382 rows=365,452 loops=3)

  • Filter: ((NOT is_cancelado) AND (NOT is_desativado_gwi) AND ((serie)::text >= '0'::text) AND ((serie)::text <= '899'::text) AND ((categoria)::text = 'ct'::text) AND (created_by = 8888))
  • Rows Removed by Filter: 494821
16. 88.383 282.414 ↑ 1.0 189,756 3

Hash (cost=15,725.56..15,725.56 rows=189,756 width=12) (actual time=94.138..94.138 rows=189,756 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 3251kB
17. 194.031 194.031 ↑ 1.0 189,756 3

Seq Scan on cliente consig (cost=0.00..15,725.56 rows=189,756 width=12) (actual time=0.004..64.677 rows=189,756 loops=3)

18. 83.424 255.423 ↑ 1.0 189,756 3

Hash (cost=15,725.56..15,725.56 rows=189,756 width=12) (actual time=85.141..85.141 rows=189,756 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 3252kB
19. 171.999 171.999 ↑ 1.0 189,756 3

Seq Scan on cliente rem (cost=0.00..15,725.56 rows=189,756 width=12) (actual time=0.006..57.333 rows=189,756 loops=3)

20. 87.270 284.670 ↑ 1.0 189,756 3

Hash (cost=15,725.56..15,725.56 rows=189,756 width=12) (actual time=94.890..94.890 rows=189,756 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 3260kB
21. 197.400 197.400 ↑ 1.0 189,756 3

Seq Scan on cliente dest (cost=0.00..15,725.56 rows=189,756 width=12) (actual time=0.021..65.800 rows=189,756 loops=3)

22. 79.347 248.166 ↑ 1.0 189,756 3

Hash (cost=15,725.56..15,725.56 rows=189,756 width=12) (actual time=82.722..82.722 rows=189,756 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 3250kB
23. 168.819 168.819 ↑ 1.0 189,756 3

Seq Scan on cliente exp (cost=0.00..15,725.56 rows=189,756 width=12) (actual time=0.004..56.273 rows=189,756 loops=3)

24. 78.150 241.404 ↑ 1.0 189,756 3

Hash (cost=15,725.56..15,725.56 rows=189,756 width=12) (actual time=80.468..80.468 rows=189,756 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 3250kB
25. 163.254 163.254 ↑ 1.0 189,756 3

Seq Scan on cliente rec (cost=0.00..15,725.56 rows=189,756 width=12) (actual time=0.004..54.418 rows=189,756 loops=3)

26. 77.349 238.035 ↑ 1.0 189,756 3

Hash (cost=15,725.56..15,725.56 rows=189,756 width=12) (actual time=79.345..79.345 rows=189,756 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 3250kB
27. 160.686 160.686 ↑ 1.0 189,756 3

Seq Scan on cliente red (cost=0.00..15,725.56 rows=189,756 width=12) (actual time=0.004..53.562 rows=189,756 loops=3)

28. 460.443 10,328.217 ↓ 2.3 858,815 3

Hash (cost=156,390.26..156,390.26 rows=371,093 width=17) (actual time=3,442.739..3,442.739 rows=858,815 loops=3)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 8) Memory Usage: 3585kB
29. 7,528.074 9,867.774 ↓ 2.3 858,815 3

GroupAggregate (cost=134,505.52..152,679.33 rows=371,093 width=17) (actual time=686.061..3,289.258 rows=858,815 loops=3)

  • Group Key: crc.ctrc_id
30. 1,624.227 2,339.700 ↑ 1.0 934,374 3

Sort (cost=134,505.52..136,841.46 rows=934,374 width=25) (actual time=686.012..779.900 rows=934,374 loops=3)

  • Sort Key: crc.ctrc_id
  • Sort Method: external merge Disk: 34160kB
31. 490.350 715.473 ↑ 1.0 934,374 3

Hash Left Join (cost=822.48..19,487.33 rows=934,374 width=25) (actual time=7.325..238.491 rows=934,374 loops=3)

  • Hash Cond: (crc.recibo_id = rc.id)
32. 203.460 203.460 ↑ 1.0 934,374 3

Seq Scan on ctrc_recibo_cte crc (cost=0.00..16,211.74 rows=934,374 width=17) (actual time=0.013..67.820 rows=934,374 loops=3)

33. 12.243 21.663 ↑ 1.0 27,577 3

Hash (cost=477.77..477.77 rows=27,577 width=12) (actual time=7.221..7.221 rows=27,577 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1429kB
34. 9.420 9.420 ↑ 1.0 27,577 3

Seq Scan on recibo_cte rc (cost=0.00..477.77 rows=27,577 width=12) (actual time=0.007..3.140 rows=27,577 loops=3)

35. 311.835 591.729 ↑ 1.0 859,087 3

Hash (cost=18,354.87..18,354.87 rows=859,087 width=12) (actual time=197.243..197.243 rows=859,087 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3546kB
36. 279.894 279.894 ↑ 1.0 859,087 3

Seq Scan on protocolo_autorizacao_cte pac (cost=0.00..18,354.87 rows=859,087 width=12) (actual time=0.012..93.298 rows=859,087 loops=3)

37. 0.021 0.105 ↑ 1.0 4 3

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.035..0.035 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.084 0.084 ↑ 1.0 4 3

Seq Scan on filial (cost=0.00..1.04 rows=4 width=4) (actual time=0.025..0.028 rows=4 loops=3)