Re: Correlation in cost_index()

> ># SHOW effective_cache_size ;
> > effective_cache_size
> >----------------------
> > 4456
> >(1 row)
>
> Only 35 MB? Are you testing on such a small machine?

Testing on my laptop right now... can't hack on my production DBs the
same way I can my laptop.

> >The stats are attached && bzip2 compressed.
>
> Nothing was attached. Did you upload it to your web site?

Gah, not yet, forgot to send it.

http://people.FreeBSD.org/~seanc/pg_statistic.txt.bz2

> >> >I can say with pretty high confidence that the patch to use a
> >> >geometric mean isn't correct
>
> >... the problem with your patch was that it picked an index less
> >often than the current code when there was low correlation.
>
> In cost_index.sxc I get lower estimates for *all* proposed new
> interpolation methods. Either my C code doesn't implement the same
> calculations as the spreadsheet, or ...
>
> >I manually applied bits of it [...]
>
> ... could this explain the unexpected behaviour?

Don't think so... the run_cost was correct, I didn't modify the
indexCorrelation behavior beyond forcing it to 1.0.

> I'm currently downloading your dump. Can you post the query you
> mentioned above?

SELECT * FROM report_user_cat_count AS rucc WHERE rucc.html_bytes > 20000000::BIGINT;
SELECT * FROM report_user_cat_count AS rucc WHERE user_id = 42 AND utc_date = NOW();
SELECT * FROM report_user_cat_count AS rucc WHERE user_id = 42;
SELECT * FROM report_user_cat_count AS rucc WHERE user_id < 1000 AND utc_date > '2003-01-01'::TIMESTAMP WITH TIME ZONE;

And various timestamps back to 2002-09-19 and user_id's IN(1,42).

-sc

--
Sean Chittenden