explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z1j5

Settings
# exclusive inclusive rows x rows loops node
1. 0.748 192.858 ↑ 1.0 1 1

Aggregate (cost=201,645.63..201,645.64 rows=1 width=32) (actual time=192.857..192.858 rows=1 loops=1)

  • Functions: 142
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 21.675 ms, Inlining 0.000 ms, Optimization 9.310 ms, Emission 128.950 ms, Total 159.935 ms
2. 0.146 192.110 ↑ 1.0 21 1

Subquery Scan on tt (cost=2,512.50..201,645.57 rows=21 width=531) (actual time=168.963..192.110 rows=21 loops=1)

3. 138.400 191.964 ↑ 1.0 21 1

Limit (cost=2,512.50..201,645.36 rows=21 width=680) (actual time=168.943..191.964 rows=21 loops=1)

4.          

Initplan (for Limit)

5. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on t_object_type ot (cost=0.00..1.11 rows=1 width=4) (actual time=0.018..0.019 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.002 0.002 ↑ 1.0 1 1

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

8. 1.274 53.543 ↑ 4.8 21 1

Result (cost=2,511.36..950,763.10 rows=100 width=680) (actual time=30.528..53.543 rows=21 loops=1)

9. 0.064 10.273 ↑ 4.8 21 1

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

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

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

11. 0.094 10.020 ↓ 1.4 140 1

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

12. 0.082 9.646 ↓ 1.4 140 1

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

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

Seq Scan on t_item_link il (cost=0.00..1,747.14 rows=100 width=8) (actual time=9.165..9.564 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.714 0.714 ↑ 1.0 1 21

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

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

Aggregate (cost=2,627.36..2,627.37 rows=1 width=32) (actual time=0.875..0.875 rows=1 loops=21)

18. 1.189 7.455 ↑ 2.2 133 21

Hash Join (cost=38.45..224.32 rows=289 width=193) (actual time=0.084..0.355 rows=133 loops=21)

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

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

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

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

  • Hash Cond: (ip.prop_id = cp.prop_id)
21. 0.739 2.814 ↓ 1.8 32 21

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

22. 0.735 0.735 ↓ 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.007..0.035 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.609 1.239 ↓ 2.0 133 21

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

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

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

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

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

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

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

28. 0.002 0.008 ↑ 141.7 6 1

Hash (cost=18.50..18.50 rows=850 width=36) (actual time=0.008..0.008 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.006 0.006 ↑ 141.7 6 1

Seq Scan on t_prop_type pt (cost=0.00..18.50 rows=850 width=36) (actual time=0.005..0.006 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.126 2.772 ↑ 1.0 1 21

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

33. 0.063 2.646 ↑ 2.0 1 21

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

34. 0.042 2.583 ↑ 2.0 1 21

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

35. 0.147 0.588 ↑ 1.0 1 21

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

36. 0.063 0.315 ↑ 1.0 1 21

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

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

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

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
38. 0.147 0.147 ↑ 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.007..0.007 rows=1 loops=21)

  • Index Cond: (object_id = i.item_id)
39. 0.126 0.126 ↑ 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.006..0.006 rows=1 loops=21)

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

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

41. 1.869 1.869 ↓ 0.0 0 21

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

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

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

43. 1.680 1.680 ↓ 0.0 0 21

Seq Scan on t_item_image im (cost=0.00..21.56 rows=1 width=31) (actual time=0.080..0.080 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. 18.371 18.392 ↑ 10.9 819 1

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

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

Initplan (for Seq Scan)

49. 0.021 0.021 ↑ 1.0 1 1

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

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