explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lhTC

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 925.699 ↑ 50.2 8 1

Sort (cost=21,810.47..21,811.47 rows=402 width=128) (actual time=925.698..925.699 rows=8 loops=1)

  • Sort Key: (array_upper(e.properties, 1)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 26kB
2.          

CTE selection

3. 0.014 0.014 ↑ 100.0 1 1

Result (cost=0.00..0.51 rows=100 width=64) (actual time=0.011..0.014 rows=1 loops=1)

4.          

CTE filter

5. 0.009 0.009 ↓ 0.0 0 1

Result (cost=0.00..0.51 rows=100 width=64) (actual time=0.008..0.009 rows=0 loops=1)

6.          

CTE sel_and_fil

7. 0.006 0.031 ↑ 200.0 1 1

HashAggregate (cost=7.00..9.00 rows=200 width=64) (actual time=0.030..0.031 rows=1 loops=1)

  • Group Key: selection.category, selection.property
8. 0.000 0.025 ↑ 200.0 1 1

Append (cost=0.00..6.00 rows=200 width=64) (actual time=0.014..0.025 rows=1 loops=1)

9. 0.016 0.016 ↑ 100.0 1 1

CTE Scan on selection (cost=0.00..2.00 rows=100 width=64) (actual time=0.014..0.016 rows=1 loops=1)

10. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on filter (cost=0.00..2.00 rows=100 width=64) (actual time=0.009..0.009 rows=0 loops=1)

11.          

CTE exclude

12. 0.008 0.008 ↓ 0.0 0 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.008..0.008 rows=0 loops=1)

13.          

CTE selected_content

14. 0.144 924.460 ↑ 100.4 8 1

GroupAggregate (cost=7,909.97..7,940.08 rows=803 width=61) (actual time=924.382..924.460 rows=8 loops=1)

  • Group Key: connection.content_id, content.title, content.description, content.size, content.crc32, content.created
  • Filter: ((array_agg(DISTINCT ROW(sel_and_fil.category, sel_and_fil.property)::category_property) <@ '{"(type,mobile-list)"}'::category_property[]) AND (array_agg(DISTINCT ROW(sel_and_fil.category, sel_and_fil.property)::category_property) @> '{"(type,mobile-list)"}'::category_property[]))
15. 0.030 924.316 ↑ 100.4 8 1

Sort (cost=7,909.97..7,911.97 rows=803 width=125) (actual time=924.313..924.316 rows=8 loops=1)

  • Sort Key: connection.content_id, content.title, content.description, content.size, content.crc32, content.created
  • Sort Method: quicksort Memory: 26kB
16. 23.693 924.286 ↑ 100.4 8 1

Nested Loop (cost=8.55..7,871.23 rows=803 width=125) (actual time=759.697..924.286 rows=8 loops=1)

17. 16.190 83.105 ↓ 78.8 68,124 1

Nested Loop (cost=8.13..266.44 rows=864 width=68) (actual time=0.213..83.105 rows=68,124 loops=1)

18. 0.360 0.859 ↑ 1.0 1 1

Hash Join (cost=7.70..228.03 rows=1 width=68) (actual time=0.182..0.859 rows=1 loops=1)

  • Hash Cond: ((property.category_id = category.category_id) AND (property.property = sel_and_fil.property))
19. 0.408 0.408 ↑ 1.0 1,561 1

Seq Scan on property (cost=0.00..208.61 rows=1,561 width=24) (actual time=0.016..0.408 rows=1,561 loops=1)

20. 0.006 0.091 ↑ 41.0 1 1

