On Tue, Feb 28, 2006 at 3:02 pm, in message
[EMAIL PROTECTED], Jim C. Nasby
[EMAIL PROTECTED]
wrote:
Maybe it's just the way my twisted mind thinks, but I generally
prefer
using a JOIN when possible...
Definitely. But sometimes you don't want one row from a table for each
qualifying row
On Tue, Feb 28, 2006 at 09:44:08AM -0600, Kevin Grittner wrote:
It struck me that it would be outstanding if the planner could
recognize this sort of situation, and build a temporary index based on
the snapshot of the data visible to the transaction. It seems to me
that the obvious downside
Jim C. Nasby [EMAIL PROTECTED] writes:
FWIW, Sybase supported something similar a long time ago. It had the
ability to build a temporary 'clustered table' (think index organized
table) when there was enough benefit to do so. This is actually
much easier to make happen inside a transaction for
On Tue, Feb 28, 2006 at 11:05 am, in message
[EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
The issue at hand really has nothing to do with temp indexes, it's
with
the constrained way that the planner deals with EXISTS subplans.
Yet when the index exists, the query is optimized well.
Kevin Grittner [EMAIL PROTECTED] writes:
EXISTS works in situations where
you need to compare on multiple columns, so it is useful in many
situations where IN or MIN/MAX techniques just don't work.
IN works fine on multiple columns:
(foo, bar, baz) IN (SELECT x, y, z FROM ...)
On Tue, Feb 28, 2006 at 11:55:32AM -0600, Kevin Grittner wrote:
Also, EXISTS works in situations where
you need to compare on multiple columns, so it is useful in many
situations where EXISTS or MIN/MAX techniques just don't work.
Sorry. That should have read:
EXISTS works in situations
On Tue, Feb 28, 2006 at 11:36:28AM -0600, Kevin Grittner wrote:
digression
I'm all for that. So far, we've been going after the low-hanging fruit
in our use of PostgreSQL. When we get to the main applications, we're
going to be dealing with a lot more in the way of EXISTS clauses. The
On Tue, Feb 28, 2006 at 11:05 am, in message
[EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
The limiting factor is that EXISTS subplans
aren't flattened ... and once that's fixed, I doubt the example
would
need any new kind of join support.
I rewrote the query to use IN predicates