pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (6+ messages)  latest in thread

Message-ID: <38B8364F.FD8D1D3D@ihug.co.nz>
Permalink:  ../38B8364F.FD8D1D3D@ihug.co.nz/
Also on:    postgresql.org/message-id/38B8364F.FD8D1D3D@ihug.co.nz

 · 

reply

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