To: Dan Harris <fbsd@drivefaster.net>
cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Encouraging multi-table join order 
In-reply-to: <443AAC26.4070802@drivefaster.net> 
References: <443AAC26.4070802@drivefaster.net>
Comments: In-reply-to Dan Harris <fbsd@drivefaster.net>
	message dated "Mon, 10 Apr 2006 13:04:06 -0600"
Date: Mon, 10 Apr 2006 19:12:36 -0400
Message-ID: <1543.1144710756@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Dan Harris <fbsd@drivefaster.net> writes:
> I have a query that is intended to select from multiple "small tables" 
> to get a limited subset of "incidentid" and then join with a "very 
> large" table.  One of the operations will require a sequential scan, but 
> the planner is doing the scan on the very large table before joining the 
> small ones, resulting in a huge amount of disk I/O.  How would I make 
> this query join the large table only after narrowing down the possible 
> selections from the smaller tables?  This is running on version 8.0.3.

That's very strange --- the estimated cost of the seqscan is high enough
that the planner should have chosen a nestloop with inner indexscan on
the big table.  I'm not sure about the join-order point, but the hash
plan for the first join seems wrong in any case.

Um, you do have an index on eventactivity.incidentid, right?  What's the
datatype(s) of the incidentid columns?  What happens to the plan if you
turn off enable_hashjoin and enable_mergejoin?

			regards, tom lane