From: Mark Kirkwood <markir@ihug.co.nz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] New Optimizer Behaviour In 7.0b1
Date: Sun, 27 Feb 2000 09:23:44 +1300
Message-ID: <38B8364F.FD8D1D3D@ihug.co.nz> (raw)
References: <38B77942.8B5B6A85@ihug.co.nz>
<26472.951552291@sss.pgh.pa.us>
Tom Lane wrote:
> In this case I guess the first question to ask is whether its
> selectivity estimates are any good. It seems to be estimating that your
> "d0.d0f1 between '1999-11-01' and '1999-12-01'" clause will select about
> 100 of the 900 rows in dim0; is that anywhere near right? Also, in the
> nested-loop plan we can see that it thinks about 1500 rows from fact1
> will match on "d0.d0key = f.d0key" against any given selected row from
> dim0; is that on the mark? Finally, is the estimate that the total
> number of joined rows (before GROUP BY) is about 33333 any good?
>
> If you have not done VACUUM ANALYZE recently on these two tables,
> it'd be worth trying that to see if it brings the estimates any
> closer to reality.
>
> regards, tom lane
Tom,
Here is the row data for comparison with the selectivity estimates:
select count(*) from dim0 d0 where d0.d0f1 between '1999-11-01' and
'1999-12-01' 31 rows
select count(*) from fact1 where d0key =
<value>
3000 rows
total number of joined rows before group
by
9000 rows
( i.e : there are only 3 distinct d0key values in fact1 for the "month" ,
and each one has 3000 rows )
It looks like the estimate on the big table ( fact1 ) are right order of
magnitude, but the small table ( dim0 ) ones are too high (and presumably )
throwing the rest off
I did a vacuum analyze of these tables again, just in case....( no change
to the plans)
Cheers
Mark
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: markir@ihug.co.nz, tgl@sss.pgh.pa.us
Subject: Re: [SQL] New Optimizer Behaviour In 7.0b1
In-Reply-To: <38B8364F.FD8D1D3D@ihug.co.nz>
References: <38B77942.8B5B6A85@ihug.co.nz> <26472.951552291@sss.pgh.pa.us> <38B8364F.FD8D1D3D@ihug.co.nz>
* 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