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
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 specific cases
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 using
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 execute
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
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 this ...
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 that
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 some
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 releases
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
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 fixed
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 = case
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.
In that
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
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 about 95% of
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 would
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 further
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 lots of
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 missing
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 suggest a
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 = b1) and
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
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 the
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'
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 Cond
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, tom
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,
well, beg for
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 an easy
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 mistakenly assumed it
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
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.
[...]
Even
31 matches
Mail list logo
