explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M2pf

Settings
# exclusive inclusive rows x rows loops node
1. 0.899 141.124 ↑ 1.0 1 1

Aggregate (cost=57,618.47..57,618.48 rows=1 width=32) (actual time=141.124..141.124 rows=1 loops=1)

2. 0.123 140.225 ↑ 1.0 50 1

Subquery Scan on tt (cost=37,334.49..57,618.34 rows=50 width=1,467) (actual time=122.568..140.225 rows=50 loops=1)

3. 0.017 140.102 ↑ 1.0 50 1

Limit (cost=37,334.49..57,617.84 rows=50 width=1,711) (actual time=122.556..140.102 rows=50 loops=1)

4.          

Initplan (for Limit)

5. 0.005 0.005 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((brief)::text = 'GEOGRAPHIC_SUBNOTION'::text)
  • Rows Removed by Filter: 21
8. 10.711 140.069 ↑ 4.2 50 1

WindowAgg (cost=37,330.84..121,709.60 rows=208 width=1,711) (actual time=122.555..140.069 rows=50 loops=1)

9. 5.069 111.608 ↓ 41.0 8,534 1

Nested Loop Left Join (cost=37,330.84..37,449.56 rows=208 width=1,443) (actual time=68.928..111.608 rows=8,534 loops=1)

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

Merge Left Join (cost=37,330.84..37,423.97 rows=208 width=1,383) (actual time=68.915..98.005 rows=8,534 loops=1)

  • Merge Cond: (i.item_id = tsi.item_id)
11. 3.980 95.568 ↓ 41.0 8,534 1

Nested Loop Left Join (cost=37,328.37..37,420.77 rows=208 width=1,379) (actual time=68.883..95.568 rows=8,534 loops=1)

12. 2.843 74.520 ↓ 41.0 8,534 1

Merge Left Join (cost=37,327.95..37,329.02 rows=208 width=1,359) (actual time=68.877..74.520 rows=8,534 loops=1)

  • Merge Cond: (i.item_id = p.item_id)
13. 16.305 71.660 ↓ 41.0 8,534 1

Sort (cost=37,318.71..37,319.23 rows=208 width=1,327) (actual time=68.855..71.660 rows=8,534 loops=1)

  • Sort Key: i.item_id DESC
  • Sort Method: external merge Disk: 2824kB
14. 3.031 55.355 ↓ 41.0 8,534 1

Hash Join (cost=37,174.44..37,310.70 rows=208 width=1,327) (actual time=17.664..55.355 rows=8,534 loops=1)

  • Hash Cond: (i.cat_id = c.cat_id)
15. 5.727 52.309 ↓ 37.0 8,807 1

Nested Loop (cost=37,173.26..37,308.55 rows=238 width=291) (actual time=17.640..52.309 rows=8,807 loops=1)

16. 5.793 20.161 ↓ 37.0 8,807 1

HashAggregate (cost=37,172.83..37,175.21 rows=238 width=8) (actual time=17.622..20.161 rows=8,807 loops=1)

  • Group Key: ip.item_id
17. 1.121 14.368 ↓ 37.0 8,817 1

Nested Loop (cost=1,196.87..37,172.24 rows=238 width=8) (actual time=1.712..14.368 rows=8,817 loops=1)

18. 8.317 8.792 ↑ 59.6 11 1

Bitmap Heap Scan on t_prop_value pv (cost=1,196.44..19,850.88 rows=656 width=4) (actual time=1.696..8.792 rows=11 loops=1)

  • Recheck Cond: (prop_id = $14)
  • Filter: ((value)::text ~* 'чувашия'::text)
  • Rows Removed by Filter: 7240
  • Heap Blocks: exact=128
19. 0.475 0.475 ↑ 9.0 7,251 1

Bitmap Index Scan on t_prop_value_p_id_pv_val_idx (cost=0.00..1,196.27 rows=65,563 width=0) (actual time=0.475..0.475 rows=7,251 loops=1)

  • Index Cond: (prop_id = $14)
20. 4.455 4.455 ↓ 50.1 802 11

Index Scan using ip_pv_idx on t_item_prop ip (cost=0.43..26.24 rows=16 width=12) (actual time=0.007..0.405 rows=802 loops=11)

  • Index Cond: (prop_value_id = pv.prop_value_id)
  • Filter: (prop_id = $13)
21. 26.421 26.421 ↑ 1.0 1 8,807

Index Scan using t_item_pkey on t_item i (cost=0.42..0.56 rows=1 width=291) (actual time=0.003..0.003 rows=1 loops=8,807)

  • Index Cond: (item_id = ip.item_id)
22. 0.005 0.015 ↑ 1.0 7 1

Hash (cost=1.10..1.10 rows=7 width=1,036) (actual time=0.015..0.015 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.010 0.010 ↑ 1.0 7 1

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

  • Filter: ((brief)::text <> 'AUTHORS'::text)
  • Rows Removed by Filter: 1
24. 0.010 0.017 ↓ 0.0 0 1

Sort (cost=9.25..9.26 rows=3 width=40) (actual time=0.017..0.017 rows=0 loops=1)

  • Sort Key: p.item_id DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.002 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on t_pline p (cost=6.05..9.22 rows=3 width=40) (actual time=0.007..0.007 rows=0 loops=1)

  • Recheck Cond: (pl_id = 1)
26. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on t_pline_pline_id_pl_id_key1 (cost=0.00..6.05 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (pl_id = 1)
27. 17.068 17.068 ↑ 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.002..0.002 rows=1 loops=8,534)

  • Index Cond: (i.manufacturer_id = manufacturer_id)
28. 0.024 0.039 ↑ 1.0 40 1

Sort (cost=2.46..2.56 rows=40 width=8) (actual time=0.029..0.039 rows=40 loops=1)

  • Sort Key: tsi.item_id DESC
  • Sort Method: quicksort Memory: 26kB
29. 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)

