To: Chris Travers <chris@travelamericas.com>
Cc: Gnanavel S <s.gnanavel@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Left joining against two empty tables makes a query 
In-reply-to: <42E90E19.3030702@travelamericas.com> 
References: <42E86A03.1070803@travelamericas.com>
	<eec3b03c050728025527b91961@mail.gmail.com>
	<42E90E19.3030702@travelamericas.com>
Comments: In-reply-to Chris Travers <chris@travelamericas.com>
	message dated "Thu, 28 Jul 2005 09:55:53 -0700"
Date: Thu, 28 Jul 2005 14:13:51 -0400
Message-ID: <25699.1122574431@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris Travers <chris@travelamericas.com> writes:
> Secondly, the project table has *never* had anything in it.  So where 
> are these numbers coming from?

The planner is designed to assume a certain minimum size (10 pages) when
it sees that a table is of zero physical length.  The reason for this is
that there are lots of scenarios where a plan created just after a table
is first created will continue to be used while the table is filled, and
if we optimized on the assumption of zero size we would produce plans
that seriously suck once the table gets big.  Assuming a few thousand
rows keeps us out of the worst problems of this type.

(If we had an infrastructure for regenerating cached plans then we could
fix this more directly, by replanning whenever the table size changes
"too much".  We don't yet but I hope there will be something by 8.2.)

You might try going ahead and actually putting a row or two into
projects; vacuuming that will change the state to where the planner
will believe the small size.  (If you aren't ever planning to have
anything in projects, why have the table at all?)

			regards, tom lane