From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dan Harris <fbsd@drivefaster.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Encouraging multi-table join order
Date: Mon, 10 Apr 2006 19:12:36 -0400
Message-ID: <1543.1144710756@sss.pgh.pa.us> (raw)
In-Reply-To: <443AAC26.4070802@drivefaster.net>
References: <443AAC26.4070802@drivefaster.net>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, fbsd@drivefaster.net
Subject: Re: Encouraging multi-table join order
In-Reply-To: <1543.1144710756@sss.pgh.pa.us>
* 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