30. 8.529 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)

31. 0.005 0.005 ↑ 1.0 8 1

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

32.          

SubPlan (for WindowAgg)

33. 0.000 0.150 ↑ 1.0 1 50

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

34. 0.050 0.150 ↓ 0.0 0 50

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

35. 0.100 0.100 ↓ 0.0 0 50

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

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 10
36. 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)
37. 0.000 0.100 ↑ 1.0 1 50

Aggregate (cost=2.24..2.25 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=50)

38. 0.050 0.100 ↓ 0.0 0 50

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

39. 0.050 0.050 ↓ 0.0 0 50

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=50)

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 10
40. 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)
41. 4.834 10.550 ↑ 1.0 1 50

Aggregate (cost=352.54..352.55 rows=1 width=32) (actual time=0.211..0.211 rows=1 loops=50)

42. 0.400 5.400 ↑ 5.4 18 50

Hash Join (cost=70.17..92.83 rows=98 width=709) (actual time=0.102..0.108 rows=18 loops=50)

  • Hash Cond: (pt.prop_type_id = p_1.type)
43. 0.050 0.050 ↑ 141.7 6 50

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

44. 0.300 4.950 ↑ 1.3 18 50

Hash (cost=69.88..69.88 rows=23 width=677) (actual time=0.099..0.099 rows=18 loops=50)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
45. 0.083 4.650 ↑ 1.3 18 50

Nested Loop Left Join (cost=3.75..69.88 rows=23 width=677) (actual time=0.025..0.093 rows=18 loops=50)

46. 0.318 1.900 ↑ 1.3 18 50

Hash Join (cost=3.33..9.04 rows=23 width=628) (actual time=0.020..0.038 rows=18 loops=50)

  • Hash Cond: (cp.prop_id = p_1.prop_id)
47. 0.550 1.550 ↑ 1.3 18 50

Hash Right Join (cost=1.83..7.48 rows=23 width=108) (actual time=0.018..0.031 rows=18 loops=50)

  • Hash Cond: (ip_1.prop_id = cp.prop_id)
48. 0.500 0.500 ↑ 1.6 16 50

Index Scan using itm_id_idx on t_item_prop ip_1 (cost=0.43..5.75 rows=26 width=108) (actual time=0.005..0.010 rows=16 loops=50)

  • Index Cond: (item_id = i.item_id)
49. 0.150 0.500 ↑ 1.2 13 50

Hash (cost=1.20..1.20 rows=16 width=4) (actual time=0.010..0.010 rows=13 loops=50)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.350 0.350 ↑ 1.2 13 50

Seq Scan on t_cat_prop cp (cost=0.00..1.20 rows=16 width=4) (actual time=0.004..0.007 rows=13 loops=50)

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 44
51. 0.023 0.032 ↑ 1.0 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
52. 0.009 0.009 ↑ 1.0 22 1

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

53. 2.667 2.667 ↑ 1.0 1 889

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.003..0.003 rows=1 loops=889)

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

SubPlan (for Aggregate)

55. 0.316 0.316 ↑ 1.0 1 79

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

  • Index Cond: (item_id = ip_1.object_id)
56. 0.050 5.700 ↑ 1.0 1 50

Aggregate (cost=29.24..29.25 rows=1 width=32) (actual time=0.114..0.114 rows=1 loops=50)

57. 0.050 5.650 ↓ 0.0 0 50

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

58. 0.050 5.600 ↓ 0.0 0 50

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

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
59. 0.100 0.100 ↑ 1.0 1 50

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

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 7
60. 5.450 5.450 ↓ 0.0 0 50

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

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 1163
61. 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)
62. 0.050 0.450 ↑ 1.0 1 50

Aggregate (cost=11.64..11.65 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=50)

63. 0.050 0.400 ↓ 0.0 0 50

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

64. 0.000 0.350 ↓ 0.0 0 50

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

65. 0.200 0.350 ↓ 0.0 0 50

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

  • Sort Key: tt_1.subject_id, ts.name, ts.subject_id
  • Sort Method: quicksort Memory: 25kB
66. 0.000 0.150 ↓ 0.0 0 50

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

  • Merge Cond: (tt_1.subject_id = ts.subject_id)
67. 0.150 0.150 ↓ 0.0 0 50

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.003..0.003 rows=0 loops=50)

  • Index Cond: (item_id = i.item_id)
  • Heap Fetches: 0
68. 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)

69. 0.100 0.800 ↑ 1.0 1 50

Aggregate (cost=7.11..7.12 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=50)

70.          

Initplan (for Aggregate)

71. 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)
72. 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)
73. 0.144 0.700 ↓ 0.0 0 50

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

  • Hash Cond: (tt_2.tag_id = ttl.tag_id)
74. 0.006 0.006 ↑ 87.0 1 1

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

75. 0.000 0.550 ↓ 0.0 0 50

Hash (cost=2.82..2.82 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=50)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
76. 0.550 0.550 ↓ 0.0 0 50

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

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