explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4b5r

Settings
# exclusive inclusive rows x rows loops node
1. 0.114 2,670.713 ↑ 1.0 1 1

Aggregate (cost=13,542.95..13,542.96 rows=1 width=32) (actual time=2,670.713..2,670.713 rows=1 loops=1)

2. 0.041 2,670.599 ↑ 1.0 6 1

Subquery Scan on tt (cost=5.50..13,542.93 rows=6 width=1,467) (actual time=2,668.041..2,670.599 rows=6 loops=1)

3. 0.000 2,670.558 ↑ 1.0 6 1

Limit (cost=5.50..13,542.87 rows=6 width=1,711) (actual time=2,668.029..2,670.558 rows=6 loops=1)

4.          

Initplan (for Limit)

5. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on t_object_type ot (cost=0.00..1.10 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 7
6. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on t_prop p_2 (cost=0.00..1.27 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=1)

  • Filter: ((brief)::text = 'GEOGRAPHIC_SUBNOTION'::text)
  • Rows Removed by Filter: 21
7. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on t_prop p_3 (cost=0.00..1.27 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: ((brief)::text = 'GEOGRAPHIC_SUBNOTION'::text)
  • Rows Removed by Filter: 21
8. 14.470 2,670.543 ↑ 34.7 6 1

WindowAgg (cost=1.85..469,297.09 rows=208 width=1,711) (actual time=2,668.027..2,670.543 rows=6 loops=1)

9. 6.590 2,652.827 ↓ 41.0 8,534 1

Nested Loop Left Join (cost=1.85..385,037.05 rows=208 width=1,443) (actual time=71.562..2,652.827 rows=8,534 loops=1)

  • Join Filter: (tse.set_id = tsi.set_id)
  • Rows Removed by Join Filter: 68272
10. 37.397 2,637.703 ↓ 41.0 8,534 1

Nested Loop Left Join (cost=1.85..385,011.45 rows=208 width=1,383) (actual time=71.532..2,637.703 rows=8,534 loops=1)

  • Join Filter: (i.item_id = tsi.item_id)
  • Rows Removed by Join Filter: 341360
11. 4.282 2,583.238 ↓ 41.0 8,534 1

Nested Loop Left Join (cost=1.85..384,885.15 rows=208 width=1,379) (actual time=71.505..2,583.238 rows=8,534 loops=1)

12. 8.585 2,570.422 ↓ 41.0 8,534 1

Nested Loop Left Join (cost=1.70..384,849.78 rows=208 width=1,347) (actual time=71.463..2,570.422 rows=8,534 loops=1)

13. 7.786 2,536.235 ↓ 41.0 8,534 1

Nested Loop (cost=1.28..384,758.03 rows=208 width=1,327) (actual time=71.456..2,536.235 rows=8,534 loops=1)

  • Join Filter: (i.cat_id = c.cat_id)
  • Rows Removed by Join Filter: 14136
14. 62.475 2,528.449 ↓ 37.0 8,807 1

Merge Semi Join (cost=1.28..384,734.28 rows=238 width=291) (actual time=71.439..2,528.449 rows=8,807 loops=1)

  • Merge Cond: (i.item_id = ip.item_id)
15. 354.641 354.641 ↑ 1.2 575,555 1

Index Scan Backward using t_item_pkey on t_item i (cost=0.42..33,921.23 rows=684,645 width=291) (actual time=0.010..354.641 rows=575,555 loops=1)

16. 349.391 2,111.333 ↓ 37.0 8,817 1

Nested Loop (cost=0.86..354,739.17 rows=238 width=8) (actual time=65.189..2,111.333 rows=8,817 loops=1)

17. 721.080 721.080 ↓ 1.3 520,431 1

Index Only Scan Backward using t_item_prop_item_id_idx on t_item_prop ip (cost=0.43..150,106.02 rows=404,067 width=12) (actual time=0.046..721.080 rows=520,431 loops=1)

  • Index Cond: (prop_id = $13)
  • Heap Fetches: 520431
18. 1,040.862 1,040.862 ↓ 0.0 0 520,431

Index Scan using t_prop_value_pkey on t_prop_value pv (cost=0.43..0.51 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=520,431)

  • Index Cond: (prop_value_id = ip.prop_value_id)
  • Filter: (((value)::text ~* 'чувашия'::text) AND (prop_id = $14))
  • Rows Removed by Filter: 1
19. 0.000 0.000 ↑ 2.3 3 8,807

Materialize (cost=0.00..1.14 rows=7 width=1,036) (actual time=0.000..0.000 rows=3 loops=8,807)

20. 0.014 0.014 ↑ 1.0 7 1

Seq Scan on t_cat c (cost=0.00..1.10 rows=7 width=1,036) (actual time=0.009..0.014 rows=7 loops=1)

  • Filter: ((brief)::text <> 'AUTHORS'::text)
  • Rows Removed by Filter: 1
21. 25.602 25.602 ↑ 1.0 1 8,534

Index Scan using t_manufacturer_pkey on t_manufacturer m (cost=0.42..0.44 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=8,534)

  • Index Cond: (i.manufacturer_id = manufacturer_id)
22. 8.534 8.534 ↓ 0.0 0 8,534

Index Scan using pl_item on t_pline p (cost=0.15..0.17 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=8,534)

  • Index Cond: ((item_id = i.item_id) AND (pl_id = 1))
23. 17.053 17.068 ↑ 1.0 40 8,534

Materialize (cost=0.00..1.60 rows=40 width=8) (actual time=0.000..0.002 rows=40 loops=8,534)

24. 0.015 0.015 ↑ 1.0 40 1

Seq Scan on t_set_items tsi (cost=0.00..1.40 rows=40 width=8) (actual time=0.010..0.015 rows=40 loops=1)

25. 8.510 8.534 ↑ 1.0 8 8,534

Materialize (cost=0.00..1.12 rows=8 width=68) (actual time=0.000..0.001 rows=8 loops=8,534)

26. 0.024 0.024 ↑ 1.0 8 1

Seq Scan on t_set tse (cost=0.00..1.08 rows=8 width=68) (actual time=0.023..0.024 rows=8 loops=1)

27.          

SubPlan (for WindowAgg)

28. 0.012 0.042 ↑ 1.0 1 6

Aggregate (cost=2.24..2.25 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=6)

29. 0.006 0.030 ↓ 0.0 0 6

Nested Loop (cost=0.00..2.23 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=6)

30. 0.024 0.024 ↓ 0.0 0 6

Seq Scan on t_rating_link rl (cost=0.00..1.12 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=6)

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 10
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_object_type ot1 (cost=0.00..1.10 rows=1 width=0) (never executed)

  • Filter: ((brief)::text = 'ITEM'::text)
32. 0.006 0.018 ↑ 1.0 1 6

Aggregate (cost=2.24..2.25 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=6)

33. 0.006 0.012 ↓ 0.0 0 6

Nested Loop (cost=0.00..2.23 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=6)

34. 0.006 0.006 ↓ 0.0 0 6

Seq Scan on t_rating_link rl_1 (cost=0.00..1.12 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=6)

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 10
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_object_type ot1_1 (cost=0.00..1.10 rows=1 width=0) (never executed)

  • Filter: ((brief)::text = 'ITEM'::text)
36. 0.571 2.244 ↑ 1.0 1 6

Aggregate (cost=352.54..352.55 rows=1 width=32) (actual time=0.374..0.374 rows=1 loops=6)

37. 0.078 1.464 ↑ 5.4 18 6

Hash Join (cost=70.17..92.83 rows=98 width=709) (actual time=0.238..0.244 rows=18 loops=6)

  • Hash Cond: (pt.prop_type_id = p_1.type)
38. 0.018 0.018 ↑ 141.7 6 6

Seq Scan on t_prop_type pt (cost=0.00..18.50 rows=850 width=36) (actual time=0.002..0.003 rows=6 loops=6)

39. 0.042 1.368 ↑ 1.3 18 6

Hash (cost=69.88..69.88 rows=23 width=677) (actual time=0.228..0.228 rows=18 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
40. 0.078 1.326 ↑ 1.3 18 6

Nested Loop Left Join (cost=3.75..69.88 rows=23 width=677) (actual time=0.057..0.221 rows=18 loops=6)

41. 0.041 0.408 ↑ 1.3 18 6

Hash Join (cost=3.33..9.04 rows=23 width=628) (actual time=0.041..0.068 rows=18 loops=6)

  • Hash Cond: (cp.prop_id = p_1.prop_id)
42. 0.090 0.348 ↑ 1.3 18 6

Hash Right Join (cost=1.83..7.48 rows=23 width=108) (actual time=0.035..0.058 rows=18 loops=6)

  • Hash Cond: (ip_1.prop_id = cp.prop_id)
43. 0.186 0.186 ↑ 1.6 16 6

Index Scan using itm_id_idx on t_item_prop ip_1 (cost=0.43..5.75 rows=26 width=108) (actual time=0.017..0.031 rows=16 loops=6)

  • Index Cond: (item_id = i.item_id)
44. 0.018 0.072 ↑ 1.2 13 6

Hash (cost=1.20..1.20 rows=16 width=4) (actual time=0.012..0.012 rows=13 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.054 0.054 ↑ 1.2 13 6

Seq Scan on t_cat_prop cp (cost=0.00..1.20 rows=16 width=4) (actual time=0.005..0.009 rows=13 loops=6)

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 44
46. 0.006 0.019 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=524) (actual time=0.019..0.019 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
47. 0.013 0.013 ↑ 1.0 22 1

Seq Scan on t_prop p_1 (cost=0.00..1.22 rows=22 width=524) (actual time=0.009..0.013 rows=22 loops=1)

48. 0.840 0.840 ↑ 1.0 1 105

Index Scan using t_prop_value_pkey on t_prop_value pv_1 (cost=0.43..2.65 rows=1 width=53) (actual time=0.008..0.008 rows=1 loops=105)

  • Index Cond: (prop_value_id = ip_1.prop_value_id)
49.          

SubPlan (for Aggregate)

50. 0.209 0.209 ↑ 1.0 1 11

Index Scan using t_item_pkey on t_item ti1 (cost=0.42..2.64 rows=1 width=59) (actual time=0.019..0.019 rows=1 loops=11)

  • Index Cond: (item_id = ip_1.object_id)
51. 0.006 0.702 ↑ 1.0 1 6

Aggregate (cost=29.24..29.25 rows=1 width=32) (actual time=0.117..0.117 rows=1 loops=6)

52. 0.006 0.696 ↓ 0.0 0 6

Nested Loop (cost=0.28..29.24 rows=1 width=100) (actual time=0.116..0.116 rows=0 loops=6)

53. 0.012 0.690 ↓ 0.0 0 6

Nested Loop (cost=0.00..26.74 rows=1 width=24) (actual time=0.115..0.115 rows=0 loops=6)

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
54. 0.012 0.012 ↑ 1.0 1 6

Seq Scan on t_object_type ot_1 (cost=0.00..1.10 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=6)

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 7
55. 0.666 0.666 ↓ 0.0 0 6

Seq Scan on t_file_link fl (cost=0.00..25.60 rows=3 width=24) (actual time=0.111..0.111 rows=0 loops=6)

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 1163
56. 0.000 0.000 ↓ 0.0 0

Index Scan using t_file_pkey on t_file f (cost=0.28..2.50 rows=1 width=80) (never executed)

  • Index Cond: (file_id = fl.file_id)
  • Filter: (NOT deleted)
57. 0.024 0.108 ↑ 1.0 1 6

Aggregate (cost=11.64..11.65 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=6)

58. 0.000 0.084 ↓ 0.0 0 6

Subquery Scan on affiliates (cost=11.48..11.62 rows=7 width=72) (actual time=0.014..0.014 rows=0 loops=6)

59. 0.006 0.084 ↓ 0.0 0 6

Unique (cost=11.48..11.55 rows=7 width=48) (actual time=0.014..0.014 rows=0 loops=6)

60. 0.036 0.078 ↓ 0.0 0 6

Sort (cost=11.48..11.50 rows=7 width=48) (actual time=0.013..0.013 rows=0 loops=6)

  • Sort Key: tt_1.subject_id, ts.name, ts.subject_id
  • Sort Method: quicksort Memory: 25kB
61. 0.006 0.042 ↓ 0.0 0 6

Merge Left Join (cost=0.93..11.38 rows=7 width=48) (actual time=0.007..0.007 rows=0 loops=6)

  • Merge Cond: (tt_1.subject_id = ts.subject_id)
62. 0.036 0.036 ↓ 0.0 0 6

Index Only Scan using t_tran_item_id_idx on t_tran tt_1 (cost=0.42..9.35 rows=7 width=4) (actual time=0.006..0.006 rows=0 loops=6)

  • Index Cond: (item_id = i.item_id)
  • Heap Fetches: 0
63. 0.000 0.000 ↓ 0.0 0

Index Scan using t_subject_pkey on t_subject ts (cost=0.14..5.64 rows=79 width=44) (never executed)

64. 0.012 0.132 ↑ 1.0 1 6

Aggregate (cost=7.11..7.12 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=6)

65.          

Initplan (for Aggregate)

66. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_object_type ot1_2 (cost=0.00..1.10 rows=1 width=4) (never executed)

  • Filter: ((brief)::text = 'ITEM'::text)
67. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_link_type tlt (cost=0.00..1.06 rows=1 width=8) (never executed)

  • Filter: ((brief)::text = 'TAG'::text)
68. 0.026 0.120 ↓ 0.0 0 6

Hash Semi Join (cost=2.83..4.94 rows=1 width=20) (actual time=0.020..0.020 rows=0 loops=6)

  • Hash Cond: (tt_2.tag_id = ttl.tag_id)
69. 0.010 0.010 ↑ 87.0 1 1

Seq Scan on t_tag tt_2 (cost=0.00..1.87 rows=87 width=28) (actual time=0.010..0.010 rows=1 loops=1)

70. 0.006 0.084 ↓ 0.0 0 6

Hash (cost=2.82..2.82 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
71. 0.078 0.078 ↓ 0.0 0 6

Seq Scan on t_tag_link ttl (cost=0.00..2.82 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=6)

  • Filter: ((object_id = i.item_id) AND (object_type_id = $10) AND (link_type_id = $11))
  • Rows Removed by Filter: 104
Planning time : 5.507 ms
Execution time : 2,671.888 ms