explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dovF

Settings
# exclusive inclusive rows x rows loops node
1. 5.124 1,133.754 ↑ 1.0 1 1

Aggregate (cost=44,070.83..44,070.84 rows=1 width=32) (actual time=1,133.754..1,133.754 rows=1 loops=1)

2. 0.483 1,128.630 ↓ 1.4 50 1

Subquery Scan on tt (cost=29,865.59..44,070.74 rows=35 width=1,467) (actual time=1,036.762..1,128.630 rows=50 loops=1)

3. 0.184 1,128.147 ↓ 1.4 50 1

Limit (cost=29,865.59..44,070.39 rows=35 width=1,711) (actual time=1,036.742..1,128.147 rows=50 loops=1)

4.          

Initplan (for Limit)

5. 0.008 0.008 ↑ 1.0 1 1

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

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

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

  • Filter: ((brief)::text = 'KEYWORDS'::text)
  • Rows Removed by Filter: 21
7. 1.179 1,127.944 ↓ 1.4 50 1

WindowAgg (cost=29,863.22..44,068.01 rows=35 width=1,711) (actual time=1,036.741..1,127.944 rows=50 loops=1)

8. 0.401 1,035.165 ↓ 5.9 207 1

Nested Loop Left Join (cost=29,863.22..29,889.64 rows=35 width=1,443) (actual time=1,033.229..1,035.165 rows=207 loops=1)

  • Join Filter: (tse.set_id = tsi.set_id)
  • Rows Removed by Join Filter: 1,656
9. 0.212 1,034.557 ↓ 5.9 207 1

Nested Loop Left Join (cost=29,863.22..29,884.43 rows=35 width=1,383) (actual time=1,033.204..1,034.557 rows=207 loops=1)

10. 0.140 1,034.138 ↓ 5.9 207 1

Nested Loop Left Join (cost=29,863.07..29,878.47 rows=35 width=1,351) (actual time=1,033.172..1,034.138 rows=207 loops=1)

11. 0.192 1,033.377 ↓ 5.9 207 1

Merge Left Join (cost=29,862.65..29,863.03 rows=35 width=1,331) (actual time=1,033.157..1,033.377 rows=207 loops=1)

  • Merge Cond: (i.item_id = tsi.item_id)
12. 0.344 1,033.123 ↓ 5.9 207 1

Sort (cost=29,860.19..29,860.27 rows=35 width=1,327) (actual time=1,033.093..1,033.123 rows=207 loops=1)

  • Sort Key: i.item_id DESC
  • Sort Method: quicksort Memory: 111kB
13. 0.142 1,032.779 ↓ 5.9 207 1

Hash Join (cost=29,836.74..29,859.29 rows=35 width=1,327) (actual time=1,031.085..1,032.779 rows=207 loops=1)

  • Hash Cond: (i.cat_id = c.cat_id)
14. 0.278 1,032.618 ↓ 5.3 211 1

Nested Loop (cost=29,835.55..29,857.94 rows=40 width=291) (actual time=1,031.045..1,032.618 rows=211 loops=1)

15. 0.378 1,031.074 ↓ 5.3 211 1

HashAggregate (cost=29,835.13..29,835.53 rows=40 width=8) (actual time=1,030.998..1,031.074 rows=211 loops=1)

  • Group Key: ip.item_id
16. 0.080 1,030.696 ↓ 7.3 291 1

Nested Loop (cost=1,000.43..29,835.03 rows=40 width=8) (actual time=264.186..1,030.696 rows=291 loops=1)

17. 12.458 1,026.008 ↓ 3.5 384 1

Gather (cost=1,000.00..24,487.06 rows=110 width=4) (actual time=31.301..1,026.008 rows=384 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 1,013.550 1,013.550 ↓ 2.8 128 3 / 3

Parallel Seq Scan on t_prop_value pv (cost=0.00..23,476.06 rows=46 width=4) (actual time=42.464..1,013.550 rows=128 loops=3)

  • Filter: ((value)::text ~* 'чапаев'::text)
  • Rows Removed by Filter: 371,396
19. 4.608 4.608 ↑ 16.0 1 384

Index Scan using ip_pv_idx on t_item_prop ip (cost=0.43..48.46 rows=16 width=12) (actual time=0.011..0.012 rows=1 loops=384)

  • Index Cond: (prop_value_id = pv.prop_value_id)
  • Filter: (prop_id = $13)
  • Rows Removed by Filter: 2
20. 1.266 1.266 ↑ 1.0 1 211

Index Scan using t_item_pkey on t_item i (cost=0.42..0.56 rows=1 width=291) (actual time=0.006..0.006 rows=1 loops=211)

  • Index Cond: (item_id = ip.item_id)
21. 0.007 0.019 ↑ 1.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.012 0.012 ↑ 1.0 7 1

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

  • Filter: ((brief)::text <> 'AUTHORS'::text)
  • Rows Removed by Filter: 1
23. 0.043 0.062 ↑ 1.1 35 1

Sort (cost=2.46..2.56 rows=40 width=8) (actual time=0.058..0.062 rows=35 loops=1)

  • Sort Key: tsi.item_id DESC
  • Sort Method: quicksort Memory: 26kB
24. 0.019 0.019 ↑ 1.0 40 1

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

25. 0.621 0.621 ↑ 1.0 1 207

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

  • Index Cond: (i.manufacturer_id = manufacturer_id)
26. 0.207 0.207 ↓ 0.0 0 207

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

  • Index Cond: ((item_id = i.item_id) AND (pl_id = 1))
27. 0.199 0.207 ↑ 1.0 8 207

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

28. 0.008 0.008 ↑ 1.0 8 1

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

29.          

SubPlan (for WindowAgg)

30. 0.050 0.300 ↑ 1.0 1 50

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

31. 0.100 0.250 ↓ 0.0 0 50

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

32. 0.150 0.150 ↓ 0.0 0 50

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

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 10
33. 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)
34. 0.050 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)

