explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bAn0

Settings
# exclusive inclusive rows x rows loops node
1. 0.925 1,130.653 ↑ 1.0 1 1

Aggregate (cost=172,778.15..172,778.16 rows=1 width=32) (actual time=1,130.653..1,130.653 rows=1 loops=1)

2. 0.110 1,129.728 ↑ 1.0 50 1

Subquery Scan on tt (cost=152,493.36..172,778.02 rows=50 width=1,467) (actual time=1,111.488..1,129.728 rows=50 loops=1)

3. 0.000 1,129.618 ↑ 1.0 50 1

Limit (cost=152,493.36..172,777.52 rows=50 width=1,711) (actual time=1,111.476..1,129.618 rows=50 loops=1)

4.          

Initplan (for Limit)

5. 0.006 0.006 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((brief)::text = 'AUTHORS'::text)
  • Rows Removed by Filter: 7
8. 9.551 1,129.588 ↑ 1.4 50 1

WindowAgg (cost=152,489.89..180,887.71 rows=70 width=1,711) (actual time=1,111.475..1,129.588 rows=50 loops=1)

9. 3.835 1,101.687 ↓ 93.4 6,536 1

Nested Loop Left Join (cost=152,489.89..152,530.96 rows=70 width=1,443) (actual time=1,071.044..1,101.687 rows=6,536 loops=1)

  • Join Filter: (tse.set_id = tsi.set_id)
  • Rows Removed by Join Filter: 52284
10. 1.734 1,091.316 ↓ 93.4 6,536 1

Merge Left Join (cost=152,489.89..152,521.65 rows=70 width=1,383) (actual time=1,071.032..1,091.316 rows=6,536 loops=1)

  • Merge Cond: (i.item_id = tsi.item_id)
11. 1.844 1,089.546 ↓ 93.4 6,536 1

Nested Loop Left Join (cost=152,487.42..152,518.80 rows=70 width=1,379) (actual time=1,071.002..1,089.546 rows=6,536 loops=1)

12. 2.137 1,074.630 ↓ 93.4 6,536 1

Merge Left Join (cost=152,487.00..152,487.38 rows=70 width=1,359) (actual time=1,070.997..1,074.630 rows=6,536 loops=1)

  • Merge Cond: (i.item_id = p.item_id)
13. 12.101 1,072.476 ↓ 93.4 6,536 1

Sort (cost=152,477.75..152,477.93 rows=70 width=1,327) (actual time=1,070.976..1,072.476 rows=6,536 loops=1)

  • Sort Key: i.item_id DESC
  • Sort Method: external merge Disk: 1968kB
14. 2.325 1,060.375 ↓ 93.4 6,536 1

Hash Join (cost=152,427.62..152,475.61 rows=70 width=1,327) (actual time=1,028.010..1,060.375 rows=6,536 loops=1)

  • Hash Cond: (i.cat_id = c.cat_id)
15. 1.676 1,058.035 ↓ 83.1 6,649 1

Nested Loop (cost=152,426.43..152,474.10 rows=80 width=291) (actual time=1,027.985..1,058.035 rows=6,649 loops=1)

16. 5.788 1,029.763 ↓ 83.1 6,649 1

HashAggregate (cost=152,426.00..152,426.80 rows=80 width=8) (actual time=1,027.952..1,029.763 rows=6,649 loops=1)

  • Group Key: ip.item_id
17. 97.630 1,023.975 ↓ 84.2 6,735 1

Hash Join (cost=6,242.02..152,425.80 rows=80 width=8) (actual time=152.268..1,023.975 rows=6,735 loops=1)

  • Hash Cond: (ip.object_id = ti3.item_id)
18. 917.798 917.798 ↓ 1.8 742,310 1

Seq Scan on t_item_prop ip (cost=0.00..145,123.11 rows=404,067 width=16) (actual time=143.666..917.798 rows=742,310 loops=1)

  • Filter: (prop_id = $13)
  • Rows Removed by Filter: 6530899
19. 0.220 8.547 ↑ 1.2 1,107 1

Hash (cost=6,225.28..6,225.28 rows=1,339 width=4) (actual time=8.547..8.547 rows=1,107 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 55kB
20. 5.483 8.327 ↑ 1.2 1,107 1

Bitmap Heap Scan on t_item ti3 (cost=70.35..6,225.28 rows=1,339 width=4) (actual time=3.134..8.327 rows=1,107 loops=1)

  • Recheck Cond: ((name)::text ~* '^иванов'::text)
  • Rows Removed by Index Recheck: 2090
  • Filter: (cat_id = $14)
  • Rows Removed by Filter: 54
  • Heap Blocks: exact=1625
21. 2.844 2.844 ↑ 2.1 3,251 1

Bitmap Index Scan on t_item_name_trgm_idx (cost=0.00..70.01 rows=6,695 width=0) (actual time=2.843..2.844 rows=3,251 loops=1)

  • Index Cond: ((name)::text ~* '^иванов'::text)
22. 26.596 26.596 ↑ 1.0 1 6,649

Index Scan using t_item_pkey on t_item i (cost=0.42..0.59 rows=1 width=291) (actual time=0.004..0.004 rows=1 loops=6,649)

  • Index Cond: (item_id = ip.item_id)
23. 0.004 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
24. 0.011 0.011 ↑ 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.011 rows=7 loops=1)

  • Filter: ((brief)::text <> 'AUTHORS'::text)
  • Rows Removed by Filter: 1
25. 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
26. 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)
27. 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.004..0.005 rows=0 loops=1)

  • Index Cond: (pl_id = 1)
28. 13.072 13.072 ↑ 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.002..0.002 rows=1 loops=6,536)

  • Index Cond: (i.manufacturer_id = manufacturer_id)
29. 0.021 0.036 ↑ 1.0 40 1

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

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

31. 6.531 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)

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

33.          

SubPlan (for WindowAgg)

34. 0.050 0.200 ↑ 1.0 1 50

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

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

36. 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
37. 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)
38. 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)

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

40. 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
41. 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)
42. 4.950 10.950 ↑ 1.0 1 50

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

43. 0.500 5.500 ↑ 5.4 18 50

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

  • Hash Cond: (pt.prop_type_id = p_1.type)
44. 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)

45. 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
46. 0.868 4.650 ↑ 1.3 18 50

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

47. 0.336 2.000 ↑ 1.3 18 50

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

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

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

  • Hash Cond: (ip_1.prop_id = cp.prop_id)
49. 0.550 0.550 ↑ 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.006..0.011 rows=16 loops=50)

  • Index Cond: (item_id = i.item_id)
50. 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: 1024 Batches: 1 Memory Usage: 9kB
51. 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
52. 0.006 0.014 ↑ 1.0 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
53. 0.008 0.008 ↑ 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.008 rows=22 loops=1)

54. 1.782 1.782 ↑ 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.002..0.002 rows=1 loops=891)

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

SubPlan (for Aggregate)

56. 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.004..0.005 rows=1 loops=100)

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

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

58. 0.050 5.850 ↓ 0.0 0 50

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

59. 0.050 5.800 ↓ 0.0 0 50

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

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
60. 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
61. 5.650 5.650 ↓ 0.0 0 50

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

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

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

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

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

66. 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
67. 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)
68. 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
69. 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)

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

71.          

Initplan (for Aggregate)

72. 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)
73. 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)
74. 0.091 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)
75. 0.009 0.009 ↑ 87.0 1 1

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

76. 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: 1024 Batches: 1 Memory Usage: 8kB
77. 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.066 ms
Execution time : 1,132.273 ms