Hash (cost=7.08..7.08 rows=41 width=68) (actual time=0.091..0.091 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.009 0.085 ↑ 41.0 1 1

Hash Join (cost=1.92..7.08 rows=41 width=68) (actual time=0.083..0.085 rows=1 loops=1)

  • Hash Cond: (sel_and_fil.category = category.category)
22. 0.033 0.033 ↑ 200.0 1 1

CTE Scan on sel_and_fil (cost=0.00..4.00 rows=200 width=64) (actual time=0.032..0.033 rows=1 loops=1)

23. 0.023 0.043 ↑ 1.0 40 1

Hash (cost=1.41..1.41 rows=41 width=36) (actual time=0.043..0.043 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.020 0.020 ↑ 1.0 40 1

Seq Scan on category (cost=0.00..1.41 rows=41 width=36) (actual time=0.010..0.020 rows=40 loops=1)

25. 66.056 66.056 ↓ 107.8 68,124 1

Index Only Scan using connection_pkey on connection (cost=0.43..32.10 rows=632 width=8) (actual time=0.022..66.056 rows=68,124 loops=1)

  • Index Cond: (property_id = property.property_id)
  • Heap Fetches: 68124
26. 340.620 817.488 ↓ 0.0 0 68,124

Index Scan using content_pkey on content (cost=0.42..8.79 rows=1 width=61) (actual time=0.012..0.012 rows=0 loops=68,124)

  • Index Cond: (content_id = connection.content_id)
  • Filter: ((user_id IS NULL) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
  • Rows Removed by Filter: 1
27.          

SubPlan (forIndex Scan)

28. 476.868 476.868 ↓ 0.0 0 68,124

Index Scan using users_namespace_identifier_idx on users (cost=0.29..8.31 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=68,124)

  • Index Cond: ((namespace = 'infront'::text) AND (identifier = '119491981'::text))
  • Filter: (user_id = content.user_id)
  • Rows Removed by Filter: 1
29. 0.000 0.000 ↓ 0.0 0

Index Scan using users_namespace_identifier_idx on users users_1 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((namespace = 'infront'::text) AND (identifier = '119491981'::text))
30.          

CTE conntected_properties

31. 0.013 1.131 ↑ 118.9 14 1

Hash Join (cost=230.47..6,768.90 rows=1,664 width=52) (actual time=0.971..1.131 rows=14 loops=1)

  • Hash Cond: (property_1.category_id = category_1.category_id)
32. 0.078 1.073 ↑ 118.9 14 1

Hash Join (cost=228.55..6,744.10 rows=1,664 width=24) (actual time=0.918..1.073 rows=14 loops=1)

  • Hash Cond: (connection_1.property_id = property_1.property_id)
33. 0.009 0.159 ↑ 118.9 14 1

Nested Loop (cost=0.43..6,497.56 rows=1,664 width=8) (actual time=0.014..0.159 rows=14 loops=1)

34. 0.086 0.086 ↑ 100.4 8 1

CTE Scan on selected_content selected_content_1 (cost=0.00..16.06 rows=803 width=4) (actual time=0.001..0.086 rows=8 loops=1)

35. 0.064 0.064 ↑ 1.0 2 8

Index Scan using fki_connection_content_id_fkey on connection connection_1 (cost=0.43..8.05 rows=2 width=8) (actual time=0.007..0.008 rows=2 loops=8)

  • Index Cond: (content_id = selected_content_1.content_id)
36. 0.380 0.836 ↑ 1.0 1,561 1

Hash (cost=208.61..208.61 rows=1,561 width=24) (actual time=0.836..0.836 rows=1,561 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 105kB
37. 0.456 0.456 ↑ 1.0 1,561 1

Seq Scan on property property_1 (cost=0.00..208.61 rows=1,561 width=24) (actual time=0.010..0.456 rows=1,561 loops=1)

38. 0.019 0.045 ↑ 1.0 40 1

Hash (cost=1.41..1.41 rows=41 width=36) (actual time=0.045..0.045 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.026 0.026 ↑ 1.0 40 1

Seq Scan on category category_1 (cost=0.00..1.41 rows=41 width=36) (actual time=0.018..0.026 rows=40 loops=1)

40.          

CTE extra_properties

41. 0.015 0.238 ↑ 1.3 6 1

GroupAggregate (cost=49.42..49.58 rows=8 width=36) (actual time=0.230..0.238 rows=6 loops=1)

  • Group Key: conntected_properties.content_id
42. 0.010 0.223 ↑ 1.3 6 1

Sort (cost=49.42..49.44 rows=8 width=68) (actual time=0.223..0.223 rows=6 loops=1)

  • Sort Key: conntected_properties.content_id
  • Sort Method: quicksort Memory: 25kB
43. 0.023 0.213 ↑ 1.3 6 1

Hash Left Join (cost=3.50..49.30 rows=8 width=68) (actual time=0.037..0.213 rows=6 loops=1)

  • Hash Cond: ((conntected_properties.property = selection_1.property) AND (conntected_properties.category = selection_1.category))
  • Filter: (selection_1.* IS NULL)
  • Rows Removed by Filter: 8
44. 0.170 0.170 ↑ 118.9 14 1

CTE Scan on conntected_properties (cost=0.00..33.28 rows=1,664 width=68) (actual time=0.000..0.170 rows=14 loops=1)

45. 0.009 0.020 ↑ 100.0 1 1

Hash (cost=2.00..2.00 rows=100 width=152) (actual time=0.020..0.020 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.011 0.011 ↑ 100.0 1 1

CTE Scan on selection selection_1 (cost=0.00..2.00 rows=100 width=152) (actual time=0.011..0.011 rows=1 loops=1)

47.          

CTE filtered_categories_raw

48. 0.009 0.983 ↓ 0.0 0 1

Hash Join (cost=3.25..53.17 rows=828 width=68) (actual time=0.982..0.983 rows=0 loops=1)

  • Hash Cond: (conntected_properties_1.category = filter_1.category)
  • Join Filter: (conntected_properties_1.property <> filter_1.property)
49. 0.973 0.973 ↑ 1,664.0 1 1

CTE Scan on conntected_properties conntected_properties_1 (cost=0.00..33.28 rows=1,664 width=68) (actual time=0.973..0.973 rows=1 loops=1)

50. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=2.00..2.00 rows=100 width=64) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
51. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on filter filter_1 (cost=0.00..2.00 rows=100 width=64) (actual time=0.001..0.001 rows=0 loops=1)

52.          

CTE filtered_properties_raw

53. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3.50..49.30 rows=4 width=68) (never executed)

  • Hash Cond: ((conntected_properties_2.category = filter_2.category) AND (conntected_properties_2.property = filter_2.property))
54. 0.000 0.000 ↓ 0.0 0

CTE Scan on conntected_properties conntected_properties_2 (cost=0.00..33.28 rows=1,664 width=68) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.00..2.00 rows=100 width=64) (never executed)

56. 0.000 0.000 ↓ 0.0 0

CTE Scan on filter filter_2 (cost=0.00..2.00 rows=100 width=64) (never executed)

57.          

CTE negjoin

58. 0.007 1.012 ↓ 0.0 0 1

HashAggregate (cost=6,816.03..6,832.71 rows=1,668 width=4) (actual time=1.012..1.012 rows=0 loops=1)

  • Group Key: filtered_categories_raw.content_id
59. 0.000 1.005 ↓ 0.0 0 1

Append (cost=22.96..6,811.86 rows=1,668 width=4) (actual time=1.005..1.005 rows=0 loops=1)

60. 0.001 0.991 ↓ 0.0 0 1

Group (cost=22.96..22.98 rows=4 width=4) (actual time=0.991..0.991 rows=0 loops=1)

  • Group Key: filtered_categories_raw.content_id
61. 0.006 0.990 ↓ 0.0 0 1

Sort (cost=22.96..22.97 rows=4 width=4) (actual time=0.990..0.990 rows=0 loops=1)

  • Sort Key: filtered_categories_raw.content_id
  • Sort Method: quicksort Memory: 25kB
62. 0.001 0.984 ↓ 0.0 0 1

Hash Left Join (cost=0.14..22.92 rows=4 width=4) (actual time=0.984..0.984 rows=0 loops=1)

  • Hash Cond: ((filtered_categories_raw.content_id = filtered_properties_raw.content_id) AND (filtered_categories_raw.category = filtered_properties_raw.category))
  • Filter: (filtered_properties_raw.property IS NULL)
63. 0.983 0.983 ↓ 0.0 0 1

CTE Scan on filtered_categories_raw (cost=0.00..16.56 rows=828 width=36) (actual time=0.983..0.983 rows=0 loops=1)

64. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.08..0.08 rows=4 width=68) (never executed)

65. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_properties_raw (cost=0.00..0.08 rows=4 width=68) (never executed)

66. 0.005 0.014 ↓ 0.0 0 1

Hash Join (cost=233.77..6,772.20 rows=1,664 width=4) (actual time=0.014..0.014 rows=0 loops=1)

  • Hash Cond: (property_2.category_id = category_2.category_id)
67. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=228.55..6,744.10 rows=1,664 width=8) (never executed)

  • Hash Cond: (connection_2.property_id = property_2.property_id)
68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..6,497.56 rows=1,664 width=8) (never executed)