35. 0.000 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)

36. 0.100 0.100 ↓ 0.0 0 50

Seq Scan on t_rating_link rl_1 (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
37. 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)
38. 41.856 82.750 ↑ 1.0 1 50

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

39. 2.400 34.000 ↓ 1.6 152 50

Hash Join (cost=70.17..92.83 rows=98 width=709) (actual time=0.636..0.680 rows=152 loops=50)

  • Hash Cond: (pt.prop_type_id = p_1.type)
40. 0.100 0.100 ↑ 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.002 rows=6 loops=50)

41. 2.400 31.500 ↓ 6.6 152 50

Hash (cost=69.88..69.88 rows=23 width=677) (actual time=0.630..0.630 rows=152 loops=50)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
42. 5.800 29.100 ↓ 6.6 152 50

Nested Loop Left Join (cost=3.75..69.88 rows=23 width=677) (actual time=0.036..0.582 rows=152 loops=50)

43. 2.073 8.150 ↓ 6.6 152 50

Hash Join (cost=3.33..9.04 rows=23 width=628) (actual time=0.030..0.163 rows=152 loops=50)

  • Hash Cond: (cp.prop_id = p_1.prop_id)
44. 2.900 6.050 ↓ 6.6 152 50

Hash Right Join (cost=1.83..7.48 rows=23 width=108) (actual time=0.028..0.121 rows=152 loops=50)

  • Hash Cond: (ip_1.prop_id = cp.prop_id)
45. 2.450 2.450 ↓ 5.8 150 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.010..0.049 rows=150 loops=50)

  • Index Cond: (item_id = i.item_id)
46. 0.200 0.700 ↑ 1.2 13 50

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.500 0.500 ↑ 1.2 13 50

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

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 44
48. 0.015 0.027 ↑ 1.0 22 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
49. 0.012 0.012 ↑ 1.0 22 1

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

50. 15.150 15.150 ↑ 1.0 1 7,575

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.002..0.002 rows=1 loops=7,575)

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

SubPlan (for Aggregate)

52. 6.894 6.894 ↑ 1.0 1 2,298

Index Scan using t_item_pkey on t_item ti1 (cost=0.42..2.64 rows=1 width=59) (actual time=0.003..0.003 rows=1 loops=2,298)

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

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

54. 0.150 6.650 ↓ 0.0 0 50

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

55. 0.050 6.500 ↓ 0.0 0 50

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

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
56. 0.150 0.150 ↑ 1.0 1 50

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

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

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

  • Filter: (object_id = i.item_id)
  • Rows Removed by Filter: 1,163
58. 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)
59. 0.050 0.700 ↑ 1.0 1 50

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

60. 0.000 0.650 ↓ 0.0 0 50

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

61. 0.100 0.650 ↓ 0.0 0 50

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

62. 0.300 0.550 ↓ 0.0 0 50

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

  • Sort Key: tt_1.subject_id, ts.name, ts.subject_id
  • Sort Method: quicksort Memory: 25kB
63. 0.050 0.250 ↓ 0.0 0 50

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

  • Merge Cond: (tt_1.subject_id = ts.subject_id)
64. 0.200 0.200 ↓ 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.004..0.004 rows=0 loops=50)

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

66. 0.150 1.000 ↑ 1.0 1 50

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

67.          

Initplan (for Aggregate)

68. 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)
69. 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)
70. 0.192 0.850 ↓ 0.0 0 50

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

  • Hash Cond: (tt_2.tag_id = ttl.tag_id)
71. 0.008 0.008 ↑ 87.0 1 1

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

72. 0.050 0.650 ↓ 0.0 0 50

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
73. 0.600 0.600 ↓ 0.0 0 50

Seq Scan on t_tag_link ttl (cost=0.00..2.82 rows=1 width=8) (actual time=0.012..0.012 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 : 5.626 ms
Execution time : 1,134.535 ms