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
Are there any tools that help with postgres/postgis performance tuning?
So they measure the acutal tuple costs and cpu power, or suggest optimal
values for the index sample counts?
Have you turned on the stat_* settings in postgresql.conf and then
examined the pg_stat_* system views?
Chris
--
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
Hello,
Are there any tools that help with postgres/postgis performance tuning?
So they measure the acutal tuple costs and cpu power, or suggest optimal
values for the index sample counts?
I could imagine that some profiling on a typical workload (or realistic
simulation thereof) could be automat
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
Dear all,
This is my two last line of a vacuum full verbose analyse;.
INFO: free space map: 27 relations, 4336 pages stored; 3232 total pages
needed
DETAIL: Allocated FSM size: 2000 relations + 5000 pages = 293088 kB
What are the good parameters to set with those informations :
max_fsm_p
24 matches
Mail list logo
