explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CDUt : 5

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 0.487 ↑ 3.0 1 1

GroupAggregate (cost=107.91..108.02 rows=3 width=23) (actual time=0.487..0.487 rows=1 loops=1)

  • Group Key: dmdev_grouping.value
2. 0.002 0.485 ↓ 1.7 5 1

Unique (cost=107.91..107.94 rows=3 width=86) (actual time=0.483..0.485 rows=5 loops=1)

3. 0.021 0.483 ↓ 2.3 7 1

Sort (cost=107.91..107.92 rows=3 width=86) (actual time=0.482..0.483 rows=7 loops=1)

  • Sort Key: dmdev_grouping.value, document.uuid, document_revision.title
  • Sort Method: quicksort Memory: 25kB
4. 0.009 0.462 ↓ 2.3 7 1

Hash Join (cost=70.34..107.89 rows=3 width=86) (actual time=0.406..0.462 rows=7 loops=1)

  • Hash Cond: ((dmde_grouping.uuid)::text = (dmdev_grouping.metadata_entry_uuid)::text)
5. 0.009 0.282 ↓ 2.3 7 1

Hash Semi Join (cost=33.95..71.41 rows=3 width=108) (actual time=0.227..0.282 rows=7 loops=1)

  • Hash Cond: ((doc_cat.parent_uuid)::text = (recursive_category.uuid)::text)
6. 0.007 0.228 ↓ 2.3 7 1

Nested Loop (cost=15.70..53.12 rows=3 width=624) (actual time=0.176..0.228 rows=7 loops=1)

7. 0.005 0.200 ↓ 1.8 7 1

Nested Loop (cost=15.56..48.66 rows=4 width=627) (actual time=0.162..0.200 rows=7 loops=1)

  • Join Filter: ((doc_cat.document_uuid)::text = (document.uuid)::text)
8. 0.004 0.181 ↓ 3.5 7 1

Nested Loop (cost=15.42..47.82 rows=2 width=1,143) (actual time=0.154..0.181 rows=7 loops=1)

9. 0.027 0.156 ↓ 2.3 7 1

Hash Join (cost=15.27..45.93 rows=3 width=1,143) (actual time=0.147..0.156 rows=7 loops=1)

  • Hash Cond: ((dmdev_date.metadata_entry_uuid)::text = (dmde_date.uuid)::text)
10. 0.030 0.049 ↑ 1.0 100 1

Bitmap Heap Scan on metadata_value dmdev_date (cost=5.05..35.30 rows=100 width=37) (actual time=0.024..0.049 rows=100 loops=1)

  • Recheck Cond: (value < '2020-10-13'::text)
  • Heap Blocks: exact=21
11. 0.019 0.019 ↑ 1.0 100 1

Bitmap Index Scan on metadata_value_idx (cost=0.00..5.03 rows=100 width=0) (actual time=0.019..0.019 rows=100 loops=1)

  • Index Cond: (value < '2020-10-13'::text)
12. 0.004 0.080 ↓ 1.7 5 1

