From: Sean Chittenden <sean@chittenden.org>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Correlation in cost_index()
Date: Fri, 8 Aug 2003 22:06:36 -0700
Message-ID: <20030809050636.GA40223@perrin.int.nxad.com> (raw)
In-Reply-To: <7fe8jvc4ikh1l53jb5dkaifqs4oc3vi56c@4ax.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>
> ># 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sean@chittenden.org, mkoi-pg@aon.at
Subject: Re: Correlation in cost_index()
In-Reply-To: <20030809050636.GA40223@perrin.int.nxad.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox