explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DTCi : teste1

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 46,714.029 ↓ 50.0 50 1

Limit (cost=3,455.02..3,492.77 rows=1 width=655) (actual time=46,713.369..46,714.029 rows=50 loops=1)

2. 0.151 46,714.015 ↓ 50.0 50 1

Result (cost=3,455.02..3,492.77 rows=1 width=655) (actual time=46,713.368..46,714.015 rows=50 loops=1)

3. 0.493 46,713.314 ↓ 50.0 50 1

Sort (cost=3,455.02..3,455.03 rows=1 width=631) (actual time=46,713.311..46,713.314 rows=50 loops=1)

  • Sort Key: ct.idcategory, dr.iddocument, dr.cdrevision
  • Sort Method: top-N heapsort Memory: 50kB
4. 0.841 46,712.821 ↓ 179.0 179 1

Nested Loop Anti Join (cost=2,655.79..3,455.01 rows=1 width=631) (actual time=44,707.067..46,712.821 rows=179 loops=1)

5. 12.217 46,711.264 ↓ 179.0 179 1

Nested Loop (cost=2,655.50..3,453.10 rows=1 width=473) (actual time=44,707.040..46,711.264 rows=179 loops=1)

  • Join Filter: ((dc.cddocument = attribfilterda0.cddocument) AND (de.cdrevision = attribfilterda0.cdrevision))
6. 21,859.334 46,608.069 ↓ 30,326.0 30,326 1

Nested Loop Left Join (cost=2,655.08..3,452.39 rows=1 width=485) (actual time=171.230..46,608.069 rows=30,326 loops=1)

  • Join Filter: (final_result.cdrevision = dr.cdrevision)
  • Rows Removed by Join Filter: 306981067
7. 187.436 7,189.981 ↓ 30,326.0 30,326 1

Nested Loop Left Join (cost=2,655.08..3,429.89 rows=1 width=453) (actual time=29.882..7,189.981 rows=30,326 loops=1)

  • Join Filter: ((gfft.oidform)::text = (eff.oid)::text)
  • Rows Removed by Join Filter: 2062168
8. 3,314.081 6,850.915 ↓ 30,326.0 30,326 1

Nested Loop Left Join (cost=2,655.08..3,427.94 rows=1 width=453) (actual time=29.859..6,850.915 rows=30,326 loops=1)

  • Join Filter: (gfft.cdeletronicfilecfg = cfg.cdeletronicfilecfg)
  • Rows Removed by Join Filter: 36209244
9. 14.385 686.190 ↓ 30,326.0 30,326 1

Nested Loop (cost=2,655.08..3,395.50 rows=1 width=371) (actual time=29.552..686.190 rows=30,326 loops=1)

10. 19.229 580.827 ↓ 30,326.0 30,326 1

Nested Loop Left Join (cost=2,654.80..3,394.93 rows=1 width=359) (actual time=29.539..580.827 rows=30,326 loops=1)

11. 16.463 500.946 ↓ 30,326.0 30,326 1

Nested Loop (cost=2,654.52..3,394.47 rows=1 width=359) (actual time=29.523..500.946 rows=30,326 loops=1)

12. 13.362 423.831 ↓ 30,326.0 30,326 1

Nested Loop Left Join (cost=2,654.24..3,394.10 rows=1 width=329) (actual time=29.508..423.831 rows=30,326 loops=1)

13. 39.652 349.817 ↓ 30,326.0 30,326 1

Nested Loop (cost=2,653.95..3,393.76 rows=1 width=325) (actual time=29.490..349.817 rows=30,326 loops=1)

14. 22.066 97.862 ↓ 30,329.0 30,329 1

Hash Join (cost=2,653.67..3,386.96 rows=1 width=287) (actual time=29.440..97.862 rows=30,329 loops=1)

  • Hash Cond: (dr.cddocument = dc.cddocument)
15. 39.802 66.152 ↑ 1.0 30,329 1

Hash Join (cost=1,575.80..2,229.02 rows=30,498 width=275) (actual time=19.765..66.152 rows=30,329 loops=1)

  • Hash Cond: ((de.cddocument = dr.cddocument) AND (de.cdrevision = dr.cdrevision))
