explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r12k : Optimization for: plan #DusX

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 63.526 6,336.028 ↓ 0.0 0 1

Gather (cost=353,953.86..467,127.63 rows=912 width=4) (actual time=6,330.813..6,336.028 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.047 6,272.502 ↓ 0.0 0 3

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

  • Hash Cond: (sales.filial_id = filial.idfilial)
3. 208.995 6,272.435 ↓ 0.0 0 3

Hash Left Join (cost=352,952.77..466,033.28 rows=380 width=8) (actual time=6,272.435..6,272.435 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
4. 849.404 5,839.948 ↓ 1.6 121,817 3

Hash Left Join (cost=319,664.31..426,666.58 rows=76,028 width=10) (actual time=4,619.151..5,839.948 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
5. 16.018 1,420.723 ↑ 1.2 121,817 3

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

  • Hash Cond: (ctrcs.redespacho_id = red.idcliente)
6. 16.004 1,319.916 ↑ 1.2 121,817 3

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

  • Hash Cond: (ctrcs.recebedor_id = rec.idcliente)
7. 16.642 1,221.268 ↑ 1.2 121,817 3

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

  • Hash Cond: (ctrcs.expedidor_id = exp.idcliente)
8. 77.639 1,121.493 ↑ 1.2 121,817 3

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

  • Hash Cond: (ctrcs.destinatario_id = dest.idcliente)
9. 22.476 932.757 ↑ 1.2 121,817 3

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

  • Hash Cond: (ctrcs.remetente_id = rem.idcliente)
10. 21.535 819.720 ↑ 1.2 121,817 3

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

  • Hash Cond: (sales.consignatario_id = consig.idcliente)
11. 129.877 695.941 ↑ 1.2 121,817 3

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

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

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

13. 57.144 374.199 ↓ 1.0 365,452 3

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

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

Seq Scan on sales (cost=0.00..35,612.46 rows=364,950 width=21) (actual time=0.038..317.055 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
15. 32.487 102.244 ↑ 1.0 189,756 3

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

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

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

17. 28.924 90.561 ↑ 1.0 189,756 3

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

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

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

19. 33.488 111.097 ↑ 1.0 189,756 3

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

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

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

21. 26.969 83.133 ↑ 1.0 189,756 3

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

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

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

23. 27.247 82.644 ↑ 1.0 189,756 3

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

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

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

25. 27.150 84.789 ↑ 1.0 189,756 3

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

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

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

27. 168.062 3,569.821 ↓ 2.3 858,815 3

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

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 8) Memory Usage: 3585kB
28. 2,560.172 3,401.759 ↓ 2.3 858,815 3

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

  • Group Key: crc.ctrc_id
29. 593.576 841.587 ↑ 1.0 934,374 3

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

  • Sort Key: crc.ctrc_id
  • Sort Method: external merge Disk: 34160kB
30. 167.046 248.011 ↑ 1.0 934,374 3

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

  • Hash Cond: (crc.recibo_id = rc.id)
31. 75.375 75.375 ↑ 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.014..75.375 rows=934,374 loops=3)

32. 3.006 5.590 ↑ 1.0 27,577 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1429kB
33. 2.584 2.584 ↑ 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..2.584 rows=27,577 loops=3)

34. 116.304 223.492 ↑ 1.0 859,087 3

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

  • Buckets: 131072 Batches: 16 Memory Usage: 3546kB
35. 107.188 107.188 ↑ 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..107.188 rows=859,087 loops=3)

36. 0.005 0.020 ↑ 1.0 4 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.015 0.015 ↑ 1.0 4 3

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