From: | Daniel Kalchev <daniel(at)digsys(dot)bg> |
---|---|
To: | "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: again on index usage |
Date: | 2002-01-10 16:42:13 |
Message-ID: | 200201101642.SAA04435@dcave.digsys.bg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>>>"Zeugswetter Andreas SB SD" said:
>
> > > 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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas SB SD | 2002-01-10 17:18:39 | Re: again on index usage |
Previous Message | Daniel Kalchev | 2002-01-10 16:09:15 | Re: again on index usage |