explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rvuTD

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 24,136.851 ↑ 1.0 1 1

Aggregate (cost=10,218,681.10..10,218,681.11 rows=1 width=32) (actual time=24,136.851..24,136.851 rows=1 loops=1)

2. 0.026 24,136.808 ↑ 305.0 2 1

Subquery Scan on tt (cost=10,218,671.95..10,218,679.57 rows=610 width=2,582) (actual time=24,136.806..24,136.808 rows=2 loops=1)

3. 0.003 24,136.782 ↑ 305.0 2 1

Limit (cost=10,218,671.95..10,218,673.47 rows=610 width=2,566) (actual time=24,136.780..24,136.782 rows=2 loops=1)

4. 0.016 24,136.779 ↑ 305.0 2 1

Sort (cost=10,218,671.95..10,218,673.47 rows=610 width=2,566) (actual time=24,136.779..24,136.779 rows=2 loops=1)

  • Sort Key: t.item_id DESC
  • Sort Method: quicksort Memory: 27kB
5. 0.011 24,136.763 ↑ 305.0 2 1

WindowAgg (cost=257.82..10,218,246.03 rows=610 width=2,566) (actual time=24,136.762..24,136.763 rows=2 loops=1)

6. 43.717 24,136.752 ↑ 305.0 2 1

Subquery Scan on t (cost=257.82..10,218,238.40 rows=610 width=2,558) (actual time=24,136.471..24,136.752 rows=2 loops=1)

  • Filter: (t.customer_id = 1602)
  • Rows Removed by Filter: 310120
7. 1,372.710 24,093.035 ↓ 1.0 310,122 1

WindowAgg (cost=257.82..10,214,361.89 rows=310,121 width=2,558) (actual time=4.318..24,093.035 rows=310,122 loops=1)

8.          

Initplan (forWindowAgg)

9. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: ((brief)::text = 'ITEM'::text)
  • Rows Removed by Filter: 8
10. 147.008 701.657 ↓ 1.0 310,122 1

Hash Left Join (cost=256.71..17,427.24 rows=310,121 width=1,842) (actual time=4.039..701.657 rows=310,122 loops=1)

  • Hash Cond: (d.doc_type_id = dt.doc_type_id)
11. 154.437 554.642 ↓ 1.0 310,122 1

Hash Left Join (cost=255.62..15,749.56 rows=310,121 width=1,804) (actual time=4.018..554.642 rows=310,122 loops=1)

  • Hash Cond: (tt_1.doc_id = d.doc_id)
12. 132.879 399.669 ↓ 1.0 310,122 1

Hash Left Join (cost=201.72..14,878.19 rows=310,121 width=1,792) (actual time=3.468..399.669 rows=310,122 loops=1)

  • Hash Cond: (i.cat_id = c.cat_id)
13. 205.720 266.782 ↓ 1.0 310,122 1

Hash Left Join (cost=200.65..12,913.02 rows=310,121 width=756) (actual time=3.446..266.782 rows=310,122 loops=1)

  • Hash Cond: (tt_1.item_id = i.item_id)
14. 57.651 57.651 ↓ 1.0 310,122 1

Seq Scan on t_tran tt_1 (cost=0.00..8,448.21 rows=310,121 width=16) (actual time=0.007..57.651 rows=310,122 loops=1)

15. 0.918 3.411 ↓ 1.0 1,582 1

