explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 63Qs

Settings
# exclusive inclusive rows x rows loops node
1. 0.175 1,625.843 ↑ 1.0 1 1

Aggregate (cost=1,898,301.39..1,898,301.40 rows=1 width=32) (actual time=1,625.843..1,625.843 rows=1 loops=1)

  • Functions: 130
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 15.986 ms, Inlining 26.445 ms, Optimization 996.026 ms, Emission 569.471 ms, Total 1607.928 ms
2. 0.033 1,625.668 ↑ 12.5 16 1

Subquery Scan on tt (cost=1.13..1,898,300.89 rows=200 width=531) (actual time=1,622.566..1,625.668 rows=16 loops=1)

3. 1,569.398 1,625.635 ↑ 12.5 16 1

Limit (cost=1.13..1,898,298.89 rows=200 width=680) (actual time=1,622.557..1,625.635 rows=16 loops=1)

4.          

Initplan (for Limit)

5. 0.009 0.009 ↑ 1.0 1 1

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

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
6. 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)

7. 0.000 0.000 ↓ 0.0 0

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

8. 0.979 56.227 ↑ 46.1 16 1

WindowAgg (cost=0.00..7,004,718.71 rows=738 width=680) (actual time=53.152..56.227 rows=16 loops=1)

9. 16.655 16.655 ↑ 46.1 16 1

Seq Scan on t_item i (cost=0.00..6,620.94 rows=738 width=517) (actual time=0.426..16.655 rows=16 loops=1)

  • Filter: ((parent_id IS NULL) AND (cat_id = 1656))
  • Rows Removed by Filter: 62219
10.          

SubPlan (for WindowAgg)

11. 0.352 0.352 ↓ 0.0 0 16

Seq Scan on t_manufacturer m (cost=0.00..6.36 rows=1 width=10) (actual time=0.022..0.022 rows=0 loops=16)

  • Filter: (manufacturer_id = i.manufacturer_id)
  • Rows Removed by Filter: 296
12. 0.224 0.880 ↑ 1.0 1 16

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

13. 0.040 0.656 ↑ 96.3 3 16

Hash Join (cost=38.45..224.32 rows=289 width=193) (actual time=0.037..0.041 rows=3 loops=16)

  • Hash Cond: (p.type = pt.prop_type_id)
14. 0.058 0.608 ↑ 22.7 3 16

Hash Join (cost=9.32..184.91 rows=68 width=161) (actual time=0.035..0.038 rows=3 loops=16)

  • Hash Cond: (cp.prop_id = p.prop_id)
15. 0.112 0.496 ↑ 22.7 3 16

Hash Right Join (cost=4.26..179.67 rows=68 width=140) (actual time=0.029..0.031 rows=3 loops=16)

  • Hash Cond: (ip.prop_id = cp.prop_id)
16. 0.016 0.096 ↓ 0.0 0 16

Nested Loop Left Join (cost=0.71..175.93 rows=18 width=140) (actual time=0.006..0.006 rows=0 loops=16)

17. 0.080 0.080 ↓ 0.0 0 16

Index Scan using item_prop_idx on t_item_prop ip (cost=0.42..30.44 rows=18 width=108) (actual time=0.005..0.005 rows=0 loops=16)

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

Index Scan using t_prop_value_pkey on t_prop_value pv (cost=0.29..8.08 rows=1 width=36) (never executed)

  • Index Cond: (prop_value_id = ip.prop_value_id)
19. 0.064 0.288 ↑ 22.7 3 16

Hash (cost=2.70..2.70 rows=68 width=4) (actual time=0.018..0.018 rows=3 loops=16)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.224 0.224 ↑ 22.7 3 16

Seq Scan on t_cat_prop cp (cost=0.00..2.70 rows=68 width=4) (actual time=0.005..0.014 rows=3 loops=16)

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 133
21. 0.027 0.054 ↓ 1.2 163 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
22. 0.027 0.027 ↓ 1.2 163 1

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

23. 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
24. 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)

25.          

SubPlan (for Aggregate)

26. 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)
27. 0.080 1.152 ↑ 1.0 1 16

Aggregate (cost=49.58..49.59 rows=1 width=32) (actual time=0.071..0.072 rows=1 loops=16)

28. 0.032 1.072 ↑ 2.0 1 16

Subquery Scan on t (cost=0.84..49.58 rows=2 width=24) (actual time=0.031..0.067 rows=1 loops=16)

29. 0.016 1.040 ↑ 2.0 1 16

Append (cost=0.84..49.56 rows=2 width=96) (actual time=0.029..0.065 rows=1 loops=16)

30. 0.048 0.208 ↓ 0.0 0 16

Nested Loop (cost=0.84..27.96 rows=1 width=86) (actual time=0.013..0.013 rows=0 loops=16)

31. 0.016 0.160 ↓ 0.0 0 16

Nested Loop (cost=0.42..19.52 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=16)

  • Join Filter: (fl.object_type_id = ot_1.object_type_id)
32. 0.080 0.080 ↑ 1.0 1 16

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

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
33. 0.064 0.064 ↓ 0.0 0 16

Index Scan using object_id_idx on t_file_link fl (cost=0.42..18.33 rows=6 width=12) (actual time=0.004..0.004 rows=0 loops=16)

  • Index Cond: (object_id = i.item_id)
34. 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=58) (never executed)

  • Index Cond: (file_id = fl.file_id)
  • Filter: (NOT deleted)
35. 0.032 0.816 ↑ 1.0 1 16

Subquery Scan on *SELECT* 2 (cost=0.00..21.58 rows=1 width=96) (actual time=0.015..0.051 rows=1 loops=16)

36. 0.784 0.784 ↑ 1.0 1 16

Seq Scan on t_item_image ii (cost=0.00..21.57 rows=1 width=83) (actual time=0.013..0.049 rows=1 loops=16)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 764
37. 0.080 0.832 ↑ 1.0 1 16

Aggregate (cost=21.57..21.58 rows=1 width=32) (actual time=0.052..0.052 rows=1 loops=16)

38. 0.752 0.752 ↑ 1.0 1 16

Seq Scan on t_item_image im (cost=0.00..21.56 rows=1 width=31) (actual time=0.012..0.047 rows=1 loops=16)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 764
39. 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))
40.          

Initplan (for Seq Scan)

41. 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)
42. 35.348 35.377 ↑ 10.9 819 1

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

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

Initplan (for Seq Scan)

44. 0.029 0.029 ↑ 1.0 1 1

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

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