explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z5rK

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,808.858 ↑ 1.0 1 1

Aggregate (cost=67,709.32..67,709.33 rows=1 width=32) (actual time=2,808.858..2,808.858 rows=1 loops=1)

2. 0.001 2,808.854 ↓ 0.0 0 1

Subquery Scan on tt (cost=3.80..67,709.19 rows=50 width=1,467) (actual time=2,808.854..2,808.854 rows=0 loops=1)

3. 0.000 2,808.853 ↓ 0.0 0 1

Limit (cost=3.80..67,708.69 rows=50 width=1,711) (actual time=2,808.853..2,808.853 rows=0 loops=1)

4.          

Initplan (for Limit)

5. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((brief)::text = 'ITEM'::text)
6. 0.017 0.017 ↑ 1.0 1 1

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

  • Filter: ((brief)::text = 'AUTHORS'::text)
  • Rows Removed by Filter: 21
7. 0.002 2,808.851 ↓ 0.0 0 1

WindowAgg (cost=1.43..2,392,692.15 rows=1,767 width=1,711) (actual time=2,808.851..2,808.851 rows=0 loops=1)

8. 0.001 2,808.849 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..1,676,886.87 rows=1,767 width=1,443) (actual time=2,808.849..2,808.849 rows=0 loops=1)

  • Join Filter: (tse.set_id = tsi.set_id)
9. 0.000 2,808.848 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..1,676,677.84 rows=1,767 width=1,383) (actual time=2,808.848..2,808.848 rows=0 loops=1)

  • Join Filter: (i.item_id = tsi.item_id)
10. 0.001 2,808.848 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..1,675,616.14 rows=1,767 width=1,379) (actual time=2,808.848..2,808.848 rows=0 loops=1)

11. 0.000 2,808.847 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.28..1,675,315.71 rows=1,767 width=1,347) (actual time=2,808.847..2,808.847 rows=0 loops=1)

12. 0.001 2,808.847 ↓ 0.0 0 1

Nested Loop (cost=0.86..1,674,536.23 rows=1,767 width=1,327) (actual time=2,808.847..2,808.847 rows=0 loops=1)

  • Join Filter: (i.cat_id = c.cat_id)
13. 0.002 2,808.846 ↓ 0.0 0 1

Merge Semi Join (cost=0.86..1,674,342.86 rows=2,020 width=291) (actual time=2,808.846..2,808.846 rows=0 loops=1)

  • Merge Cond: (i.item_id = ip.item_id)
14. 0.010 0.010 ↑ 684,645.0 1 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..0.010 rows=1 loops=1)

15. 581.904 2,808.834 ↓ 0.0 0 1

Index Scan Backward using t_item_prop_item_id_idx on t_item_prop ip (cost=0.43..1,644,325.47 rows=2,020 width=8) (actual time=2,808.834..2,808.834 rows=0 loops=1)

  • Index Cond: (prop_id = $13)
  • Filter: (object_id = (SubPlan 13))
  • Rows Removed by Filter: 742,310
16.          

SubPlan (for Index Scan Backward)

17. 2,226.914 2,226.930 ↓ 0.0 0 742,310

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

  • Index Cond: (item_id = ip.item_id)
  • Filter: (((name)::text ~* '^иванов'::text) AND (cat_id = $14))
  • Rows Removed by Filter: 1
18.          

Initplan (for Index Scan)

19. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on t_cat c2 (cost=0.00..1.10 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)

  • Filter: ((brief)::text = 'AUTHORS'::text)
  • Rows Removed by Filter: 7
20. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.14 rows=7 width=1,036) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_cat c (cost=0.00..1.10 rows=7 width=1,036) (never executed)

  • Filter: ((brief)::text <> 'AUTHORS'::text)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using t_manufacturer_pkey on t_manufacturer m (cost=0.42..0.44 rows=1 width=28) (never executed)

  • Index Cond: (i.manufacturer_id = manufacturer_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using pl_item on t_pline p (cost=0.15..0.17 rows=1 width=40) (never executed)

  • Index Cond: ((item_id = i.item_id) AND (pl_id = 1))
24. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.60 rows=40 width=8) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_set_items tsi (cost=0.00..1.40 rows=40 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.12 rows=8 width=68) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_set tse (cost=0.00..1.08 rows=8 width=68) (never executed)

