explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rt2e

Settings
# exclusive inclusive rows x rows loops node
1. 0.418 549.902 ↓ 44.0 44 1

Sort (cost=1,039.84..1,039.85 rows=1 width=1,402) (actual time=549.900..549.902 rows=44 loops=1)

  • Sort Key: whitem.whitem_expires, whobj.whobj_seqnum DESC, whitem.whitem_dim1
  • Sort Method: quicksort Memory: 69kB
2.          

CTE site

3. 0.005 0.100 ↑ 1.0 1 1

Nested Loop (cost=0.70..4.73 rows=1 width=8) (actual time=0.098..0.100 rows=1 loops=1)

4. 0.061 0.061 ↑ 1.0 1 1

Index Scan using whloc_pkey on whloc whloc_1 (cost=0.42..2.44 rows=1 width=8) (actual time=0.059..0.061 rows=1 loops=1)

  • Index Cond: (whloc_id = 503729640)
5. 0.034 0.034 ↑ 1.0 1 1

Index Scan using wharea_pkey on wharea wharea_1 (cost=0.28..2.30 rows=1 width=16) (actual time=0.034..0.034 rows=1 loops=1)

  • Index Cond: (wharea_id = whloc_1.whloc_wharea_id)
6.          

CTE pick_area

7. 0.025 0.037 ↑ 1.0 1 1

Index Only Scan using wharea_pkey on wharea wharea_2 (cost=2.71..3.73 rows=1 width=8) (actual time=0.036..0.037 rows=1 loops=1)

  • Index Cond: (wharea_id = $2)
  • Heap Fetches: 0
8.          

Initplan (for Index Only Scan)

9. 0.012 0.012 ↑ 1.0 1 1