69. 0.000 0.000 ↓ 0.0 0

CTE Scan on selected_content selected_content_2 (cost=0.00..16.06 rows=803 width=4) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Index Scan using fki_connection_content_id_fkey on connection connection_2 (cost=0.43..8.05 rows=2 width=8) (never executed)

  • Index Cond: (content_id = selected_content_2.content_id)
71. 0.000 0.000 ↓ 0.0 0

Hash (cost=208.61..208.61 rows=1,561 width=8) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Seq Scan on property property_2 (cost=0.00..208.61 rows=1,561 width=8) (never executed)

73. 0.000 0.009 ↓ 0.0 0 1

Hash (cost=4.71..4.71 rows=41 width=4) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
74. 0.001 0.009 ↓ 0.0 0 1

Hash Join (cost=1.92..4.71 rows=41 width=4) (actual time=0.009..0.009 rows=0 loops=1)

  • Hash Cond: (exclude.category = category_2.category)
75. 0.008 0.008 ↓ 0.0 0 1

CTE Scan on exclude (cost=0.00..2.00 rows=100 width=32) (actual time=0.008..0.008 rows=0 loops=1)

76. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.41..1.41 rows=41 width=36) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Seq Scan on category category_2 (cost=0.00..1.41 rows=41 width=36) (never executed)

