Matthew Wakeling wrote:
> Trying to rewrite a plpgsql function in C.
>
> How do I do the equivalent of:
>
> FOR loc IN SELECT * FROM location ORDER BY objectid, intermine_start,
> intermine_end LOOP
> END LOOP;
>
> in a C function?
Please create a new message to the list with a new subject line
Trying to rewrite a plpgsql function in C.
How do I do the equivalent of:
FOR loc IN SELECT * FROM location ORDER BY objectid, intermine_start,
intermine_end LOOP
END LOOP;
in a C function?
Matthew
--
I wouldn't be so paranoid if you weren't all out to get me!!
--
Sent via pgsql-performance
On Wed, 1 Apr 2009, Matthew Wakeling wrote:
So, I have written a plpgsql function to calculate overlaps. It works
reasonably quickly where there aren't that many overlaps. However, it seems
to go very slowly when there are a large number of rows to return.
In plpgsql, what happens about memory
On Mon, 30 Mar 2009, Віталій Тимчишин wrote:
What is the bad plan? Is it like the first plan from your first message?
It's the plan a few messages back. The UNION ALL query I showed
effectively got the database to do it both ways round.
It's the case that a "between" index scan will return m
On Wed, 1 Apr 2009, Віталій Тимчишин wrote:
The outer nested join has the VALUES as the main loop, and the
complicated join as the leaf. So, the complicated
overlap-finding join gets run twice.
That's weird. What do you have as statistics target? Planner is incorrect few
orders of
>
>
> The outer nested join has the VALUES as the main loop, and the complicated
> join as the leaf. So, the complicated overlap-finding join gets run twice.
That's weird. What do you have as statistics target? Planner is incorrect
few orders of magnitude, so increasing it may help.
BTW: One of c
On Mon, 30 Mar 2009, Віталій Тимчишин wrote:
select
case when n == 1 then id1 else id2 end,
case when n == 2 then id1 else id2 end
from (
SELECT
l1.id AS id1,
l2.id AS id2
FROM
location l1,
location l2
WHERE
l1.objectid = 22893
AND l2.objectid = 22893
AND l1.id <
On Mon, 30 Mar 2009, Marc Mamin wrote:
Are your objects limited to some smaller ranges of your whole interval ?
If yes you may possibly reduce the ranges to search for while using an
additional table with the min(start) max(end) of each
object...
No, they aren't. However, even if they were, th
>
>
> Yeah, that's nice.
>
> However, it is still the case that we can't trust the database to choose
> the correct plan. It is currently only choosing the correct plan now by
> chance, and some time later it may by chance switch to one that takes 40
> minutes.
What is the bad plan? Is it like th
Hello Matthew,
Another idea:
Are your objects limited to some smaller ranges of your whole interval ?
If yes you may possibly reduce the ranges to search for while using an
additional table with the min(start) max(end) of each object...
Marc Mamin
Hi.
Look, what I did mean by "symmetric" is that you don't need to make second
part of query because you will get just same results simply by
select
case when n == 1 then id1 else id2 end,
case when n == 2 then id1 else id2 end
from (
SELECT
l1.id AS id1,
l2.id AS id2
FROM
location l1,
On Mon, 30 Mar 2009, Віталій Тимчишин wrote:
select
case when n == 1 then id1 else id2 end,
case when n == 2 then id1 else id2 end
from (
... a, (values (1),(2)) b(n)
Yeah, that's nice.
However, it is still the case that we can't trust the database to choose
the correct plan. It is curren
On Mon, 30 Mar 2009, Marc Mamin wrote:
But I often read that BETWEEN is faster than using 2 comparison operators.
http://www.postgresql.org/docs/current/static/functions-comparison.html
says otherwise.
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
There is no difference between th
Shouldn't Postgres favour a "between" index scan over an open-ended
one?
On Fri, 27 Mar 2009, Tom Lane wrote:
Currently the planner only notices that for a range check that involves
comparisons of the same variable expression to two constants (or
pseudoconstants anyway). In principle it might
>> WHERE (l2.start BETWEEN l1.start AND l1.end
>> OR
>> l1.start BETWEEN l2.start AND l2.end
>> )
>Yes, that's another way to calculate an overlap. However, it turns out to not
>be that fast.
>The problem is that OR there, which causes a bitmap index scan, as the le
On Fri, 27 Mar 2009, Marc Mamin wrote:
if your data are mostly static and you have a few mains objects,
maybe you can have some gain while defining conditional indexes for those plus
one for the rest
and then slicing the query:
Maybe. I thought about doing that. However, I am not convinced tha
On Fri, 27 Mar 2009, Dimitri Fontaine wrote:
Maybe it's just that I didn't devote enough time to reading your detailed
explanation above, but this part sounds like it could be done in an aggregate
you'd use in a correlated subquery containing the right ORDER BY, couldn't
it?
http://www.postgre
On Fri, 27 Mar 2009, Tom Lane wrote:
Notice the two different index conditions:
(l1.end > l2.start) AND (l1.start < l2.start) - "between"
(l1.end > l2.start) AND (l1.start >= l2.start) - open-ended
Both have a cost of (cost=0.00..123.10 rows=4809 width=12)
Currently the planner only
Hello,
if your data are mostly static and you have a few mains objects,
maybe you can have some gain while defining conditional indexes for those plus
one for the rest
and then slicing the query:
create index o_1x on X (start,end,id) where object_id = 1
create index o_2x on X (start,end,id) wh
Hi,
Le 26 mars 09 à 15:30, Matthew Wakeling a écrit :
Now, it happens that there is an algorithm for calculating overlaps
which is really quick. It involves iterating through the table in
order of the start variable and keeping a list of ranges which
"haven't ended yet". When you read the n
Matthew Wakeling writes:
> Notice the two different index conditions:
> (l1.end > l2.start) AND (l1.start < l2.start) - "between"
> (l1.end > l2.start) AND (l1.start >= l2.start) - open-ended
> Both have a cost of (cost=0.00..123.10 rows=4809 width=12)
> Postgres estimates these two in
On Fri, 27 Mar 2009, Віталій Тимчишин wrote:
...an index on (objectid, start) would help...
Definitely.
You could try adding "AND l2.start > l1.start" to the first query.
This will drop symmetric half of intersections (the ones that will
remain are l2 inside or to the left of l1), but y
Hello.
You could try adding"AND l2.start > l1.start" to the first query. This
will drop symmetric half of intersections (the ones that will remain are l2
inside or to the left of l1), but you can redo results by
id1,id2 union all id2, id1 and may allow to use start index for "between",
for m
Matthew Wakeling writes:
> Is there an operator class for integer for gist indexes that I can use?
See contrib/btree_gist.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.po
On Thu, 26 Mar 2009, I wrote:
release-16.0-preview-14-mar=# \d location
Table "public.location"
Column | Type | Modifiers
-+-+---
end | integer |
start | integer |
objectid| integer |
id | integer | not
On Thu, 26 Mar 2009, Tom Lane wrote:
No, it doesn't. Have you thought about coding it in plpgsql?
*Looks* Nice.
So, it looks like I would be able to write a plpgsql function that returns
a table equivalent to the query I posted earlier. However, I'd like to
eat my cake *and* have it. My int
Matthew Wakeling writes:
> This query takes about two hours.
> Now, it happens that there is an algorithm for calculating overlaps which
> is really quick. It involves iterating through the table in order of the
> start variable and keeping a list of ranges which "haven't ended yet".
> When yo
Matthew Wakeling wrote:
> any other tips?
I would try adding an index on (objectid, start) and another on
(objectid, end) and see how that first query does. Also, if id is a
unique identifier, I'd recommend a unique constraint or (better) a
primary key definition. Check the system tables to s
So, I have an query that has a very great difference between the possible
performance and the achieved performance. I was wondering if there was a
possibility that Postgres would approach the possible performance by some
devious method.
The query returns a list of overlaps between objects. E
29 matches
Mail list logo