Hash (cost=10.19..10.19 rows=3 width=1,180) (actual time=0.080..0.080 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
13. 0.007 0.076 ↓ 1.7 5 1

Nested Loop (cost=4.25..10.19 rows=3 width=1,180) (actual time=0.062..0.076 rows=5 loops=1)

14. 0.012 0.054 ↑ 1.0 5 1

Hash Join (cost=4.11..7.04 rows=5 width=1,180) (actual time=0.051..0.054 rows=5 loops=1)

  • Hash Cond: ((dmd_grouping.document_uuid)::text = (doc_cat.document_uuid)::text)
15. 0.010 0.010 ↑ 1.3 49 1

Seq Scan on document_metadata dmd_grouping (cost=0.00..2.64 rows=64 width=74) (actual time=0.005..0.010 rows=49 loops=1)

16. 0.003 0.032 ↑ 1.0 5 1

Hash (cost=4.04..4.04 rows=5 width=1,106) (actual time=0.032..0.032 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.013 0.029 ↑ 1.0 5 1

Hash Join (cost=1.11..4.04 rows=5 width=1,106) (actual time=0.025..0.029 rows=5 loops=1)

  • Hash Cond: ((dmd_date.document_uuid)::text = (doc_cat.document_uuid)::text)
18. 0.008 0.008 ↑ 1.3 49 1

Seq Scan on document_metadata dmd_date (cost=0.00..2.64 rows=64 width=74) (actual time=0.002..0.008 rows=49 loops=1)

19. 0.004 0.008 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=1,032) (actual time=0.008..0.008 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.004 0.004 ↓ 1.8 9 1

Seq Scan on category doc_cat (cost=0.00..1.05 rows=5 width=1,032) (actual time=0.002..0.004 rows=9 loops=1)

21. 0.015 0.015 ↑ 1.0 1 5

Index Scan using document_metadata_uuid_idx on metadata_entry dmde_date (cost=0.14..0.62 rows=1 width=74) (actual time=0.003..0.003 rows=1 loops=5)

  • Index Cond: ((document_metadata_uuid)::text = (dmd_date.uuid)::text)
  • Filter: ((metadata_uuid)::text = '49a7266e-19c5-4f1e-84c7-fef6a5fe9a27'::text)
  • Rows Removed by Filter: 2
22. 0.021 0.021 ↑ 1.0 1 7

Index Scan using document_metadata_uuid_idx on metadata_entry dmde_grouping (cost=0.14..0.62 rows=1 width=74) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: ((document_metadata_uuid)::text = (dmd_grouping.uuid)::text)
  • Filter: ((metadata_uuid)::text = '777921a8-baa5-4e72-a6b9-5af1715847e3'::text)
  • Rows Removed by Filter: 2
23. 0.014 0.014 ↑ 1.0 1 7

Index Scan using document_pkey on document (cost=0.14..0.41 rows=1 width=74) (actual time=0.002..0.002 rows=1 loops=7)

  • Index Cond: ((uuid)::text = (dmd_date.document_uuid)::text)
  • Filter: ((NOT template) AND (NOT is_archived))
24. 0.021 0.021 ↑ 1.0 1 7

Index Scan using doc_pkey on document_revision (cost=0.14..1.10 rows=1 width=71) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: ((uuid)::text = (document.current_working_revision_uuid)::text)
  • Filter: (is_released OR (version > 0))
25. 0.010 0.045 ↑ 6.4 8 1

Hash (cost=17.61..17.61 rows=51 width=516) (actual time=0.045..0.045 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.035 0.035 ↑ 6.4 8 1

CTE Scan on recursive_category (cost=16.08..17.10 rows=51 width=516) (actual time=0.005..0.035 rows=8 loops=1)

27.          

CTE recursive_category

28. 0.010 0.031 ↑ 6.4 8 1

Recursive Union (cost=0.00..16.08 rows=51 width=1,548) (actual time=0.004..0.031 rows=8 loops=1)

29. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on category (cost=0.00..1.06 rows=1 width=1,548) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: ((uuid)::text = '77788088-8e44-4861-a325-888f01d77734'::text)
  • Rows Removed by Filter: 8
30. 0.010 0.018 ↑ 1.2 4 2

Hash Join (cost=1.11..1.40 rows=5 width=1,548) (actual time=0.008..0.009 rows=4 loops=2)

  • Hash Cond: ((recursive_category_1.uuid)::text = (category_1.parent_uuid)::text)
31. 0.002 0.002 ↑ 2.5 4 2

WorkTable Scan on recursive_category recursive_category_1 (cost=0.00..0.20 rows=10 width=516) (actual time=0.000..0.001 rows=4 loops=2)

32. 0.003 0.006 ↓ 1.6 8 1

Hash (cost=1.05..1.05 rows=5 width=1,548) (actual time=0.006..0.006 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.003 0.003 ↓ 1.8 9 1

Seq Scan on category category_1 (cost=0.00..1.05 rows=5 width=1,548) (actual time=0.002..0.003 rows=9 loops=1)

34. 0.032 0.171 ↑ 1.0 102 1

Hash (cost=35.09..35.09 rows=104 width=52) (actual time=0.171..0.171 rows=102 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
35. 0.139 0.139 ↓ 1.0 105 1

Seq Scan on metadata_value dmdev_grouping (cost=0.00..35.09 rows=104 width=52) (actual time=0.004..0.139 rows=105 loops=1)

  • Filter: is_default
  • Rows Removed by Filter: 504
Planning time : 2.767 ms
Execution time : 0.677 ms