>>>"Zeugswetter Andreas SB SD" said:> > > > What is actually estimated wrong here seems to be the estimated> > >
effectivecache size, and thus the cache ratio of page fetches.> > > > Good point, but I think the estimates are only
marginallysensitive> > to estimated cache size (if they're not, we have a problem, considering> > how poorly we can
estimatethe kernel's disk buffer size). It would> > be interesting for Daniel to try a few different settings of> >
effective_cache_sizeand see how much the EXPLAIN costs change.> > Well, the number I told him (29370) should clearly
preferthe index.> The estimate is very sensitive to this value :-(> With 29370 (=229 Mb) the index cost is 1,364
insteadof 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.
I considered the possibility that the clustered table can still maintain some
ordering by ipdate after being clustered by ipaddr - but with over 65k ip
addresses, almost evenly spread, this should be not so significant.
Best Regards,
Daniel
