>>>"Zeugswetter Andreas SB SD" said:> First thing you should verify is if there is actually a measurable differenc
e>in IO throughput on the pg drive during the seq scan and the index scan. (io stat)> If there is not, then
random_page_costis too high in your scenario.> (All assuming your data is not still clustered like Tom suggested)
At this idle time (got to have other emergency at 5am in the office :) here is
what I have (sd0 is the 'system' drive, sd1 is where postgres data lives):
tin tout sps tps msps sps tps msps usr nic sys int idl 0 39 831 12 2.0 8962 121 3.6 4 26 7 0
63 0 13 215 4 7.7 9917 122 3.7 5 24 5 0 66 0 13 216 3 6.1 7116 115 4.1 5 23 4 0
68 0 13 220 3 5.0 9401 128 5.0 5 17 4 0 74 0 13 226 3 12.2 9232 122 3.8 4 24 4
0 67 0 13 536 26 8.5 11353 147 4.4 13 16 9 0 62 0 13 259 5 5.8 12102 165 4.1 8 14
8 0 70 0 13 492 20 7.2 13913 186 4.5 8 9 6 0 76 0 13 185 2 4.7 11423 184 5.0 14
6 8 0 72
running index scan:
0 13 274 8 4.9 5786 145 4.4 18 10 8 0 64 0 13 210 3 8.1 5707 153 3.9 20 9 6 0
64 0 13 286 8 7.7 6283 139 4.3 21 9 8 0 62 0 13 212 3 9.7 5900 133 3.3 22 13 7 0
58 0 13 222 3 6.0 5811 148 3.5 20 12 6 0 61 0 13 350 16 7.5 5640 134 4.1 22 12 7
0 58
(seems to be slowing down other I/O :)
running seq scan:
0 13 50 4 1.9 4787 101 3.8 24 12 7 0 57 0 13 34 3 5.6 5533 105 3.4 24 12 6 0
58 0 13 42 4 3.1 5414 103 3.0 25 12 6 0 58 0 13 26 2 0.0 5542 102 3.9 28 12 6 0
54 0 13 52 5 2.8 5644 112 4.1 24 11 7 0 58 0 13 27 2 4.1 6462 122 4.0 26 8 7
0 60 0 13 36 3 2.0 5616 128 4.2 22 8 7 0 63
I can't seem to find any difference... Perhaps this is because the
'sequential' data is anyway scattered all around the disk.
I have done this test first, now I will try the random() clustering Tom
suggested (although... isn't random not so random to trust it in this
scenario? :)
Daniel
