From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Query analyse
Date: Fri, 25 Jul 2003 15:39:38 -0400
Message-ID: <27750.1059161978@sss.pgh.pa.us> (raw)
In-Reply-To: <20030725084243.U31713-100000@megazone.bigpanda.com>
References: <20030725084243.U31713-100000@megazone.bigpanda.com>
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> You're probably ending up with different plans since in one case it has
> a plain column reference and in the other it has a marginally complicated
> expression in the join condition.
Yeah. 7.3 and before cannot do merge or hash joins on conditions that
are any more complex than "var = var". The query with the trunc() is
undoubtedly falling back to the stupidest kind of nestloop.
> As something to try, perhaps make a function that returns
> trunc($1/100000.0)*100000 and index on that function for the column and
> see if that changes the plan you get.
It might help --- you might possibly get a nestloop-with-inner-indexscan
out of that. Not sure though, since the planner is likely to be using
bad guesstimates about the selectivity of the expression.
7.4 should do better on this.
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, sszabo@megazone.bigpanda.com, ElielsonF@prodam.sp.gov.br, pgsql-general@postgresql.org
Subject: Re: Query analyse
In-Reply-To: <27750.1059161978@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