Skip site navigation (1) Skip section navigation (2)

Re: [SQL] New Optimizer Behaviour In 7.0b1

From: Mark Kirkwood <markir(at)ihug(dot)co(dot)nz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] New Optimizer Behaviour In 7.0b1
Date: 2000-02-26 20:23:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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


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  =
3000 rows
total number of joined rows before group
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)



In response to


pgsql-sql by date

Next:From: Tom LaneDate: 2000-02-26 21:27:06
Subject: Re: [SQL] text -> char
Previous:From: Tom LaneDate: 2000-02-26 08:04:51
Subject: Re: [SQL] New Optimizer Behaviour In 7.0b1

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group