explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Ou3

Settings
# exclusive inclusive rows x rows loops node
1. 3.205 3,075.868 ↑ 1.1 3,975 1

Sort (cost=90,968.45..90,979.22 rows=4,309 width=96) (actual time=3,075.369..3,075.868 rows=3,975 loops=1)

  • Sort Key: (max(aliassincronizacaoaplicalimitseparado.sincronizacaoversao))
  • Sort Method: quicksort Memory: 407kB
2. 146.687 3,072.663 ↑ 1.1 3,975 1

GroupAggregate (cost=90,589.84..90,708.33 rows=4,309 width=96) (actual time=2,562.110..3,072.663 rows=3,975 loops=1)

  • Group Key: (COALESCE(transacionadorcodigo.cod, COALESCE(transacionadordocumento.cod, transdependente.cod), aliassincronizacaoaplicalimitseparado.codigotransacionador)), ((round(COALESCE(transacionadorcodigo.cgc, transacionadordocumento.cgc, ((CASE WHEN (((SubPlan 1))::text = ''::text) THEN '0'::character varying ELSE (SubPlan 2) END)::numeric(18,0))), 0))::character varying)
3. 813.284 2,925.976 ↓ 95.9 413,400 1

Sort (cost=90,589.84..90,600.61 rows=4,309 width=96) (actual time=2,561.904..2,925.976 rows=413,400 loops=1)

  • Sort Key: (COALESCE(transacionadorcodigo.cod, COALESCE(transacionadordocumento.cod, transdependente.cod), aliassincronizacaoaplicalimitseparado.codigotransacionador)), ((round(COALESCE(transacionadorcodigo.cgc, transacionadordocumento.cgc, ((CASE WHEN (((SubPlan 1))::text = ''::text) THEN '0'::character varying ELSE (SubPlan 2) END)::numeric(18,0))), 0))::character varying)
  • Sort Method: external merge Disk: 14272kB
4. 257.692 2,112.692 ↓ 95.9 413,400 1

Merge Right Join (cost=87,276.51..90,329.72 rows=4,309 width=96) (actual time=1,820.502..2,112.692 rows=413,400 loops=1)

  • Merge Cond: (transdependente.cpf = (((((CASE WHEN (((SubPlan 1))::text = ''::text) THEN '0'::character varying ELSE (SubPlan 2) END)::numeric(18,0)))::bigint)::numeric))
5. 3.858 3.858 ↑ 6.6 105 1

Index Scan using trsccv01_idx02 on trsccv01 transdependente (cost=0.29..2,929.84 rows=688 width=9) (actual time=3.405..3.858 rows=105 loops=1)

  • Index Cond: (cad > '0'::numeric)
6. 40.586 1,851.142 ↓ 138.8 413,297 1

Sort (cost=87,276.22..87,283.66 rows=2,978 width=87) (actual time=1,817.085..1,851.142 rows=413,297 loops=1)

  • Sort Key: (((((CASE WHEN (((SubPlan 1))::text = ''::text) THEN '0'::character varying ELSE (SubPlan 2) END)::numeric(18,0)))::bigint)::numeric)
  • Sort Method: quicksort Memory: 407kB
7. 417.693 1,810.556 ↓ 1.3 3,975 1

Nested Loop Left Join (cost=1,729.95..87,104.39 rows=2,978 width=87) (actual time=8.718..1,810.556 rows=3,975 loops=1)

  • Join Filter: (aliassincronizacaoaplicalimitseparado.codigotransacionador = '0'::numeric)
  • Rows Removed by Join Filter: 2953425
  • Filter: (transacionadordocumento.dtx IS NULL)
8. 6.310 116.888 ↓ 1.3 3,975 1

Nested Loop Left Join (cost=1,729.53..74,718.74 rows=2,984 width=72) (actual time=8.113..116.888 rows=3,975 loops=1)

  • Filter: (transacionadorcodigo.dtx IS NULL)
9. 0.738 82.753 ↓ 1.3 3,975 1

Limit (cost=1,729.24..63,091.52 rows=2,990 width=57) (actual time=8.091..82.753 rows=3,975 loops=1)

10. 16.678 82.015 ↓ 1.3 3,975 1

Subquery Scan on aliassincronizacaoaplicalimitseparado (cost=1,729.24..63,091.52 rows=2,990 width=57) (actual time=8.090..82.015 rows=3,975 loops=1)

11. 3.804 9.687 ↓ 1.3 3,975 1

Sort (cost=1,729.24..1,736.72 rows=2,990 width=73) (actual time=8.003..9.687 rows=3,975 loops=1)

  • Sort Key: (max(sincronizacaotransacionadordebito.nr_sincronizacaoversao))
  • Sort Method: quicksort Memory: 283kB
12. 3.905 5.883 ↓ 1.3 3,975 1

HashAggregate (cost=1,526.73..1,556.63 rows=2,990 width=73) (actual time=4.783..5.883 rows=3,975 loops=1)

  • Group Key: sincronizacaotransacionadordebito.cd_transacionador, sincronizacaotransacionadordebito.tx_cpfcnpj
13. 1.978 1.978 ↓ 1.3 3,993 1

Index Scan using tb_sinc_transdebito_idx01 on tb_sinc_transacionadordebito sincronizacaotransacionadordebito (cost=0.42..1,503.84 rows=3,052 width=16) (actual time=0.019..1.978 rows=3,993 loops=1)

  • Index Cond: ((nr_sincronizacaoversao >= '11228567'::numeric) AND (nr_sincronizacaoversao <= '11237135'::numeric))
  • Filter: ((cd_transacionador <> '0'::numeric) OR (((tx_cpfcnpj)::text <> ''::text) AND ((tx_cpfcnpj)::text <> '0'::text)))
  • Rows Removed by Filter: 1
14.          

SubPlan (forSubquery Scan)

15. 55.650 55.650 ↑ 1,000.0 1 3,975

Function Scan on pcd_retornar_somente_numeros (cost=0.25..10.25 rows=1,000 width=516) (actual time=0.014..0.014 rows=1 loops=3,975)

16. 0.000 0.000 ↓ 0.0 0

Function Scan on pcd_retornar_somente_numeros pcd_retornar_somente_numeros_1 (cost=0.25..10.25 rows=1,000 width=516) (never executed)

17. 27.825 27.825 ↑ 1.0 1 3,975

Index Scan using trstra01_pkey on trstra01 transacionadorcodigo (cost=0.29..3.87 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=3,975)

  • Index Cond: (cod = aliassincronizacaoaplicalimitseparado.codigotransacionador)
18. 1,275.975 1,275.975 ↓ 743.0 743 3,975

Index Scan using trstra01_004 on trstra01 transacionadordocumento (cost=0.41..4.14 rows=1 width=19) (actual time=0.011..0.321 rows=743 loops=3,975)

  • Index Cond: (cgc = ((CASE WHEN (((SubPlan 1))::text = ''::text) THEN '0'::character varying ELSE (SubPlan 2) END)::numeric(18,0)))