Re: again on index usage

>>>"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_cost is 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