28.          

SubPlan (for WindowAgg)

29. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.24..2.25 rows=1 width=32) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.23 rows=1 width=4) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_rating_link rl (cost=0.00..1.12 rows=1 width=4) (never executed)

  • Filter: (object_id = i.item_id)
32. 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)
33. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.24..2.25 rows=1 width=32) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.23 rows=1 width=4) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_rating_link rl_1 (cost=0.00..1.12 rows=1 width=4) (never executed)

  • Filter: (object_id = i.item_id)
36. 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)
37. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=352.54..352.55 rows=1 width=32) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=70.17..92.83 rows=98 width=709) (never executed)

  • Hash Cond: (pt.prop_type_id = p_1.type)
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_prop_type pt (cost=0.00..18.50 rows=850 width=36) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Hash (cost=69.88..69.88 rows=23 width=677) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=3.75..69.88 rows=23 width=677) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3.33..9.04 rows=23 width=628) (never executed)

  • Hash Cond: (cp.prop_id = p_1.prop_id)
43. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=1.83..7.48 rows=23 width=108) (never executed)

  • Hash Cond: (ip_1.prop_id = cp.prop_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using itm_id_idx on t_item_prop ip_1 (cost=0.43..5.75 rows=26 width=108) (never executed)

  • Index Cond: (item_id = i.item_id)
45. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.20..1.20 rows=16 width=4) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_cat_prop cp (cost=0.00..1.20 rows=16 width=4) (never executed)

  • Filter: (cat_id = i.cat_id)
47. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.22..1.22 rows=22 width=524) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_prop p_1 (cost=0.00..1.22 rows=22 width=524) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using t_prop_value_pkey on t_prop_value pv (cost=0.43..2.65 rows=1 width=53) (never executed)

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

SubPlan (for Aggregate)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using t_item_pkey on t_item ti1 (cost=0.42..2.64 rows=1 width=59) (never executed)

  • Index Cond: (item_id = ip_1.object_id)
52. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=29.24..29.25 rows=1 width=32) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..29.24 rows=1 width=100) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..26.74 rows=1 width=24) (never executed)

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
55. 0.000 0.000 ↓ 0.0 0

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

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

Seq Scan on t_file_link fl (cost=0.00..25.60 rows=3 width=24) (never executed)

  • Filter: (object_id = i.item_id)
57. 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)
58. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=11.64..11.65 rows=1 width=32) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Subquery Scan on affiliates (cost=11.48..11.62 rows=7 width=72) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Unique (cost=11.48..11.55 rows=7 width=48) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.48..11.50 rows=7 width=48) (never executed)

  • Sort Key: tt_1.subject_id, ts.name, ts.subject_id
62. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=0.93..11.38 rows=7 width=48) (never executed)

  • Merge Cond: (tt_1.subject_id = ts.subject_id)
63. 0.000 0.000 ↓ 0.0 0

Index Only Scan using t_tran_item_id_idx on t_tran tt_1 (cost=0.42..9.35 rows=7 width=4) (never executed)

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

65. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=7.11..7.12 rows=1 width=32) (never executed)

66.          

Initplan (for Aggregate)

67. 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)
68. 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)
69. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=2.83..4.94 rows=1 width=20) (never executed)

  • Hash Cond: (tt_2.tag_id = ttl.tag_id)
70. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_tag tt_2 (cost=0.00..1.87 rows=87 width=28) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.82..2.82 rows=1 width=8) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_tag_link ttl (cost=0.00..2.82 rows=1 width=8) (never executed)

  • Filter: ((object_id = i.item_id) AND (object_type_id = $10) AND (link_type_id = $11))
Planning time : 5.298 ms
Execution time : 2,809.338 ms