
>  > > > What is actually estimated wrong here seems to be the estimated
>  > > > effective cache size, and thus the cache ratio of page fetches.
>  > > 
>  > > Good point, but I think the estimates are only marginally sensitive
>  > > to estimated cache size (if they're not, we have a problem, considering
>  > > how poorly we can estimate the kernel's disk buffer size).  It would
>  > > be interesting for Daniel to try a few different settings of
>  > > effective_cache_size and see how much the EXPLAIN costs change.
>  > 
>  > Well, the number I told him (29370) should clearly prefer the index.
>  > The estimate is very sensitive to this value :-(
>  > With 29370 (=229 Mb) the index cost is 1,364 instead of 3,887 with the 
>  > default of 1000 pages ==> index scan.
> 
> But... if I understand it right (effective_cache_size to be related to kernel 
> buffer space). it turns out that the estimates are different with reality - my 
> buffer cache is ca. 50 MB and I still get at least twice the performance with 
> index scan instead of sequential scan - where as Tom explained things should 
> be much worse.

Since pg only reads one 8k page at a time, the IO performance of a seq scan is
probably not nearly a good as it could be when a lot of other IO is done on the
same drive.

First thing you should verify is if there is actually a measurable difference
in IO throughput on the pg drive during the seq scan and the index scan. (iostat)
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)

Andreas

Thu, 10 Jan 2002 18:18:39 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: again on index usage 

This topic seems to come up a lot.  Is there something we are missing in
the FAQ?

---------------------------------------------------------------------------

Zeugswetter Andreas SB SD wrote:
> 
> >  > > > What is actually estimated wrong here seems to be the estimated
> >  > > > effective cache size, and thus the cache ratio of page fetches.
> >  > > 
> >  > > Good point, but I think the estimates are only marginally sensitive
> >  > > to estimated cache size (if they're not, we have a problem, considering
> >  > > how poorly we can estimate the kernel's disk buffer size).  It would
> >  > > be interesting for Daniel to try a few different settings of
> >  > > effective_cache_size and see how much the EXPLAIN costs change.
> >  > 
> >  > Well, the number I told him (29370) should clearly prefer the index.
> >  > The estimate is very sensitive to this value :-(
> >  > With 29370 (=229 Mb) the index cost is 1,364 instead of 3,887 with the 
> >  > default of 1000 pages ==> index scan.
> > 
> > But... if I understand it right (effective_cache_size to be related to kernel 
> > buffer space). it turns out that the estimates are different with reality - my 
> > buffer cache is ca. 50 MB and I still get at least twice the performance with 
> > index scan instead of sequential scan - where as Tom explained things should 
> > be much worse.
> 
> Since pg only reads one 8k page at a time, the IO performance of a seq scan is
> probably not nearly a good as it could be when a lot of other IO is done on the
> same drive.
> 
> First thing you should verify is if there is actually a measurable difference
> in IO throughput on the pg drive during the seq scan and the index scan. (iostat)
> 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)
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 10 Jan 2002 14:40:25 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
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


Fri, 11 Jan 2002 06:08:48 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 