explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BvlZ

Settings
# exclusive inclusive rows x rows loops node
1. 1.322 3,505.404 ↑ 1.0 1 1

Aggregate (cost=77,815.69..77,815.70 rows=1 width=32) (actual time=3,505.404..3,505.404 rows=1 loops=1)

2. 0.159 3,504.082 ↑ 1.0 50 1

Subquery Scan on tt (cost=4.23..77,815.56 rows=50 width=1,467) (actual time=3,482.250..3,504.082 rows=50 loops=1)

3. 0.009 3,503.923 ↑ 1.0 50 1

Limit (cost=4.23..77,815.06 rows=50 width=1,711) (actual time=3,482.230..3,503.923 rows=50 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 = 'AUTHORS'::text)
  • Rows Removed by Filter: 21
7. 14.369 3,503.893 ↑ 7.0 50 1

WindowAgg (cost=1.85..547,790.13 rows=352 width=1,711) (actual time=3,482.229..3,503.893 rows=50 loops=1)

8. 6.425 3,467.074 ↓ 18.6 6,536 1

Nested Loop Left Join (cost=1.85..405,196.21 rows=352 width=1,443) (actual time=3.394..3,467.074 rows=6,536 loops=1)

  • Join Filter: (tse.set_id = tsi.set_id)
  • Rows Removed by Join Filter: 52,284
9. 24.097 3,454.113 ↓ 18.6 6,536 1

Nested Loop Left Join (cost=1.85..405,153.71 rows=352 width=1,383) (actual time=3.380..3,454.113 rows=6,536 loops=1)

  • Join Filter: (i.item_id = tsi.item_id)
  • Rows Removed by Join Filter: 261,439
10. 4.813 3,410.408 ↓ 18.6 6,536 1

Nested Loop Left Join (cost=1.85..404,941.01 rows=352 width=1,379) (actual time=3.336..3,410.408 rows=6,536 loops=1)

11. 3.434 3,399.059 ↓ 18.6 6,536 1

Nested Loop Left Join (cost=1.70..404,881.16 rows=352 width=1,347) (actual time=3.324..3,399.059 rows=6,536 loops=1)

12. 7.757 3,369.481 ↓ 18.6 6,536 1

Nested Loop (cost=1.28..404,723.16 rows=352 width=1,327) (actual time=3.318..3,369.481 rows=6,536 loops=1)

  • Join Filter: (i.cat_id = c.cat_id)
  • Rows Removed by Join Filter: 13,975
13. 64.179 3,361.724 ↓ 16.5 6,649 1

Merge Semi Join (cost=1.28..404,683.77 rows=402 width=291) (actual time=3.301..3,361.724 rows=6,649 loops=1)

  • Merge Cond: (i.item_id = ip.item_id)
14. 348.219 348.219 ↑ 1.2 575,906 1

Index Scan Backward using t_item_pkey on t_item i (cost=0.42..36,547.48 rows=684,645 width=291) (actual time=0.010..348.219 rows=575,906 loops=1)

15. 202.828 2,949.326 ↓ 16.8 6,735 1

Nested Loop (cost=0.86..372,476.09 rows=402 width=8) (actual time=3.086..2,949.326 rows=6,735 loops=1)

16. 519.568 519.568 ↓ 1.8 742,310 1

Index Scan Backward using t_item_prop_item_id_idx on t_item_prop ip (cost=0.43..153,768.07 rows=404,067 width=16) (actual time=0.038..519.568 rows=742,310 loops=1)

  • Index Cond: (prop_id = $13)
17. 2,226.930 2,226.930 ↓ 0.0 0 742,310

Index Scan using t_item_pkey on t_item ti3 (cost=0.42..0.54 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=742,310)

  • Index Cond: (item_id = ip.object_id)
  • Filter: ((name)::text ~* '^иванов'::text)
  • Rows Removed by Filter: 1
18. 0.000 0.000 ↑ 2.3 3 6,649

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

19. 0.009 0.009 ↑ 1.0 7 1

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

  • Filter: ((brief)::text <> 'AUTHORS'::text)
  • Rows Removed by Filter: 1
20. 26.144 26.144 ↑ 1.0 1 6,536

Index Scan using t_manufacturer_pkey on t_manufacturer m (cost=0.42..0.45 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=6,536)

  • Index Cond: (i.manufacturer_id = manufacturer_id)
21. 6.536 6.536 ↓ 0.0 0 6,536

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=6,536)

  • Index Cond: ((item_id = i.item_id) AND (pl_id = 1))
22. 19.595 19.608 ↑ 1.0 40 6,536

Materialize (cost=0.00..1.60 rows=40 width=8) (actual time=0.000..0.003 rows=40 loops=6,536)

23. 0.013 0.013 ↑ 1.0 40 1

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

24. 6.528 6.536 ↑ 1.0 8 6,536

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

25. 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.007..0.008 rows=8 loops=1)

26.          

SubPlan (for WindowAgg)

27. 0.050 0.250 ↑ 1.0 1 50

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

28. 0.100 0.200 ↓ 0.0 0 50

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

29. 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
30. 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)
31. 0.050 0.150 ↑ 1.0 1 50

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

32. 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)

33. 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
34. 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)
35. 5.100 14.900 ↑ 1.0 1 50

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

36. 0.500 9.300 ↑ 5.4 18 50

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

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

38. 0.350 8.700 ↑ 1.3 18 50

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
39. 0.204 8.350 ↑ 1.3 18 50

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

40. 0.328 2.800 ↑ 1.3 18 50

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

  • Hash Cond: (cp.prop_id = p_1.prop_id)
41. 0.650 2.450 ↑ 1.3 18 50

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

  • Hash Cond: (ip_1.prop_id = cp.prop_id)
42. 1.250 1.250 ↑ 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.016..0.025 rows=16 loops=50)

  • Index Cond: (item_id = i.item_id)
43. 0.150 0.550 ↑ 1.2 13 50

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
44. 0.400 0.400 ↑ 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.008 rows=13 loops=50)

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 44
45. 0.009 0.022 ↑ 1.0 22 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
46. 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.010..0.013 rows=22 loops=1)

47. 5.346 5.346 ↑ 1.0 1 891

Index Scan using t_prop_value_pkey on t_prop_value pv (cost=0.43..2.65 rows=1 width=53) (actual time=0.006..0.006 rows=1 loops=891)

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

SubPlan (for Aggregate)

49. 0.500 0.500 ↑ 1.0 1 100

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

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

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

51. 0.050 5.750 ↓ 0.0 0 50

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

52. 0.100 5.700 ↓ 0.0 0 50

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

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
53. 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
54. 5.500 5.500 ↓ 0.0 0 50

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

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

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

57. 0.000 0.450 ↓ 0.0 0 50

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

58. 0.050 0.450 ↓ 0.0 0 50

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

59. 0.200 0.400 ↓ 0.0 0 50

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

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

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

  • Merge Cond: (tt_1.subject_id = ts.subject_id)
61. 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
62. 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)

63. 0.100 0.850 ↑ 1.0 1 50

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

64.          

Initplan (for Aggregate)

65. 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)
66. 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)
67. 0.140 0.750 ↓ 0.0 0 50

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

  • Hash Cond: (tt_2.tag_id = ttl.tag_id)
68. 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)

69. 0.050 0.600 ↓ 0.0 0 50

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
70. 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 : 5.814 ms
Execution time : 3,506.457 ms