> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > I would much rather see postgres 'get' (a,b,c) > (a1,
> > b1, c1)...if there is even a chance this is possible, I'll direct my
> > efforts there.
>
> For the ISAM context this whole discussion is kinda moot, because you
> really don't want to have
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> However, this would work:
> Create index on t(stackparam(array[a::text,b::text,c::text),
> array['char(2)', 'int', 'date')];
Well, I fear not all datatypes sort properly when treated as text. Notably
integers don't. "10" sorts before "2" for example
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> I would much rather see postgres 'get' (a,b,c) > (a1,
> b1, c1)...if there is even a chance this is possible, I'll direct my
> efforts there.
For the ISAM context this whole discussion is kinda moot, because you
really don't want to have to plan and e
Greg Stark wrote:
> Well I'm not sure whether you caught it, but Tom did come up with a
> work-around that works with the current infrastructure if all the
columns
> involved are the same datatype.
>
> You can create a regular btree index on the expression array[a,b,c]
and
> then
> do your lookup
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> Well, if and when the rowtype comparison can be made to work over multi
> part keys (and the optimizer is made to do tricks there), postgres can
> be made to give much better generalized ISAM access. In the meantime,
> I'll just troubleshoot specifi
Just an update on this:
queries in the 'next key' form on fields(a,b,c)
only ever use the index for the first field (a). I just never noticed
that before...in most cases this is selective enough. In most logical
cases in multi part keys the most important stuff comes first.
Curiously, queries in
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> One thing I did not like about your first pass is that it makes
>> unsupportable assumptions about there being a semantic relationship
>> between operators named, say, '<' and '<='.
> In that case though, it seems e
Tom Lane <[EMAIL PROTECTED]> writes:
> One thing I did not like about your first pass is that it makes
> unsupportable assumptions about there being a semantic relationship
> between operators named, say, '<' and '<='.
Hm, I think I even had caught that issue on the mailing list previously.
I
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> The only reason the code in parse_expr.c appears new is that the
>> functionality used to be in gram.y.
> Ah, that was what I was missing. Though it's odd since it seems there was code
> in parse_expr.c to handle the
Tom Lane <[EMAIL PROTECTED]> writes:
> The only reason the code in parse_expr.c appears new is that the
> functionality used to be in gram.y.
Ah, that was what I was missing. Though it's odd since it seems there was code
in parse_expr.c to handle the "=" case specially.
> I'd like to see this f
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Removing <,<=,>,>= would be trivial.
>
> ... and not backwards-compatible. If we did that then cases involving
> unlabeled row expressions would no longer work as they did in prior
> releases. For example
>
> select (1,2,3) < (4,5,6);
>
> is a
Greg Stark <[EMAIL PROTECTED]> writes:
> Removing <,<=,>,>= would be trivial.
... and not backwards-compatible. If we did that then cases involving
unlabeled row expressions would no longer work as they did in prior
releases. For example
select (1,2,3) < (4,5,6);
is accepted by all rel
> Greg Stark <[EMAIL PROTECTED]> writes:
> This approach won't get the optimizer to actually use an index for
these
> comparisons, but it will fix the semantics to match the spec. Later we
can
> either improve the optimizer to detect expressions like this (which I
> think
> would be cooler since so
Greg Stark <[EMAIL PROTECTED]> writes:
> Fixing it to write out complex boolean expressions wouldn't be too hard, but
> I'm not clear it would be worth it, since I suspect the end result would be as
> the comment indicates, to introduce a new runtime node.
Just to prove that it isn't really all
Stephan Szabo <[EMAIL PROTECTED]> writes:
> Given the comment on make_row_op,
> /*
>* XXX it's really wrong to generate a simple AND combination for < <=
>* > >=. We probably need to invent a new runtime node type to handle
>* those correctly. For the moment, though, keep on doing
On Tue, 27 Jul 2004, Merlin Moncure wrote:
> Greg Stark wrote:
> > > > do it for multi-column keys. It seems it would be nice if some
> syntax
> > > > similar to (a,b,c) > (a1,b1,c1) worked for this.
> > Hum. It would seem my intuition matches the SQL92 spec and Postgres
> gets
> > this
> > wrong
Markus wrote:
> > The basic problem is the planner can't always match the query to the
> > index. So, either the planner has to be helped/fixed or I have to
> > explore another solution. This seems to happen most when the 'a'
> > column has very poor selectivity. In this case, the planner will
o
> Hmm, it sure seems like there ought to be an easy way to do this...
Here is the only alternative that I see:
create function column_stacker(text[] columns, text[] types) returns
text
[...]
language 'C' immutable;
the above function stacks the columns together in a single string for
easy range i
> > select * from t where
> > a >= a1 and
> > (a > a1 or b >= b1) and
> > (a > a1 or b > b1 or c > c1)
>
> > In about 95% of cases, the planner correctly selects the index
t(a,b,c)
> > and uses it.
>
> I'm surprised it's that good. Why not do
It is. In fact, it's so good, I mis
Tom Lane <[EMAIL PROTECTED]> writes:
> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
>> Plus, your where clause does not guarantee results.
> No, but in combination with the ORDER BY it does.
Oh, wait, you're right --- I'm mis-visualizing the situation.
Hmm, it sure seems like there ought to be a
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > do it for multi-column keys. It seems it would be nice if some syntax
> > similar to (a,b,c) > (a1,b1,c1) worked for this.
>
> 'nice' would be an understatement...
>
> if the above syntax is not defined in the standard, I would humbly suggest,
> w
> > So, for a table t with a three part key over columns a,b,c, the
query
> > to read the next value from t for given values a1, b1, c1 is
> >
> > select * from t where
> > a >= a1 and
> > (a > a1 or b >= b1) and
> > (a > a1 or b > b1 or c > c1)
>
> You mut not rely on such tricker
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> Plus, your where clause does not guarantee results.
No, but in combination with the ORDER BY it does. Please note also
that the offset would *always* be one, so your gripe about it not
scaling seems misguided to me.
regards,
Greg wrote:
> One thing that can help is to add ORDER BY a,b,c LIMIT 1 to your
query.
> That
> will virtually guarantee that it uses an index scan, which will at
least
> avoid
> making it scan all the records *after* finding the match. However it
still
> doesn't seem to make Postgres use an Index C
Hi, Merlin,
On Tue, 27 Jul 2004 10:21:32 -0400
"Merlin Moncure" <[EMAIL PROTECTED]> wrote:
> The basic problem is the planner can't always match the query to the
> index. So, either the planner has to be helped/fixed or I have to
> explore another solution. This seems to happen most when the 'a
I said:
> Oh, wait, you're right --- I'm mis-visualizing the situation.
> Hmm, it sure seems like there ought to be an easy way to do this...
The problem is that a multi-column index doesn't actually have the
semantics you want. If you are willing to consider adding another
index (or replacing th
You only want one record to be returned? Tack a LIMIT 1 onto the end of
the query.
> My problem is deceptively simple: how you read the next record from a
> table based on a given set of values? In practice, this is difficult to
> implement. If anybody can suggest a alternative/better way to th
Hi, Merlin,
On Tue, 27 Jul 2004 09:07:02 -0400
"Merlin Moncure" <[EMAIL PROTECTED]> wrote:
> So, for a table t with a three part key over columns a,b,c, the query
> to read the next value from t for given values a1, b1, c1 is
>
> select * from t where
> a >= a1 and
> (a > a1 or b >=
Hi, Merlin,
On Tue, 27 Jul 2004 16:13:25 +0200, I myself wrote:
> You mut not
Should be "must", not "mut" :-)
> > My problem is deceptively simple: how you read the next record from
> > a table based on a given set of values? In practice, this is
> > difficult to implement. If anybody can su
"Merlin Moncure" <[EMAIL PROTECTED]> wrote ..
[snip]
> select * from t where
> a >= a1 and
> (a > a1 or b >= b1) and
> (a > a1 or b > b1 or c > c1)
I don't see why this is guaranteed to work without an ORDER BY clause, even if TABLE t
is clustered on the correct index. Am I miss
> Interestingly, it is possible to rewrite the above query by switching
> and with or and >= with >. However when written that way, the planner
> almost never gets it right.
Well, note it's still not really getting it right even in your case. It's
doing an index scan on a>=a1 but if you have lo
> SELECT * FROM t WHERE
> (a >= a1 AND b>=b1 AND c>=c1) ORDER BY a,b,c LIMIT 1 OFFSET 1;
>
> using the way LIMIT cuts down on sort time (I've never tried it with
both
> LIMIT and OFFSET, though; you could always use LIMIT 2 and skip a
record
> client-side if that works better).
Don't want to furt
Greg Stark wrote:
> > > do it for multi-column keys. It seems it would be nice if some
syntax
> > > similar to (a,b,c) > (a1,b1,c1) worked for this.
> Hum. It would seem my intuition matches the SQL92 spec and Postgres
gets
> this
> wrong.
[...]
> Even if Postgres did this right I'm not sure that
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> So, for a table t with a three part key over columns a,b,c, the query to
> read the next value from t for given values a1, b1, c1 is
> select * from t where
> a >= a1 and
> (a > a1 or b >= b1) and
> (a > a1 or b > b1 or c > c1)
> In
I am in a situation where I have to treat a table as logically ordered
based on an index. Right now I'm doing this via queries, and a I need a
better way to do it. Cursors do not meet my requirements, because they
are always insensitive. Also, my performance requirements are
extreme...I need 100
35 matches
Mail list logo
