From: Tom Lane <tgl@sss.pgh.pa.us>
To: toby@lyricist.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: join optimization problem
Date: Sun, 03 Aug 2003 12:19:49 -0400
Message-ID: <27076.1059927589@sss.pgh.pa.us> (raw)
In-Reply-To: <200308032020.52741.toby@lyricist.com.au>
References: <200307281412.36981.toby@lyricist.com.au>
<2180.1059698182@sss.pgh.pa.us>
<200308032020.52741.toby@lyricist.com.au>
Toby Tremayne <toby@lyricist.com.au> writes:
> The output of explain analyze is this:
> -> Nested Loop (cost=0.00..1657643.30 rows=65807 width=568)
> (actual time=539.82..543404.25 rows=1537 loops=1)
> Join Filter: ("inner".thread_id =
> ("outer".thread_id)::double precision)
Hm. It would appear that you've been inconsistent about the datatype of
thread_id. Current PG releases are not capable of using anything smarter
than a nested-loop join when there is a forced datatype coercion
involved in the join condition. Try making your column datatypes the
same.
Also, do you really need a LEFT JOIN, or would plain inner join do?
It's hard for me to imagine a zero-message thread. If you do have such
things, do you care whether this query finds them? (I'm not sure that
eliminating the LEFT would really help any, but it couldn't hurt, since
LEFT restricts the planner's choices.)
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, toby@lyricist.com.au
Subject: Re: join optimization problem
In-Reply-To: <27076.1059927589@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