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
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 for a
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
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
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
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,
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
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
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?
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
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
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 leaf of
a
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
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 the
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
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,
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
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 the first
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
Matthew Wakeling matt...@flymine.org 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
Hello.
You could try addingAND 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 my
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 you
Matthew Wakeling matt...@flymine.org 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
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)
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.
Matthew Wakeling matt...@flymine.org 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
Matthew Wakeling matt...@flymine.org 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
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
28 matches
Mail list logo
