explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WXjr

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 219,489.220 ↑ 1.0 1 1

Aggregate (cost=272,481,334.20..272,481,334.21 rows=1 width=0) (actual time=219,489.220..219,489.220 rows=1 loops=1)

  • Output: count(*)
2. 0.012 219,489.214 ↓ 3.0 3 1

Nested Loop (cost=253,126,793.53..272,481,334.20 rows=1 width=0) (actual time=216,084.379..219,489.214 rows=3 loops=1)

  • Join Filter: (doc.idtdoc = otr.id_object_type)
3. 0.010 219,488.461 ↓ 3.0 3 1

Nested Loop Left Join (cost=253,126,786.65..272,481,327.27 rows=1 width=4) (actual time=216,083.642..219,488.461 rows=3 loops=1)

  • Output: doc.idtdoc
  • Join Filter: (public.crt.idcrt = fd.id_folder)
4. 2.229 219,488.184 ↓ 3.0 3 1

Merge Join (cost=253,126,740.29..272,481,271.65 rows=1 width=8) (actual time=216,083.425..219,488.184 rows=3 loops=1)

  • Output: doc.idtdoc, fd.id_folder
  • Merge Cond: (doc.iddoc = fd.id_document)
5. 3.758 219,077.221 ↓ 1.5 1,891 1

Merge Left Join (cost=253,126,740.29..274,120,089.19 rows=1,304 width=12) (actual time=186,874.764..219,077.221 rows=1,891 loops=1)

  • Output: doc.iddoc, doc.idtdoc, doc0.iddoc
  • Merge Cond: (doc.iddoc = dp.id_document)
  • Filter: ((dp.id_document IS NULL) OR ((dp.id_document IS NOT NULL) AND COALESCE((COALESCE(bool_and(dpv.show), false)), true)))
6. 10.806 219,073.412 ↑ 1.4 1,891 1

Nested Loop (cost=253,126,514.83..274,119,849.06 rows=2,609 width=12) (actual time=186,874.704..219,073.412 rows=1,891 loops=1)

  • Output: doc.iddoc, doc.idtdoc, doc0.iddoc
7. 2.098 186,839.966 ↑ 1.4 1,891 1

Merge Left Join (cost=253,126,514.83..274,076,715.99 rows=2,609 width=4) (actual time=186,836.189..186,839.966 rows=1,891 loops=1)

  • Output: doc0.iddoc
  • Merge Cond: (doc0.iddoc = fd.id_document)
  • Filter: COALESCE((gand(COALESCE(fpt.traverse, false))), true)
8. 7.488 186,837.825 ↑ 2.8 1,891 1

Sort (cost=1,580,325.66..1,580,338.70 rows=5,218 width=4) (actual time=186,836.142..186,837.825 rows=1,891 loops=1)

  • Output: doc0.iddoc
  • Sort Key: doc0.iddoc
  • Sort Method: quicksort Memory: 138kB
9. 186,830.337 186,830.337 ↑ 2.7 1,907 1

Seq Scan on public.doc0 (cost=0.00..1,580,003.46 rows=5,218 width=4) (actual time=953.265..186,830.337 rows=1,907 loops=1)

  • Output: doc0.iddoc
  • Filter: ((doc0.d0c01)::text ~~* '%31845%'::text)
10. 0.002 0.043 ↓ 0.0 0 1

Materialize (cost=251,546,189.18..272,490,073.85 rows=2,515,151 width=5) (actual time=0.043..0.043 rows=0 loops=1)

  • Output: fd.id_document, (gand(COALESCE(fpt.traverse, false)))
11. 0.001 0.041 ↓ 0.0 0 1

GroupAggregate (cost=251,546,189.18..272,458,634.47 rows=2,515,151 width=5) (actual time=0.041..0.041 rows=0 loops=1)

  • Output: fd.id_document, gand(COALESCE(fpt.traverse, false))
12. 0.029 0.040 ↓ 0.0 0 1

Sort (cost=251,546,189.18..251,750,966.57 rows=81,910,956 width=5) (actual time=0.040..0.040 rows=0 loops=1)

  • Output: fd.id_document, fpt.traverse
  • Sort Key: fd.id_document
  • Sort Method: quicksort Memory: 25kB
13. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=7,768.55..239,660,118.65 rows=81,910,956 width=5) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: fd.id_document, fpt.traverse
14. 0.010 0.011 ↓ 0.0 0 1

