explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pLa5T

Settings
# exclusive inclusive rows x rows loops node
1. 0.189 56.111 ↑ 1.0 1 1

Aggregate (cost=13,341.35..13,341.36 rows=1 width=76) (actual time=56.111..56.111 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 0.013 0.013 ↑ 1.0 1 1

Index Scan using t_cat_pkey on t_cat (cost=0.28..8.29 rows=1 width=45) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (cat_id = 1665)
4. 0.042 55.909 ↑ 1.2 35 1

Subquery Scan on t (cost=13,332.30..13,332.84 rows=43 width=92) (actual time=55.876..55.909 rows=35 loops=1)

5. 0.166 55.867 ↑ 1.2 35 1

Sort (cost=13,332.30..13,332.41 rows=43 width=652) (actual time=55.864..55.867 rows=35 loops=1)

  • Sort Key: c1.order_weight DESC
  • Sort Method: quicksort Memory: 97kB
6. 0.265 55.701 ↑ 1.2 35 1

Bitmap Heap Scan on t_cat c1 (cost=4.63..13,330.27 rows=43 width=652) (actual time=2.153..55.701 rows=35 loops=1)

  • Recheck Cond: (parent_id = 1665)
  • Filter: ((hide IS NOT TRUE) AND (cat_id <> ALL ('{382,385,389,390,397,410,411,415,422,423}'::integer[])))
  • Rows Removed by Filter: 10
  • Heap Blocks: exact=2
7. 0.031 0.031 ↑ 1.0 45 1

Bitmap Index Scan on t_cat_parent_id_idx (cost=0.00..4.62 rows=45 width=0) (actual time=0.031..0.031 rows=45 loops=1)

  • Index Cond: (parent_id = 1665)
8.          

SubPlan (forBitmap Heap Scan)

9. 0.035 7.595 ↑ 1.0 1 35

Limit (cost=0.28..50.70 rows=1 width=17) (actual time=0.216..0.217 rows=1 loops=35)

10. 0.035 7.560 ↑ 1.0 1 35

Result (cost=0.28..50.70 rows=1 width=17) (actual time=0.216..0.216 rows=1 loops=35)

  • One-Time Filter: (c1.hide IS NOT TRUE)
11. 0.630 7.525 ↑ 1.0 1 35

Nested Loop Left Join (cost=0.28..50.70 rows=1 width=17) (actual time=0.215..0.215 rows=1 loops=35)

12. 6.790 6.790 ↑ 1.0 1 35

Seq Scan on t_image_link til (cost=0.00..42.39 rows=1 width=8) (actual time=0.194..0.194 rows=1 loops=35)

  • Filter: ((c1.cat_id = object_id) AND (object_type_id = 9))
  • Rows Removed by Filter: 1875
13. 0.105 0.105 ↑ 1.0 1 35

Index Scan using t_image_pkey on t_image ti (cost=0.28..8.30 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=35)

  • Index Cond: (til.image_id = image_id)
14. 0.035 2.520 ↑ 1.0 1 35

Aggregate (cost=72.98..72.99 rows=1 width=32) (actual time=0.072..0.072 rows=1 loops=35)

15. 0.000 2.485 ↓ 0.0 0 35

Subquery Scan on i (cost=72.95..72.97 rows=1 width=102) (actual time=0.071..0.071 rows=0 loops=35)

16. 0.070 2.485 ↓ 0.0 0 35

Unique (cost=72.95..72.96 rows=1 width=78) (actual time=0.071..0.071 rows=0 loops=35)

17. 0.175 2.415 ↓ 0.0 0 35

Sort (cost=72.95..72.96 rows=1 width=78) (actual time=0.069..0.069 rows=0 loops=35)

  • Sort Key: pv.brief, pv.value, ti_1.image_path
  • Sort Method: quicksort Memory: 25kB
18. 0.035 2.240 ↓ 0.0 0 35

Nested Loop Left Join (cost=0.84..72.84 rows=1 width=78) (actual time=0.064..0.064 rows=0 loops=35)

19. 0.000 2.205 ↓ 0.0 0 35

Nested Loop Left Join (cost=0.56..66.25 rows=1 width=69) (actual time=0.063..0.063 rows=0 loops=35)

  • Join Filter: (pv.prop_value_id = til_1.object_id)
20. 0.315 2.205 ↓ 0.0 0 35

Nested Loop (cost=0.56..28.50 rows=1 width=65) (actual time=0.063..0.063 rows=0 loops=35)

21. 0.000 1.890 ↓ 0.0 0 35

Nested Loop (cost=0.28..20.20 rows=1 width=73) (actual time=0.054..0.054 rows=0 loops=35)

  • Join Filter: (op.prop_value_id = pv.prop_value_id)
22. 1.645 1.645 ↑ 1.0 7 35

Seq Scan on t_prop_value pv (cost=0.00..11.05 rows=7 width=65) (actual time=0.013..0.047 rows=7 loops=35)

  • Filter: (prop_id = 28)
  • Rows Removed by Filter: 397
23. 0.105 0.245 ↓ 0.0 0 245

Materialize (cost=0.28..8.94 rows=2 width=12) (actual time=0.001..0.001 rows=0 loops=245)

24. 0.140 0.140 ↓ 0.0 0 35

Index Scan using t_object_propval_object_id_idx on t_object_propval op (cost=0.28..8.93 rows=2 width=12) (actual time=0.004..0.004 rows=0 loops=35)

  • Index Cond: (object_id = c1.cat_id)
25. 0.000 0.000 ↓ 0.0 0

Index Only Scan using t_cat_pkey on t_cat c (cost=0.28..8.29 rows=1 width=4) (never executed)

  • Index Cond: (cat_id = c1.cat_id)
  • Heap Fetches: 0
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_image_link til_1 (cost=0.00..37.66 rows=7 width=16) (never executed)

  • Filter: (object_type_id = 11)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using t_image_pkey on t_image ti_1 (cost=0.28..6.58 rows=1 width=25) (never executed)

  • Index Cond: (til_1.image_id = image_id)
28. 1.120 45.290 ↑ 1.0 1 35

Aggregate (cost=185.61..185.62 rows=1 width=32) (actual time=1.293..1.294 rows=1 loops=35)

29. 0.385 44.170 ↓ 7.0 7 35

Subquery Scan on t_1 (cost=185.57..185.61 rows=1 width=205) (actual time=1.249..1.262 rows=7 loops=35)

30. 0.175 43.785 ↓ 7.0 7 35

Unique (cost=185.57..185.60 rows=1 width=1,213) (actual time=1.247..1.251 rows=7 loops=35)

31. 1.190 43.610 ↓ 7.0 7 35

Sort (cost=185.57..185.57 rows=1 width=1,213) (actual time=1.246..1.246 rows=7 loops=35)

  • Sort Key: tpv.value, tc.cat_id, tc.name, tc.brief, tc.description, ti_2.image_path, tc.hide, tpv1.value, tpv1.brief, ((SubPlan 4)), ((SubPlan 5))
  • Sort Method: quicksort Memory: 32kB
32. 0.491 42.420 ↓ 7.0 7 35

Nested Loop (cost=100.66..185.04 rows=1 width=1,213) (actual time=1.052..1.212 rows=7 loops=35)

33. 0.269 38.710 ↓ 16.0 16 35

Nested Loop (cost=100.38..144.64 rows=1 width=150) (actual time=1.031..1.106 rows=16 loops=35)

  • Join Filter: (tc.cat_id = top.object_id)
34. 0.471 37.730 ↓ 7.0 7 35

Nested Loop Left Join (cost=100.10..144.25 rows=1 width=154) (actual time=1.027..1.078 rows=7 loops=35)

35. 5.985 36.785 ↓ 7.0 7 35

Hash Right Join (cost=99.82..143.89 rows=1 width=145) (actual time=1.013..1.051 rows=7 loops=35)

  • Hash Cond: (til_2.object_id = tc.cat_id)
36. 7.840 7.840 ↑ 1.0 1,706 35

Seq Scan on t_image_link til_2 (cost=0.00..37.66 rows=1,706 width=16) (actual time=0.003..0.224 rows=1,706 loops=35)

  • Filter: (object_type_id = 9)
  • Rows Removed by Filter: 187
37. 0.210 22.960 ↓ 7.0 7 35

Hash (cost=99.81..99.81 rows=1 width=137) (actual time=0.656..0.656 rows=7 loops=35)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
38. 0.314 22.750 ↓ 7.0 7 35

Nested Loop (cost=17.09..99.81 rows=1 width=137) (actual time=0.595..0.650 rows=7 loops=35)

39. 11.480 21.350 ↑ 1.4 16 35

Hash Join (cost=16.82..92.78 rows=23 width=80) (actual time=0.534..0.610 rows=16 loops=35)

  • Hash Cond: (top1.object_id = tc.cat_id)
40. 9.485 9.485 ↑ 1.0 3,797 35

Seq Scan on t_object_propval top1 (cost=0.00..65.97 rows=3,797 width=12) (actual time=0.003..0.271 rows=3,797 loops=35)

41. 0.105 0.385 ↑ 1.7 7 35

Hash (cost=16.67..16.67 rows=12 width=68) (actual time=0.011..0.011 rows=7 loops=35)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
42. 0.280 0.280 ↑ 1.7 7 35

Index Scan using t_cat_parent_id_idx on t_cat tc (cost=0.28..16.67 rows=12 width=68) (actual time=0.005..0.008 rows=7 loops=35)

  • Index Cond: (parent_id = c1.cat_id)
  • Filter: (hide IS NOT TRUE)
43. 1.086 1.086 ↓ 0.0 0 543

Index Scan using t_prop_value_pkey on t_prop_value tpv1 (cost=0.27..0.31 rows=1 width=65) (actual time=0.002..0.002 rows=0 loops=543)

  • Index Cond: (prop_value_id = top1.prop_value_id)
  • Filter: (((brief)::text <> ALL ('{knitwear,non-iron}'::text[])) AND (prop_id = 27))
  • Rows Removed by Filter: 1
44. 0.474 0.474 ↑ 1.0 1 237

Index Scan using t_image_pkey on t_image ti_2 (cost=0.28..0.36 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=237)

  • Index Cond: (til_2.image_id = image_id)
45. 0.711 0.711 ↑ 1.0 2 237

Index Scan using t_object_propval_object_id_idx on t_object_propval top (cost=0.28..0.36 rows=2 width=12) (actual time=0.002..0.003 rows=2 loops=237)

  • Index Cond: (object_id = top1.object_id)
46. 1.086 1.086 ↓ 0.0 0 543

Index Scan using t_prop_value_pkey on t_prop_value tpv (cost=0.27..0.30 rows=1 width=39) (actual time=0.002..0.002 rows=0 loops=543)

  • Index Cond: (prop_value_id = top.prop_value_id)
  • Filter: (prop_id = 22)
  • Rows Removed by Filter: 1
47.          

SubPlan (forNested Loop)

48. 0.000 1.185 ↓ 0.0 0 237

Limit (cost=0.28..20.05 rows=1 width=35) (actual time=0.005..0.005 rows=0 loops=237)

49. 0.474 1.185 ↓ 0.0 0 237

Nested Loop (cost=0.28..20.05 rows=1 width=35) (actual time=0.005..0.005 rows=0 loops=237)

  • Join Filter: (top2.prop_value_id = tpv2.prop_value_id)
  • Rows Removed by Join Filter: 4
50. 0.711 0.711 ↑ 1.0 2 237

Index Scan using t_object_propval_object_id_idx on t_object_propval top2 (cost=0.28..8.93 rows=2 width=4) (actual time=0.002..0.003 rows=2 loops=237)

  • Index Cond: (object_id = tc.cat_id)
51. 0.000 0.000 ↑ 1.0 2 543

Materialize (cost=0.00..11.06 rows=2 width=39) (actual time=0.000..0.000 rows=2 loops=543)

52. 0.054 0.054 ↑ 1.0 2 1

Seq Scan on t_prop_value tpv2 (cost=0.00..11.05 rows=2 width=39) (actual time=0.032..0.054 rows=2 loops=1)

  • Filter: (prop_id = 39)
  • Rows Removed by Filter: 402
53. 0.000 0.948 ↓ 0.0 0 237

Limit (cost=0.28..20.05 rows=1 width=35) (actual time=0.004..0.004 rows=0 loops=237)

54. 0.474 0.948 ↓ 0.0 0 237

Nested Loop (cost=0.28..20.05 rows=1 width=35) (actual time=0.004..0.004 rows=0 loops=237)

  • Join Filter: (top2_1.prop_value_id = tpv2_1.prop_value_id)
  • Rows Removed by Join Filter: 4
55. 0.474 0.474 ↑ 1.0 2 237

Index Scan using t_object_propval_object_id_idx on t_object_propval top2_1 (cost=0.28..8.93 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=237)

  • Index Cond: (object_id = tc.cat_id)
56. 0.000 0.000 ↑ 1.0 2 543

Materialize (cost=0.00..11.06 rows=2 width=39) (actual time=0.000..0.000 rows=2 loops=543)

57. 0.065 0.065 ↑ 1.0 2 1

Seq Scan on t_prop_value tpv2_1 (cost=0.00..11.05 rows=2 width=39) (actual time=0.045..0.065 rows=2 loops=1)

  • Filter: (prop_id = 40)
  • Rows Removed by Filter: 402