Date: Fri, 8 Aug 2003 22:06:36 -0700
From: Sean Chittenden <sean@chittenden.org>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Correlation in cost_index()
Message-ID: <20030809050636.GA40223@perrin.int.nxad.com>
References: <n6cmpug13b9rk1srebjvhphg0lm8dou1kn@4ax.com>
	<29475.1033598929@sss.pgh.pa.us>
	<20030807204419.GJ94710@perrin.int.nxad.com>
	<9452.1060296663@sss.pgh.pa.us>
	<20030808180656.GL94710@perrin.int.nxad.com>
	<ruu7jvo5vnjl064rm327ndqml0pmvgdsfa@4ax.com>
	<20030808235348.GR94710@perrin.int.nxad.com>
	<7fe8jvc4ikh1l53jb5dkaifqs4oc3vi56c@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <7fe8jvc4ikh1l53jb5dkaifqs4oc3vi56c@4ax.com>
User-Agent: Mutt/1.5.4i

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