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_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