explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GX7k

Settings
# exclusive inclusive rows x rows loops node
1. 0.393 60.650 ↑ 1.0 1 1

Aggregate (cost=225,215.88..225,215.89 rows=1 width=32) (actual time=60.650..60.650 rows=1 loops=1)

2. 0.099 60.257 ↑ 1.0 21 1

Subquery Scan on tt (cost=2,412.63..225,215.82 rows=21 width=572) (actual time=49.400..60.257 rows=21 loops=1)

3. 0.005 60.158 ↑ 1.0 21 1

Limit (cost=2,412.63..225,215.61 rows=21 width=721) (actual time=49.381..60.158 rows=21 loops=1)

4.          

Initplan (for Limit)

5. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on t_object_type ot (cost=0.00..1.11 rows=1 width=4) (actual time=0.004..0.005 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.001 0.001 ↑ 1.0 1 1

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

8. 1.016 60.147 ↑ 42.1 21 1

WindowAgg (cost=2,411.50..9,381,355.84 rows=884 width=721) (actual time=49.379..60.147 rows=21 loops=1)

9. 4.254 31.322 ↓ 1.5 1,339 1

Merge Semi Join (cost=2,411.50..15,188.58 rows=884 width=558) (actual time=19.156..31.322 rows=1,339 loops=1)

  • Merge Cond: (i.item_id = il.item_id)
10. 22.181 22.181 ↑ 1.0 53,638 1

Index Scan using t_item_pkey on t_item i (cost=0.29..13,438.73 rows=53,638 width=558) (actual time=0.009..22.181 rows=53,638 loops=1)

11. 0.343 4.887 ↓ 1.5 1,339 1

Sort (cost=1,602.50..1,604.71 rows=884 width=8) (actual time=4.740..4.887 rows=1,339 loops=1)

  • Sort Key: il.item_id
  • Sort Method: quicksort Memory: 111kB
12. 4.544 4.544 ↓ 1.5 1,339 1

Seq Scan on t_item_link il (cost=0.00..1,559.23 rows=884 width=8) (actual time=2.928..4.544 rows=1,339 loops=1)

  • Filter: ((object_id = 53,608) AND (link_type_id = 10))
  • Rows Removed by Filter: 68,410
13.          

SubPlan (for WindowAgg)

14. 0.357 0.357 ↑ 1.0 1 21

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

  • Filter: (manufacturer_id = i.manufacturer_id)
  • Rows Removed by Filter: 268
15. 6.384 10.458 ↑ 1.0 1 21

Aggregate (cost=2,660.02..2,660.03 rows=1 width=32) (actual time=0.498..0.498 rows=1 loops=21)

16. 0.755 4.074 ↑ 2.0 142 21

Hash Join (cost=44.58..256.99 rows=289 width=193) (actual time=0.056..0.194 rows=142 loops=21)

  • Hash Cond: (p.type = pt.prop_type_id)
17. 0.728 3.297 ↓ 2.1 142 21

Hash Join (cost=15.45..217.58 rows=68 width=161) (actual time=0.054..0.157 rows=142 loops=21)

  • Hash Cond: (cp.prop_id = p.prop_id)
18. 0.588 2.520 ↓ 2.1 142 21

Hash Right Join (cost=8.39..210.34 rows=68 width=140) (actual time=0.050..0.120 rows=142 loops=21)

  • Hash Cond: (ip.prop_id = cp.prop_id)
19. 0.255 1.302 ↑ 1.0 17 21

Nested Loop Left Join (cost=4.84..206.60 rows=17 width=140) (actual time=0.017..0.062 rows=17 loops=21)

20. 0.168 0.315 ↑ 1.0 17 21

Bitmap Heap Scan on t_item_prop ip (cost=4.56..69.42 rows=17 width=108) (actual time=0.010..0.015 rows=17 loops=21)

  • Recheck Cond: (item_id = i.item_id)
  • Heap Blocks: exact=45
21. 0.147 0.147 ↑ 1.0 17 21

Bitmap Index Scan on itmid_pid_pvid_idx (cost=0.00..4.55 rows=17 width=0) (actual time=0.007..0.007 rows=17 loops=21)

  • Index Cond: (item_id = i.item_id)
22. 0.732 0.732 ↑ 1.0 1 366

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

  • Index Cond: (prop_value_id = ip.prop_value_id)
23. 0.294 0.630 ↓ 2.0 133 21

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
24. 0.336 0.336 ↓ 2.0 133 21

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

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 3
25. 0.021 0.049 ↑ 1.0 136 1

Hash (cost=5.36..5.36 rows=136 width=25) (actual time=0.049..0.049 rows=136 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
26. 0.028 0.028 ↑ 1.0 136 1

Seq Scan on t_prop p (cost=0.00..5.36 rows=136 width=25) (actual time=0.003..0.028 rows=136 loops=1)

27. 0.006 0.022 ↑ 141.7 6 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.016 0.016 ↑ 141.7 6 1

Seq Scan on t_prop_type pt (cost=0.00..18.50 rows=850 width=36) (actual time=0.011..0.016 rows=6 loops=1)

29.          

SubPlan (for Aggregate)

30. 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=70) (never executed)

  • Index Cond: (item_id = ip.object_id)
31. 0.021 0.609 ↑ 1.0 1 21

Aggregate (cost=48.42..48.43 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=21)

32. 0.000 0.588 ↓ 0.0 0 21

Subquery Scan on t (cost=4.77..48.41 rows=2 width=24) (actual time=0.028..0.028 rows=0 loops=21)

33. 0.021 0.588 ↓ 0.0 0 21

Append (cost=4.77..48.39 rows=2 width=96) (actual time=0.028..0.028 rows=0 loops=21)

34. 0.021 0.147 ↓ 0.0 0 21

Nested Loop (cost=4.77..39.63 rows=1 width=84) (actual time=0.007..0.007 rows=0 loops=21)

35. 0.021 0.126 ↓ 0.0 0 21

Nested Loop (cost=4.35..31.19 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=21)

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
36. 0.042 0.042 ↑ 1.0 1 21

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

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
37. 0.021 0.063 ↓ 0.0 0 21

Bitmap Heap Scan on t_file_link fl (cost=4.35..29.99 rows=7 width=12) (actual time=0.003..0.003 rows=0 loops=21)

  • Recheck Cond: (object_id = i.item_id)
38. 0.042 0.042 ↓ 0.0 0 21

Bitmap Index Scan on object_id_idx (cost=0.00..4.35 rows=7 width=0) (actual time=0.002..0.002 rows=0 loops=21)

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

Index Scan using t_file_pkey on t_file f (cost=0.42..8.44 rows=1 width=56) (never executed)

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

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

41. 0.420 0.420 ↓ 0.0 0 21

Seq Scan on t_item_image ii (cost=0.00..8.73 rows=1 width=87) (actual time=0.020..0.020 rows=0 loops=21)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 298
42. 0.021 0.399 ↑ 1.0 1 21

Aggregate (cost=8.73..8.74 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=21)

43. 0.378 0.378 ↓ 0.0 0 21

Seq Scan on t_item_image im (cost=0.00..8.72 rows=1 width=36) (actual time=0.018..0.018 rows=0 loops=21)

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

Seq Scan on t_item i2 (cost=1.06..7,871.63 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.06 rows=1 width=4) (never executed)

  • Filter: ((brief)::text = 'PRODUCT_CAT'::text)
47. 15.982 15.986 ↑ 13.8 649 1

Seq Scan on t_item i2_1 (cost=1.06..7,737.54 rows=8,940 width=8) (actual time=0.908..15.986 rows=649 loops=1)

  • Filter: (cat_id = $12)
  • Rows Removed by Filter: 52,989
48.          

Initplan (for Seq Scan)

49. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on t_cat c2_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: ((brief)::text = 'PRODUCT_CAT'::text)
  • Rows Removed by Filter: 5
Planning time : 3.139 ms
Execution time : 61.429 ms