Hash (cost=180.89..180.89 rows=1,581 width=748) (actual time=3.411..3.411 rows=1,582 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 524kB
16. 0.324 2.493 ↓ 1.0 1,582 1

Hash Left Join (cost=53.64..180.89 rows=1,581 width=748) (actual time=0.677..2.493 rows=1,582 loops=1)

  • Hash Cond: (i.item_id = ic.item_id)
17. 0.462 2.162 ↓ 1.0 1,582 1

Hash Left Join (cost=52.57..173.86 rows=1,581 width=330) (actual time=0.656..2.162 rows=1,582 loops=1)

  • Hash Cond: (i.item_id = p.item_id)
18. 0.742 1.091 ↓ 1.0 1,582 1

Hash Left Join (cost=1.02..118.15 rows=1,581 width=325) (actual time=0.025..1.091 rows=1,582 loops=1)

  • Hash Cond: (i.manufacturer_id = m.manufacturer_id)
19. 0.343 0.343 ↓ 1.0 1,582 1

Seq Scan on t_item i (cost=0.00..95.81 rows=1,581 width=297) (actual time=0.003..0.343 rows=1,582 loops=1)

20. 0.002 0.006 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=36) (actual time=0.005..0.006 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on t_manufacturer m (cost=0.00..1.01 rows=1 width=36) (actual time=0.003..0.004 rows=1 loops=1)

22. 0.271 0.609 ↑ 1.0 1,542 1

Hash (cost=32.28..32.28 rows=1,542 width=13) (actual time=0.609..0.609 rows=1,542 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
23. 0.338 0.338 ↑ 1.0 1,542 1

Seq Scan on t_pline p (cost=0.00..32.28 rows=1,542 width=13) (actual time=0.008..0.338 rows=1,542 loops=1)

  • Filter: (pl_id = 1)
24. 0.003 0.007 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=426) (actual time=0.007..0.007 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on t_icode ic (cost=0.00..1.03 rows=3 width=426) (actual time=0.003..0.004 rows=3 loops=1)

26. 0.004 0.008 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=1,036) (actual time=0.008..0.008 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on t_cat c (cost=0.00..1.03 rows=3 width=1,036) (actual time=0.004..0.004 rows=3 loops=1)

28. 0.235 0.536 ↑ 1.0 1,018 1

Hash (cost=41.18..41.18 rows=1,018 width=28) (actual time=0.536..0.536 rows=1,018 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
29. 0.301 0.301 ↑ 1.0 1,018 1

Seq Scan on t_doc d (cost=0.00..41.18 rows=1,018 width=28) (actual time=0.005..0.301 rows=1,018 loops=1)

30. 0.004 0.007 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=46) (actual time=0.007..0.007 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on t_doc_type dt (cost=0.00..1.04 rows=4 width=46) (actual time=0.003..0.003 rows=4 loops=1)

32.          

SubPlan (forWindowAgg)

33. 618.876 4,961.952 ↓ 0.0 0 310,122

Nested Loop (cost=1.85..4.14 rows=1 width=516) (actual time=0.016..0.016 rows=0 loops=310,122)

  • Join Filter: (ip.prop_id = p_1.prop_id)
  • Rows Removed by Join Filter: 1
34. 1,108.610 4,031.586 ↑ 1.0 1 310,122

Hash Join (cost=1.85..3.04 rows=1 width=520) (actual time=0.012..0.013 rows=1 loops=310,122)

  • Hash Cond: (pv.prop_value_id = ip.prop_value_id)
35. 442.000 442.000 ↑ 1.0 13 221,000

Seq Scan on t_prop_value pv (cost=0.00..1.13 rows=13 width=520) (actual time=0.001..0.002 rows=13 loops=221,000)

36. 310.122 2,480.976 ↑ 1.0 1 310,122

Hash (cost=1.84..1.84 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=310,122)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
37. 2,170.854 2,170.854 ↑ 1.0 1 310,122

Seq Scan on t_item_prop ip (cost=0.00..1.84 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=310,122)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 66
38. 311.490 311.490 ↑ 1.0 1 311,490

Seq Scan on t_prop p_1 (cost=0.00..1.09 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=311,490)

  • Filter: ((brief)::text = 'GROUP'::text)
  • Rows Removed by Filter: 5
39. 6,512.562 14,885.856 ↑ 1.0 1 310,122

Aggregate (cost=27.16..27.17 rows=1 width=32) (actual time=0.048..0.048 rows=1 loops=310,122)

40. 310.113 8,373.294 ↓ 1.2 5 310,122

Hash Left Join (cost=3.50..27.14 rows=4 width=1,176) (actual time=0.022..0.027 rows=5 loops=310,122)

  • Hash Cond: (ip_1.prop_value_id = pv_1.prop_value_id)
41. 620.316 8,063.172 ↓ 1.2 5 310,122

Nested Loop Left Join (cost=2.21..25.83 rows=4 width=660) (actual time=0.022..0.026 rows=5 loops=310,122)

  • Join Filter: (ip_1.prop_id = cp.prop_id)
  • Rows Removed by Join Filter: 4
42. 1,240.488 4,341.708 ↓ 1.2 5 310,122

Hash Join (cost=2.21..23.93 rows=4 width=560) (actual time=0.012..0.014 rows=5 loops=310,122)

  • Hash Cond: (pt.prop_type_id = p_2.type)
43. 310.122 310.122 ↑ 141.7 6 310,122

Seq Scan on t_prop_type pt (cost=0.00..18.50 rows=850 width=36) (actual time=0.000..0.001 rows=6 loops=310,122)

44. 310.122 2,791.098 ↓ 5.0 5 310,122

Hash (cost=2.19..2.19 rows=1 width=528) (actual time=0.009..0.009 rows=5 loops=310,122)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 1,240.488 2,480.976 ↓ 5.0 5 310,122

Hash Join (cost=1.09..2.19 rows=1 width=528) (actual time=0.006..0.008 rows=5 loops=310,122)

  • Hash Cond: (p_2.prop_id = cp.prop_id)
46. 310.122 310.122 ↑ 1.0 7 310,122

Seq Scan on t_prop p_2 (cost=0.00..1.07 rows=7 width=524) (actual time=0.000..0.001 rows=7 loops=310,122)

47. 310.122 930.366 ↓ 5.0 5 310,122

Hash (cost=1.07..1.07 rows=1 width=4) (actual time=0.003..0.003 rows=5 loops=310,122)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 620.244 620.244 ↓ 5.0 5 310,122

Seq Scan on t_cat_prop cp (cost=0.00..1.07 rows=1 width=4) (actual time=0.001..0.002 rows=5 loops=310,122)

  • Filter: (cat_id = i.cat_id)
  • Rows Removed by Filter: 1
49. 930.294 3,101.148 ↑ 1.0 1 1,550,574

Materialize (cost=0.00..1.84 rows=1 width=108) (actual time=0.001..0.002 rows=1 loops=1,550,574)

50. 2,170.854 2,170.854 ↑ 1.0 1 310,122

Seq Scan on t_item_prop ip_1 (cost=0.00..1.84 rows=1 width=108) (actual time=0.005..0.007 rows=1 loops=310,122)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 66
51. 0.005 0.009 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=520) (actual time=0.009..0.009 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.004 0.004 ↑ 1.0 13 1

Seq Scan on t_prop_value pv_1 (cost=0.00..1.13 rows=13 width=520) (actual time=0.002..0.004 rows=13 loops=1)

53. 930.366 2,170.854 ↑ 1.0 1 310,122

Aggregate (cost=1.53..1.54 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=310,122)

54. 1,240.488 1,240.488 ↑ 1.0 1 310,122

Seq Scan on t_item_image ii (cost=0.00..1.52 rows=1 width=1,040) (actual time=0.003..0.004 rows=1 loops=310,122)

  • Filter: (item_id = i.item_id)
  • Rows Removed by Filter: 41
Planning time : 3.518 ms
Execution time : 24,137.166 ms