16. 6.739 6.739 ↑ 1.0 31,467 1

Seq Scan on dcfile de (cost=0.00..487.21 rows=31,621 width=12) (actual time=0.007..6.739 rows=31,467 loops=1)

17. 9.779 19.611 ↑ 1.0 30,329 1

Hash (cost=1,119.36..1,119.36 rows=30,429 width=263) (actual time=19.611..19.611 rows=30,329 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 4538kB
18. 9.832 9.832 ↑ 1.0 30,329 1

Seq Scan on dcdocrevision dr (cost=0.00..1,119.36 rows=30,429 width=263) (actual time=0.006..9.832 rows=30,329 loops=1)

  • Filter: (fgcurrent = 1)
  • Rows Removed by Filter: 1138
19. 3.871 9.644 ↑ 1.0 30,329 1

Hash (cost=697.07..697.07 rows=30,464 width=12) (actual time=9.644..9.644 rows=30,329 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1441kB
20. 5.773 5.773 ↑ 1.0 30,329 1

Seq Scan on dcdocument dc (cost=0.00..697.07 rows=30,464 width=12) (actual time=0.008..5.773 rows=30,329 loops=1)

  • Filter: (fgstatus = ANY ('{1,2,3,5,6}'::integer[]))
21. 90.987 212.303 ↑ 1.0 1 30,329

Index Scan using dccategory_pkey on dccategory ct (cost=0.28..6.78 rows=1 width=42) (actual time=0.007..0.007 rows=1 loops=30,329)

  • Index Cond: (cdcategory = dr.cdcategory)
  • Filter: (SubPlan 5)
  • Rows Removed by Filter: 0
22.          

SubPlan (forIndex Scan)

23. 60.658 121.316 ↑ 2.0 1 30,329

Append (cost=0.29..12.64 rows=2 width=4) (actual time=0.004..0.004 rows=1 loops=30,329)

24. 60.658 60.658 ↑ 1.0 1 30,329

Index Only Scan using gnuserpermtyperole_pkey on gnuserpermtyperole pm_1 (cost=0.29..12.61 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=30,329)

  • Index Cond: ((cduser = ANY ('{40,-1}'::integer[])) AND (cdpermission = 5) AND (cdtyperole = ct.cdtyperole) AND (fgpermissiontype = 1))
  • Heap Fetches: 30326
25. 0.000 0.000 ↓ 0.0 0 3

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=3)

  • One-Time Filter: (ct.cdtyperole IS NULL)
26. 60.652 60.652 ↑ 1.0 1 30,326

Index Scan using dcdocumentarchival_pkey on dcdocumentarchival da (cost=0.29..0.34 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=30,326)

  • Index Cond: (cddocument = dr.cddocument)
27. 60.652 60.652 ↑ 1.0 1 30,326

Index Scan using gnrevision_pkey on gnrevision gr (cost=0.29..0.37 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=30,326)

  • Index Cond: (cdrevision = de.cdrevision)
28. 60.652 60.652 ↑ 1.0 1 30,326

Index Scan using gnrevconfig_pkey on gnrevconfig grc (cost=0.28..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=30,326)

  • Index Cond: (ct.cdrevconfig = cdrevconfig)
29. 90.978 90.978 ↑ 1.0 1 30,326

Index Scan using gneletronicfilecfg_pkey on gneletronicfilecfg cfg (cost=0.28..0.57 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=30,326)

  • Index Cond: (cdeletronicfilecfg = ct.cdeletronicfilecfg)
30. 2,850.644 2,850.644 ↓ 1.0 1,195 30,326

Seq Scan on gnformcfgtemp gfft (cost=0.00..17.75 rows=1,175 width=86) (actual time=0.003..0.094 rows=1,195 loops=30,326)

31. 151.630 151.630 ↓ 1.6 68 30,326

Seq Scan on efform eff (cost=0.00..1.42 rows=42 width=164) (actual time=0.001..0.005 rows=68 loops=30,326)

32. 17,558.754 17,558.754 ↓ 10.1 10,123 30,326

Function Scan on crosstab final_result (cost=0.00..10.00 rows=1,000 width=36) (actual time=0.005..0.579 rows=10,123 loops=30,326)

33. 90.978 90.978 ↓ 0.0 0 30,326

Index Scan using dcdocumentattrib_pkey on dcdocumentattrib attribfilterda0 (cost=0.42..0.70 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=30,326)

  • Index Cond: ((cdattribute = 19) AND (cdrevision = gr.cdrevision))
  • Filter: (cdvalue = 359)
  • Rows Removed by Filter: 0
34. 0.716 0.716 ↓ 0.0 0 179

Index Scan using seixtyperoleperm on gnuserpermtyperole pm (cost=0.29..1.07 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=179)

  • Index Cond: (cdtyperole = ct.cdtyperole)
  • Filter: ((cduser = ANY ('{40,-1}'::integer[])) AND (cdpermission = 5) AND (fgpermissiontype = 2))
  • Rows Removed by Filter: 6
35.          

SubPlan (forResult)

36. 0.000 0.400 ↑ 1.0 1 50

Aggregate (cost=17.04..17.05 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=50)

37. 0.000 0.400 ↓ 0.0 0 50

Nested Loop (cost=0.86..17.04 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=50)

  • Join Filter: (dcf.cdcomplexfilecont = gnf.cdcomplexfilecont)
38. 0.050 0.200 ↑ 1.0 1 50

Nested Loop (cost=0.57..16.61 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=50)

39. 0.100 0.100 ↑ 1.0 1 50

Index Scan using seixfiledocrevisio on dcfile dcf (cost=0.29..8.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)

  • Index Cond: ((cddocument = dc.cddocument) AND (cdrevision = dr.cdrevision))
40. 0.050 0.050 ↑ 1.0 1 50

Index Only Scan using gncompfilecontcopy_pkey on gncompfilecontcopy gnc (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=50)

  • Index Cond: (cdcomplexfilecont = dcf.cdcomplexfilecont)
  • Heap Fetches: 50
41. 0.200 0.200 ↓ 0.0 0 50

Index Scan using seixfilegncompfile on gnfile gnf (cost=0.29..0.41 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=50)

  • Index Cond: (cdcomplexfilecont = gnc.cdcomplexfilecont)
  • Filter: (fgdigitalsigned = 1)
  • Rows Removed by Filter: 7
42. 0.000 0.050 ↑ 1.0 1 50

Aggregate (cost=11.29..11.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)

43. 0.050 0.050 ↓ 0.0 0 50

Bitmap Heap Scan on gnrevisionassoc ra (cost=4.17..11.28 rows=3 width=4) (actual time=0.001..0.001 rows=0 loops=50)

  • Recheck Cond: (dr.cdrevision = cdrevision)
44. 0.000 0.000 ↓ 0.0 0 50

Bitmap Index Scan on seixrevisassocrev (cost=0.00..4.17 rows=3 width=0) (actual time=0.000..0.000 rows=0 loops=50)

  • Index Cond: (dr.cdrevision = cdrevision)
45. 0.000 0.050 ↑ 1.0 1 50

Aggregate (cost=1.01..1.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)

46. 0.050 0.050 ↓ 0.0 0 50

Seq Scan on gneletfilecfgtemp gnt (cost=0.00..1.01 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=50)

  • Filter: (cdeletronicfilecfg = cfg.cdeletronicfilecfg)
  • Rows Removed by Filter: 3
47. 0.000 0.050 ↑ 1.0 1 50

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)

48. 0.050 0.050 ↓ 0.0 0 50

Index Scan using seixformccdeletron on gnformcfgtemp gnf_1 (cost=0.28..8.29 rows=1 width=82) (actual time=0.001..0.001 rows=0 loops=50)

  • Index Cond: (cdeletronicfilecfg = cfg.cdeletronicfilecfg)
  • Filter: (oidform IS NOT NULL)
  • Rows Removed by Filter: 1
Planning time : 19.450 ms
Execution time : 46,714.332 ms