78. 0.013 925.681 ↑ 50.2 8 1

Hash Left Join (cost=54.47..88.82 rows=402 width=128) (actual time=925.673..925.681 rows=8 loops=1)

  • Hash Cond: (selected_content.content_id = e.content_id)
79. 0.017 925.419 ↑ 50.2 8 1

Hash Anti Join (cost=54.21..85.89 rows=402 width=92) (actual time=925.414..925.419 rows=8 loops=1)

  • Hash Cond: (selected_content.content_id = i.content_id)
80. 924.388 924.388 ↑ 100.4 8 1

CTE Scan on selected_content (cost=0.00..16.06 rows=803 width=92) (actual time=924.385..924.388 rows=8 loops=1)

81. 0.000 1.014 ↓ 0.0 0 1

Hash (cost=33.36..33.36 rows=1,668 width=4) (actual time=1.014..1.014 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
82. 1.014 1.014 ↓ 0.0 0 1

CTE Scan on negjoin i (cost=0.00..33.36 rows=1,668 width=4) (actual time=1.014..1.014 rows=0 loops=1)

83. 0.006 0.249 ↑ 1.3 6 1

Hash (cost=0.16..0.16 rows=8 width=36) (actual time=0.249..0.249 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.243 0.243 ↑ 1.3 6 1

CTE Scan on extra_properties e (cost=0.00..0.16 rows=8 width=36) (actual time=0.231..0.243 rows=6 loops=1)