Index Scan using whloc_pkey on whloc whloc_2 (cost=0.42..2.44 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (whloc_id = 503729640)
10.          

CTE allowed_locs

11. 0.532 5.121 ↓ 47.0 3,384 1

Nested Loop (cost=4.54..179.58 rows=72 width=8) (actual time=0.502..5.121 rows=3,384 loops=1)

12.          

Initplan (for Nested Loop)

13. 0.104 0.104 ↑ 1.0 1 1

CTE Scan on site (cost=0.00..0.02 rows=1 width=8) (actual time=0.101..0.104 rows=1 loops=1)

14. 0.039 0.039 ↑ 1.0 1 1

CTE Scan on pick_area (cost=0.00..0.02 rows=1 width=8) (actual time=0.038..0.039 rows=1 loops=1)

15. 0.012 0.474 ↓ 3.0 6 1

Nested Loop (cost=4.09..6.90 rows=2 width=8) (actual time=0.436..0.474 rows=6 loops=1)

16. 0.221 0.221 ↑ 1.0 1 1

Index Only Scan using whsite_pkey on whsite (cost=0.14..1.16 rows=1 width=8) (actual time=0.220..0.221 rows=1 loops=1)

  • Index Cond: (whsite_id = $4)
  • Heap Fetches: 1
17. 0.104 0.241 ↓ 3.0 6 1

Bitmap Heap Scan on wharea wharea_3 (cost=3.94..5.72 rows=2 width=16) (actual time=0.207..0.241 rows=6 loops=1)

  • Recheck Cond: ((wharea_whsite_id = $4) AND (wharea_whareatype_id = 3))
  • Filter: (wharea_id <> $5)
  • Heap Blocks: exact=3
18. 0.004 0.137 ↓ 0.0 0 1

BitmapAnd (cost=3.94..3.94 rows=2 width=0) (actual time=0.137..0.137 rows=0 loops=1)

19. 0.082 0.082 ↑ 1.7 27 1

Bitmap Index Scan on wharea_whsite_id (cost=0.00..1.63 rows=47 width=0) (actual time=0.082..0.082 rows=27 loops=1)

  • Index Cond: (wharea_whsite_id = $4)
20. 0.051 0.051 ↓ 1.0 105 1

Bitmap Index Scan on wharea_whareatype_id (cost=0.00..2.06 rows=104 width=0) (actual time=0.051..0.051 rows=105 loops=1)

  • Index Cond: (wharea_whareatype_id = 3)
21. 3.972 3.972 ↓ 3.8 564 6

Index Scan using whloc_wharea_id on whloc whloc_3 (cost=0.42..84.82 rows=150 width=16) (actual time=0.035..0.662 rows=564 loops=6)

  • Index Cond: (whloc_wharea_id = wharea_3.wharea_id)
  • Filter: ((NOT whloc_external) AND (whloc_id <> 503729640))
22.          

CTE allowed_whitems

23. 0.125 2.151 ↓ 1.3 106 1

Nested Loop (cost=2.61..338.16 rows=79 width=19) (actual time=0.192..2.151 rows=106 loops=1)

24. 0.266 0.314 ↓ 1.1 107 1

Bitmap Heap Scan on whitem whitem_1 (cost=2.19..95.98 rows=99 width=19) (actual time=0.088..0.314 rows=107 loops=1)

  • Recheck Cond: (whitem_mtart_id = 1324447632)
  • Heap Blocks: exact=15
25. 0.048 0.048 ↓ 1.1 107 1

Bitmap Index Scan on whitem_mtart_id (cost=0.00..2.17 rows=99 width=0) (actual time=0.048..0.048 rows=107 loops=1)

  • Index Cond: (whitem_mtart_id = 1324447632)
26. 1.712 1.712 ↑ 1.0 1 107

Index Scan using f4108c_iolotn on f4108c (cost=0.42..2.45 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=107)

  • Index Cond: ((f4108c_iolotn)::text = (whitem_1.whitem_dim1)::text)
  • Filter: ((f4108c_iolots)::text = ANY ('{"",F,J,H,S}'::text[]))
  • Rows Removed by Filter: 0
27. 121.087 549.484 ↓ 44.0 44 1

Nested Loop Semi Join (cost=275.04..513.63 rows=1 width=1,402) (actual time=73.040..549.484 rows=44 loops=1)

  • Join Filter: (whitem.whitem_id = allowed_whitems.em_id)
  • Rows Removed by Join Filter: 1256196
28. 18.998 285.981 ↓ 11,868.0 11,868 1

Nested Loop (cost=275.04..511.06 rows=1 width=1,402) (actual time=11.482..285.981 rows=11,868 loops=1)

29. 11.900 183.907 ↓ 11,868.0 11,868 1

Nested Loop (cost=274.62..510.57 rows=1 width=1,232) (actual time=11.410..183.907 rows=11,868 loops=1)

30. 9.761 136.403 ↓ 11,868.0 11,868 1

Nested Loop (cost=274.20..510.11 rows=1 width=1,132) (actual time=11.361..136.403 rows=11,868 loops=1)

31. 7.639 79.170 ↓ 11,868.0 11,868 1

Hash Left Join (cost=273.78..509.65 rows=1 width=850) (actual time=11.311..79.170 rows=11,868 loops=1)

  • Hash Cond: ((whobj.whobj_whitem_id = whordpos.whordpos_whitem_id) AND (whobj.whobj_whloc_id = whordpos.whordpos_source_whloc_id) AND (wharea.wharea_whsite_id = whord.whord_whsite_id))
  • Filter: ((min(whordpos.whordpos_id)) IS NULL)
  • Rows Removed by Filter: 67
32. 5.257 69.583 ↓ 72.3 11,935 1

Nested Loop (cost=2.74..237.31 rows=165 width=818) (actual time=9.331..69.583 rows=11,935 loops=1)

33. 3.233 37.254 ↓ 47.0 3,384 1

Nested Loop (cost=2.32..193.11 rows=72 width=174) (actual time=9.283..37.254 rows=3,384 loops=1)

34. 2.661 27.253 ↓ 47.0 3,384 1

Nested Loop (cost=2.04..171.66 rows=72 width=103) (actual time=9.266..27.253 rows=3,384 loops=1)

35. 4.194 11.056 ↓ 47.0 3,384 1

HashAggregate (cost=1.62..2.34 rows=72 width=8) (actual time=9.221..11.056 rows=3,384 loops=1)

  • Group Key: allowed_locs.whloc_id
36. 6.862 6.862 ↓ 47.0 3,384 1

CTE Scan on allowed_locs (cost=0.00..1.44 rows=72 width=8) (actual time=0.504..6.862 rows=3,384 loops=1)

37. 13.536 13.536 ↑ 1.0 1 3,384

Index Scan using whloc_pkey on whloc (cost=0.42..2.35 rows=1 width=95) (actual time=0.004..0.004 rows=1 loops=3,384)

  • Index Cond: (whloc_id = allowed_locs.whloc_id)
38. 6.768 6.768 ↑ 1.0 1 3,384

Index Scan using wharea_pkey on wharea (cost=0.28..0.30 rows=1 width=71) (actual time=0.002..0.002 rows=1 loops=3,384)

  • Index Cond: (wharea_id = whloc.whloc_wharea_id)
39. 27.072 27.072 ↑ 1.2 4 3,384

Index Scan using whobj_whloc_id on whobj (cost=0.42..0.56 rows=5 width=652) (actual time=0.005..0.008 rows=4 loops=3,384)

  • Index Cond: (whobj_whloc_id = whloc.whloc_id)
40. 0.073 1.948 ↓ 1.1 128 1

Hash (cost=268.92..268.92 rows=121 width=32) (actual time=1.948..1.948 rows=128 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
41. 0.218 1.875 ↓ 1.1 128 1

HashAggregate (cost=266.50..267.71 rows=121 width=32) (actual time=1.823..1.875 rows=128 loops=1)

  • Group Key: whordpos.whordpos_whitem_id, whordpos.whordpos_source_whloc_id, whord.whord_whsite_id
42. 0.706 1.657 ↓ 1.1 128 1

Nested Loop (cost=5.34..265.29 rows=121 width=32) (actual time=0.144..1.657 rows=128 loops=1)

43. 0.107 0.183 ↓ 1.1 128 1

Bitmap Heap Scan on whord (cost=5.02..102.66 rows=121 width=16) (actual time=0.106..0.183 rows=128 loops=1)

  • Recheck Cond: (whord_whordlc_id = ANY ('{1,2,4,5}'::bigint[]))
  • Heap Blocks: exact=5
44. 0.076 0.076 ↓ 1.5 177 1

Bitmap Index Scan on whord_whordlc_id (cost=0.00..4.99 rows=121 width=0) (actual time=0.076..0.076 rows=177 loops=1)

  • Index Cond: (whord_whordlc_id = ANY ('{1,2,4,5}'::bigint[]))
45. 0.384 0.768 ↑ 1.0 1 128

Bitmap Heap Scan on whordpos (cost=0.32..1.33 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=128)

  • Recheck Cond: (whordpos_whord_id = whord.whord_id)
  • Heap Blocks: exact=128
46. 0.384 0.384 ↑ 1.0 1 128

Bitmap Index Scan on whordpos_whord_id (cost=0.00..0.32 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=128)

  • Index Cond: (whordpos_whord_id = whord.whord_id)
47. 47.472 47.472 ↑ 1.0 1 11,868

Index Scan using whitem_pkey on whitem (cost=0.42..0.47 rows=1 width=282) (actual time=0.004..0.004 rows=1 loops=11,868)

  • Index Cond: (whitem_id = whobj.whobj_whitem_id)
48. 35.604 35.604 ↑ 1.0 1 11,868

Index Scan using mtart_pkey on mtart (cost=0.42..0.46 rows=1 width=100) (actual time=0.003..0.003 rows=1 loops=11,868)

  • Index Cond: (mtart_id = whitem.whitem_mtart_id)
49. 83.076 83.076 ↑ 1.0 1 11,868

Index Scan using f4101cfactor_litm_text on f4101cfactor (cost=0.42..0.48 rows=1 width=170) (actual time=0.007..0.007 rows=1 loops=11,868)

  • Index Cond: ((f4101cfactor_litm)::text = (mtart.mtart_num)::text)
50. 142.416 142.416 ↓ 1.3 106 11,868

CTE Scan on allowed_whitems (cost=0.00..1.58 rows=79 width=8) (actual time=0.000..0.012 rows=106 loops=11,868)

Planning time : 38.175 ms
Execution time : 551.433 ms