explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XPn8

Settings
# exclusive inclusive rows x rows loops node
1. 1.676 224.187 ↑ 1.0 1 1

Aggregate (cost=162,271.57..162,271.58 rows=1 width=32) (actual time=224.187..224.187 rows=1 loops=1)

  • Functions: 142
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 23.542 ms, Inlining 0.000 ms, Optimization 11.434 ms, Emission 144.142 ms, Total 179.118 ms
2. 0.201 222.511 ↑ 1.0 21 1

Subquery Scan on tt (cost=2,512.50..162,271.52 rows=21 width=531) (actual time=203.513..222.511 rows=21 loops=1)

3. 151.679 222.310 ↑ 1.0 21 1

Limit (cost=2,512.50..162,271.31 rows=21 width=680) (actual time=203.434..222.310 rows=21 loops=1)

4.          

Initplan (for Limit)

5. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on t_object_type ot (cost=0.00..1.11 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
6. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=1) (never executed)

7. 0.005 0.005 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.005..0.005 rows=1 loops=1)

8. 2.101 70.606 ↑ 4.8 21 1

Result (cost=2,511.36..763,267.60 rows=100 width=680) (actual time=51.736..70.606 rows=21 loops=1)

9. 0.126 12.628 ↑ 4.8 21 1

Sort (cost=2,511.36..2,511.61 rows=100 width=555) (actual time=12.588..12.628 rows=21 loops=1)

  • Sort Key: i.item_id
  • Sort Method: top-N heapsort Memory: 43kB
10. 0.248 12.502 ↓ 1.4 140 1

WindowAgg (cost=1,747.68..2,508.67 rows=100 width=555) (actual time=12.389..12.502 rows=140 loops=1)

11. 0.221 12.254 ↓ 1.4 140 1

Nested Loop (cost=1,747.68..2,507.17 rows=100 width=517) (actual time=11.739..12.254 rows=140 loops=1)

12. 0.091 11.753 ↓ 1.4 140 1

HashAggregate (cost=1,747.39..1,748.39 rows=100 width=8) (actual time=11.713..11.753 rows=140 loops=1)

  • Group Key: il.item_id
13. 11.662 11.662 ↓ 1.4 140 1

Seq Scan on t_item_link il (cost=0.00..1,747.14 rows=100 width=8) (actual time=11.304..11.662 rows=140 loops=1)

  • Filter: ((object_id = 79867) AND (link_type_id = 10))
  • Rows Removed by Filter: 78003
14. 0.280 0.280 ↑ 1.0 1 140

Index Scan using t_item_pkey on t_item i (cost=0.29..7.59 rows=1 width=517) (actual time=0.002..0.002 rows=1 loops=140)

  • Index Cond: (item_id = il.item_id)
15.          

SubPlan (for Result)

16. 0.609 0.609 ↑ 1.0 1 21

Seq Scan on t_manufacturer m (cost=0.00..6.36 rows=1 width=10) (actual time=0.028..0.029 rows=1 loops=21)

  • Filter: (manufacturer_id = i.manufacturer_id)
  • Rows Removed by Filter: 295
17. 8.442 15.015 ↑ 1.0 1 21

Aggregate (cost=752.40..752.41 rows=1 width=32) (actual time=0.714..0.715 rows=1 loops=21)

18. 0.941 6.573 ↓ 2.0 133 21

Hash Join (cost=10.46..186.98 rows=68 width=193) (actual time=0.094..0.313 rows=133 loops=21)

  • Hash Cond: (p.type = pt.prop_type_id)
19. 1.088 5.607 ↓ 2.0 133 21

Hash Join (cost=9.32..184.91 rows=68 width=161) (actual time=0.091..0.267 rows=133 loops=21)

  • Hash Cond: (cp.prop_id = p.prop_id)
20. 0.735 4.452 ↓ 2.0 133 21

Hash Right Join (cost=4.26..179.67 rows=68 width=140) (actual time=0.085..0.212 rows=133 loops=21)

  • Hash Cond: (ip.prop_id = cp.prop_id)
21. 0.298 2.541 ↓ 1.8 32 21

Nested Loop Left Join (cost=0.71..175.93 rows=18 width=140) (actual time=0.021..0.121 rows=32 loops=21)

22. 0.903 0.903 ↓ 1.8 32 21

Index Scan using item_prop_idx on t_item_prop ip (cost=0.42..30.44 rows=18 width=108) (actual time=0.012..0.043 rows=32 loops=21)

  • Index Cond: (item_id = i.item_id)
23. 1.340 1.340 ↑ 1.0 1 670

Index Scan using t_prop_value_pkey on t_prop_value pv (cost=0.29..8.08 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=670)

  • Index Cond: (prop_value_id = ip.prop_value_id)