Hash Right Join (cost=32.54..71.21 rows=990 width=33) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: fp.path_folder, fpt.traverse
  • Hash Cond: (fpt.id_folder = fp.id_folder)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.folder_permission_traverse fpt (cost=0.26..28.76 rows=740 width=5) (never executed)

  • Output: fpt.id_folder, fpt.id_user, fpt.traverse, fpt.inserted, fpt.modified
  • Filter: (hashed SubPlan 2)
16.          

SubPlan (forSeq Scan)

17. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.26 rows=1 width=0) (never executed)

  • Output: user_and_groups(71)
18. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=19.90..19.90 rows=990 width=36) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: fp.path_folder, fp.id_folder
  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
19. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on public.folder_permission fp (cost=0.00..19.90 rows=990 width=36) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: fp.path_folder, fp.id_folder
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.folder_document fd (cost=7,736.01..241,046.63 rows=82,738 width=81) (never executed)

  • Output: fd.id_folder, fd.id_document, fd.id_user, fd.inserted, fd.path_folder
  • Recheck Cond: (fp.path_folder @> fd.path_folder)
21. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_folder_document_path_folder (cost=0.00..7,715.33 rows=82,738 width=0) (never executed)

  • Index Cond: (fp.path_folder @> fd.path_folder)
22. 32,222.640 32,222.640 ↑ 1.0 1 1,891

Index Scan using pk_doc on public.doc (cost=0.00..16.52 rows=1 width=8) (actual time=17.004..17.040 rows=1 loops=1,891)

  • Output: doc.iddoc, doc.idlib, doc.idtdoc, doc.datacr, doc.idute, doc.documento, doc.versione, doc.stato, doc.idwf, doc.idwfsta, doc.idtrwfsta, doc.x, doc.y, doc.iduteposs, doc.flag, doc.estensione, doc.idprv, doc.idsrv
  • Index Cond: (doc.iddoc = doc0.iddoc)
23. 0.003 0.051 ↓ 0.0 0 1

Sort (cost=225.45..229.53 rows=1,630 width=5) (actual time=0.051..0.051 rows=0 loops=1)

  • Output: dp.id_document, (COALESCE(bool_and(dpv.show), false))
  • Sort Key: dp.id_document
  • Sort Method: quicksort Memory: 25kB
  • Sort Method: quicksort Memory: 25kB
24. 0.006 0.024 ↓ 0.0 0 1

HashAggregate (cost=105.89..122.19 rows=1,630 width=5) (actual time=0.024..0.024 rows=0 loops=1)

  • Output: dp.id_document, COALESCE(bool_and(dpv.show), false)
25. 0.018 0.018 ↓ 0.0 0 1

Hash Right Join (cost=46.94..97.74 rows=1,630 width=5) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: dp.id_document, dpv.show
26. 0.006 0.024 ↓ 0.0 0 1

HashAggregate (cost=105.89..122.19 rows=1,630 width=5) (actual time=0.024..0.024 rows=0 loops=1)

  • Output: dp.id_document, COALESCE(bool_and(dpv.show), false)
27. 0.016 0.018 ↓ 0.0 0 1

Hash Right Join (cost=46.94..97.74 rows=1,630 width=5) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: dp.id_document, dpv.show
  • Hash Cond: (dpv.id_document = dp.id_document)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.document_permission_value dpv (cost=0.26..35.76 rows=1,020 width=5) (never executed)

  • Output: dpv.id_document, dpv.id_user, dpv.show, dpv.modify
  • Filter: (hashed SubPlan 1)
29.          

SubPlan (forSeq Scan)

30. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.26 rows=1 width=0) (never executed)

  • Output: user_and_groups(71)
31. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=26.30..26.30 rows=1,630 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: dp.id_document
  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
32. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.document_permission dp (cost=0.00..26.30 rows=1,630 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dp.id_document
33. 408.734 408.734 ↑ 56.6 32 1

Index Scan using pk_folder_document on public.folder_document fd (cost=0.00..3,292.70 rows=1,811 width=8) (actual time=26.872..408.734 rows=32 loops=1)

  • Output: fd.id_folder, fd.id_document, fd.id_user, fd.inserted, fd.path_folder
  • Index Cond: (fd.id_folder = 6209988)
34. 0.009 0.267 ↓ 0.0 0 3

Nested Loop (cost=46.37..55.60 rows=1 width=4) (actual time=0.089..0.089 rows=0 loops=3)

  • Output: public.crt.idcrt
  • Join Filter: (fp.path_folder @> public.crt.pathname)
35. 0.096 0.096 ↑ 1.0 1 3

Index Scan using pk_crt on public.crt (cost=0.00..9.18 rows=1 width=78) (actual time=0.031..0.032 rows=1 loops=3)

  • Output: public.crt.idcrt, public.crt.idtcrt, public.crt.idpadre, public.crt.path, public.crt.idute, public.crt.nome, public.crt.descrizione, public.crt.datacr, public.crt.livello, public.crt.idwf, public.crt.idwfsta, public.crt.idtrwfsta, public.crt.x, public.crt.flag, public.crt.numerocrt, public.crt.numerodoc, public.crt.pathname
  • Index Cond: (public.crt.idcrt = 6209988)
36. 0.000 0.162 ↓ 0.0 0 3

GroupAggregate (cost=46.37..46.39 rows=1 width=37) (actual time=0.054..0.054 rows=0 loops=3)

  • Output: fp.id_folder, fp.path_folder, COALESCE(bool_and(fpt.traverse), false)
  • Filter: (NOT COALESCE(bool_and(fpt.traverse), false))
37.          

Initplan (forGroupAggregate)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_crt on public.crt (cost=0.00..9.18 rows=1 width=74) (never executed)

  • Output: public.crt.pathname
  • Index Cond: (public.crt.idcrt = 6209988)
39. 0.160 0.162 ↓ 0.0 0 3

Sort (cost=37.18..37.19 rows=1 width=37) (actual time=0.054..0.054 rows=0 loops=3)

  • Output: fp.id_folder, fp.path_folder, fpt.traverse
  • Sort Key: fp.id_folder, fp.path_folder
  • Sort Method: quicksort Memory: 25kB
40. 0.001 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.57..37.17 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: fp.id_folder, fp.path_folder, fpt.traverse
41. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.folder_permission fp (cost=0.00..22.38 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: fp.id_folder, fp.path_folder, fp.inserted, fp.modified
  • Filter: ($4 @> fp.path_folder)
42. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.folder_permission_traverse fpt (cost=4.57..14.71 rows=7 width=5) (never executed)

  • Output: fpt.id_folder, fpt.id_user, fpt.traverse, fpt.inserted, fpt.modified
  • Recheck Cond: (fp.id_folder = fpt.id_folder)
  • Filter: (hashed SubPlan 3)
43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_folder_permission_traverse (cost=0.00..4.30 rows=7 width=0) (never executed)

  • Index Cond: (fp.id_folder = fpt.id_folder)
44.          

SubPlan (forBitmap Heap Scan)

45. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.26 rows=1 width=0) (never executed)

  • Output: user_and_groups(71)
46. 0.009 0.741 ↓ 2.0 4 3

Unique (cost=6.88..6.88 rows=2 width=4) (actual time=0.244..0.247 rows=4 loops=3)

  • Output: otr.id_object_type
47. 0.013 0.732 ↓ 3.0 6 3

Sort (cost=6.88..6.88 rows=2 width=4) (actual time=0.243..0.244 rows=6 loops=3)

  • Output: otr.id_object_type
  • Sort Key: otr.id_object_type
  • Sort Method: quicksort Memory: 25kB
48. 0.065 0.719 ↓ 3.0 6 1

Hash Semi Join (cost=0.28..6.87 rows=2 width=4) (actual time=0.650..0.719 rows=6 loops=1)

  • Output: otr.id_object_type
  • Hash Cond: (otr.id_user = (user_and_groups(71)))
49. 0.054 0.054 ↓ 1.0 286 1

Seq Scan on public.object_type_rule otr (cost=0.00..5.82 rows=282 width=8) (actual time=0.013..0.054 rows=286 loops=1)

  • Output: otr.id_object_type_rule, otr.object_type, otr.id_object_type, otr.id_user, otr.enable_command, otr.inserted, otr.updated
50. 0.004 0.600 ↓ 5.0 5 1

Hash (cost=0.27..0.27 rows=1 width=4) (actual time=0.600..0.600 rows=5 loops=1)

  • Output: (user_and_groups(71))
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.596 0.596 ↓ 5.0 5 1

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.589..0.596 rows=5 loops=1)

  • Output: user_and_groups(71)