24. 0.525 1.176 ↓ 2.0 133 21

Hash (cost=2.70..2.70 rows=68 width=4) (actual time=0.056..0.056 rows=133 loops=21)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
25. 0.651 0.651 ↓ 2.0 133 21

Seq Scan on t_cat_prop cp (cost=0.00..2.70 rows=68 width=4) (actual time=0.008..0.031 rows=133 loops=21)

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 3
26. 0.027 0.067 ↓ 1.2 163 1

Hash (cost=3.36..3.36 rows=136 width=25) (actual time=0.066..0.067 rows=163 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
27. 0.040 0.040 ↓ 1.2 163 1

Seq Scan on t_prop p (cost=0.00..3.36 rows=136 width=25) (actual time=0.018..0.040 rows=163 loops=1)

28. 0.015 0.025 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=36) (actual time=0.025..0.025 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on t_prop_type pt (cost=0.00..1.06 rows=6 width=36) (actual time=0.008..0.010 rows=6 loops=1)

30.          

SubPlan (for Aggregate)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using t_item_pkey on t_item ti1 (cost=0.29..8.31 rows=1 width=74) (never executed)

  • Index Cond: (item_id = ip.object_id)
32. 0.147 2.814 ↑ 1.0 1 21

Aggregate (cost=49.58..49.59 rows=1 width=32) (actual time=0.134..0.134 rows=1 loops=21)

33. 0.063 2.667 ↑ 2.0 1 21

Subquery Scan on t (cost=0.84..49.58 rows=2 width=24) (actual time=0.037..0.127 rows=1 loops=21)

34. 0.042 2.604 ↑ 2.0 1 21

Append (cost=0.84..49.56 rows=2 width=96) (actual time=0.035..0.124 rows=1 loops=21)

35. 0.210 0.735 ↑ 1.0 1 21

Nested Loop (cost=0.84..27.96 rows=1 width=86) (actual time=0.033..0.035 rows=1 loops=21)

36. 0.042 0.378 ↑ 1.0 1 21

Nested Loop (cost=0.42..19.52 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=21)

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
37. 0.126 0.126 ↑ 1.0 1 21

Seq Scan on t_object_type ot_1 (cost=0.00..1.11 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=21)

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
38. 0.210 0.210 ↑ 6.0 1 21

Index Scan using object_id_idx on t_file_link fl (cost=0.42..18.33 rows=6 width=12) (actual time=0.009..0.010 rows=1 loops=21)

  • Index Cond: (object_id = i.item_id)
39. 0.147 0.147 ↑ 1.0 1 21

Index Scan using t_file_pkey on t_file f (cost=0.42..8.44 rows=1 width=58) (actual time=0.007..0.007 rows=1 loops=21)

  • Index Cond: (file_id = fl.file_id)
  • Filter: (NOT deleted)
40. 0.063 1.827 ↓ 0.0 0 21

Subquery Scan on *SELECT* 2 (cost=0.00..21.58 rows=1 width=96) (actual time=0.087..0.087 rows=0 loops=21)

41. 1.764 1.764 ↓ 0.0 0 21

Seq Scan on t_item_image ii (cost=0.00..21.57 rows=1 width=83) (actual time=0.084..0.084 rows=0 loops=21)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 765
42. 0.084 1.386 ↑ 1.0 1 21

Aggregate (cost=21.57..21.58 rows=1 width=32) (actual time=0.066..0.066 rows=1 loops=21)

43. 1.302 1.302 ↓ 0.0 0 21

Seq Scan on t_item_image im (cost=0.00..21.56 rows=1 width=31) (actual time=0.062..0.062 rows=0 loops=21)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 765
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_item i2 (cost=1.07..6,777.60 rows=1 width=0) (never executed)

  • Filter: ((cat_id = $9) AND (parent_id = i.item_id))
45.          

Initplan (for Seq Scan)

46. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_cat c2 (cost=0.00..1.07 rows=1 width=4) (never executed)

  • Filter: ((brief)::text = 'PRODUCT_CAT'::text)
47. 35.924 36.053 ↑ 10.9 819 1

Seq Scan on t_item i2_1 (cost=1.07..6,622.01 rows=8,890 width=8) (actual time=8.642..36.053 rows=819 loops=1)

  • Filter: (cat_id = $12)
  • Rows Removed by Filter: 61416
48.          

Initplan (for Seq Scan)

49. 0.129 0.129 ↑ 1.0 1 1

Seq Scan on t_cat c2_1 (cost=0.00..1.07 rows=1 width=4) (actual time=0.122..0.129 rows=1 loops=1)

  • Filter: ((brief)::text = 'PRODUCT_CAT'::text)
  • Rows Removed by Filter: 6
Execution time : 248.331 ms