pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql arrays
32+ messages / 8 participants
[nested] [flat]
* plpgsql arrays
@ 2009-04-03 13:32 Matthew Wakeling <matthew@flymine.org>
0 siblings, 3 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 13:32 UTC (permalink / raw)
To: pgsql-performance
I'm writing a plpgsql function that effectively does a merge join on the
results of two queries. Now, it appears that I cannot read the results of
two queries as streams in plpgsql, so I need to copy the contents of one
query into an array first, and then iterate over the second query
afterwards.
I have discovered that creating large arrays in plpgql is rather slow. In
fact, it seems to be O(n^2). The following code fragment is incredibly
slow:
genes = '{}';
next_new = 1;
FOR loc IN SELECT location.* FROM location, gene WHERE location.subjectid = gene.id ORDER BY objectid, intermine_start, intermine_end LOOP
genes[next_new] = loc;
IF (next_new % 10000 = 0) THEN
RAISE NOTICE 'Scanned % gene locations', next_new;
END IF;
next_new = next_new + 1;
END LOOP;
genes_size = coalesce(array_upper(genes, 1), 0);
RAISE NOTICE 'Scanned % gene locations', genes_size;
For 200,000 rows it takes 40 minutes.
So, is there a way to dump the results of a query into an array quickly in
plpgsql, or alternatively is there a way to read two results streams
simultaneously?
Matthew
--
I would like to think that in this day and age people would know better than
to open executables in an e-mail. I'd also like to be able to flap my arms
and fly to the moon. -- Tim Mullen
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 13:47 Robert Haas <robertmhaas@gmail.com>
parent: Matthew Wakeling <matthew@flymine.org>
2 siblings, 1 reply; 32+ messages in thread
From: Robert Haas @ 2009-04-03 13:47 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> I'm writing a plpgsql function that effectively does a merge join on the
> results of two queries. Now, it appears that I cannot read the results of
> two queries as streams in plpgsql, so I need to copy the contents of one
> query into an array first, and then iterate over the second query
> afterwards.
Why not just use SQL to do the join?
...Robert
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 13:57 Matthew Wakeling <matthew@flymine.org>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 13:57 UTC (permalink / raw)
To: pgsql-performance
On Fri, 3 Apr 2009, Robert Haas wrote:
> On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> wrote:
>> I'm writing a plpgsql function that effectively does a merge join on the
>> results of two queries.
> Why not just use SQL to do the join?
Because the merge condition is:
WHERE l1.start <= l2.end AND l2.start <= l1.end
and merge joins in postgres only currently cope with the case where the
merge condition is an equals relationship.
Oh, hang on, I think I saw something in the docs about what conditions can
be used in a merge...
Matthew
--
Let's say I go into a field and I hear "baa baa baa". Now, how do I work
out whether that was "baa" followed by "baa baa", or if it was "baa baa"
followed by "baa"?
- Computer Science Lecturer
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Wakeling <matthew@flymine.org>
0 siblings, 2 replies; 32+ messages in thread
From: Tom Lane @ 2009-04-03 14:04 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Robert Haas wrote:
>> Why not just use SQL to do the join?
> Because the merge condition is:
> WHERE l1.start <= l2.end AND l2.start <= l1.end
> and merge joins in postgres only currently cope with the case where the
> merge condition is an equals relationship.
> Oh, hang on, I think I saw something in the docs about what conditions can
> be used in a merge...
No, you got it right the first time. I was about to suggest that maybe
you could make it work by recasting the problem as equality on an
interval datatype, but the problem is that this is not equality but
"overlaps". And you can't cheat and call it equality, because it's
not transitive.
I don't actually believe that a standard merge join algorithm will work
with an intransitive join condition ...
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Wakeling <matthew@flymine.org>
2 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2009-04-03 14:08 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> I have discovered that creating large arrays in plpgql is rather slow. In
> fact, it seems to be O(n^2).
For variable-width element types, yeah. Don't go that way.
> ... alternatively is there a way to read two results streams
> simultaneously?
Use two cursors and FETCH from each as needed? In recent releases you
can even scroll backwards, which you're going to need to do to make
a merge join work.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:14 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 14:14 UTC (permalink / raw)
To: pgsql-performance
On Fri, 3 Apr 2009, Tom Lane wrote:
>> Oh, hang on, I think I saw something in the docs about what conditions can
>> be used in a merge...
>
> No, you got it right the first time. I was about to suggest that maybe
> you could make it work by recasting the problem as equality on an
> interval datatype, but the problem is that this is not equality but
> "overlaps". And you can't cheat and call it equality, because it's
> not transitive.
Well, according to
http://www.postgresql.org/docs/8.3/interactive/xoper-optimization.html#AEN41844
| So, both data types must be capable of being fully ordered, and the
| join operator must be one that can only succeed for pairs of values that
| fall at the "same place" in the sort order.
> I don't actually believe that a standard merge join algorithm will work
> with an intransitive join condition ...
A standard merge join should work absolutely fine, depending on how it's
implemented. If the implementation keeps a list of "current" right-hand
elements, and adds right-hand rows to the list when they compare "equal"
to the current left-hand element, and removes them from the list when they
compare "not equal" to the current left-hand element, then it would work
fine. If it does something else like rewinding the right-hand stream, or
throwing away the list when the current left-hand element is "not equal"
the previous left-hand element, (which would be fine for true equality)
then it will not work.
The description in the docs doesn't make it clear which way Postgres does
it.
Matthew
--
I have an inferiority complex. But it's not a very good one.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2009-04-03 14:24 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>> I don't actually believe that a standard merge join algorithm will work
>> with an intransitive join condition ...
> A standard merge join should work absolutely fine, depending on how it's
> implemented. If the implementation keeps a list of "current" right-hand
> elements, and adds right-hand rows to the list when they compare "equal"
> to the current left-hand element, and removes them from the list when they
> compare "not equal" to the current left-hand element, then it would work
> fine.
No, it would not. Not unless you have sorted the inputs in some way
that has more knowledge than the "equal" operator represents. Otherwise
you can have elements drop out that might still be needed to match to a
later left-hand element. Remember the point of the intransitivity
assumption: there can be a right-hand element X that is "equal" to two
left-hand elements Y and Z, but Y and Z are not "equal" to each other
and thus might not be kept adjacent in the left-hand sorting.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:28 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 14:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, 3 Apr 2009, Tom Lane wrote:
> Not unless you have sorted the inputs in some way that has more
> knowledge than the "equal" operator represents. Otherwise you can have
> elements drop out that might still be needed to match to a later
> left-hand element.
Of course. You certainly have to choose a sort order that works. Sorting
by the start field would be sufficient in this case.
Matthew
--
For those of you who are into writing programs that are as obscure and
complicated as possible, there are opportunities for... real fun here
-- Computer Science Lecturer
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:45 Matthew Wakeling <matthew@flymine.org>
parent: Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 14:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, 3 Apr 2009, Matthew Wakeling wrote:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>> Not unless you have sorted the inputs in some way that has more knowledge
>> than the "equal" operator represents. Otherwise you can have elements drop
>> out that might still be needed to match to a later left-hand element.
>
> Of course. You certainly have to choose a sort order that works. Sorting by
> the start field would be sufficient in this case.
Oh &^%")(!. That algorithm only finds the matches where l1.start >=
l2.start. Yeah, you're quite right.
Matthew
--
And why do I do it that way? Because I wish to remain sane. Um, actually,
maybe I should just say I don't want to be any worse than I already am.
- Computer Science Lecturer
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Wakeling <matthew@flymine.org>
1 sibling, 2 replies; 32+ messages in thread
From: Tom Lane @ 2009-04-03 14:48 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>> Not unless you have sorted the inputs in some way that has more
>> knowledge than the "equal" operator represents. Otherwise you can have
>> elements drop out that might still be needed to match to a later
>> left-hand element.
> Of course. You certainly have to choose a sort order that works. Sorting
> by the start field would be sufficient in this case.
Uh, no, it wouldn't. Visually:
L1 -------------------------
L2 -----------
L3 ---------------------
R1 --------
At L2, you'd conclude that you're done matching R1.
Intuitively, it seems like 1-D "overlaps" is a tractable enough
operator that you should be able to make something merge-like
work. But it's more complicated than I think you realize.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 14:58 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 14:58 UTC (permalink / raw)
To: pgsql-performance
On Fri, 3 Apr 2009, Tom Lane wrote:
> Intuitively, it seems like 1-D "overlaps" is a tractable enough
> operator that you should be able to make something merge-like
> work. But it's more complicated than I think you realize.
It's tractable when the two sides are symmetrical, but not so much when
they aren't. Our "no it isn't" messages obviously crossed on the wire.
Matthew
--
Some people, when confronted with a problem, think "I know, I'll use regular
expressions." Now they have two problems. -- Jamie Zawinski
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 15:02 Merlin Moncure <mmoncure@gmail.com>
parent: Matthew Wakeling <matthew@flymine.org>
2 siblings, 1 reply; 32+ messages in thread
From: Merlin Moncure @ 2009-04-03 15:02 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> genes = '{}';
> next_new = 1;
> FOR loc IN SELECT location.* FROM location, gene WHERE location.subjectid =
> gene.id ORDER BY objectid, intermine_start, intermine_end LOOP
> genes[next_new] = loc;
> IF (next_new % 10000 = 0) THEN
> RAISE NOTICE 'Scanned % gene locations', next_new;
> END IF;
> next_new = next_new + 1;
> END LOOP;
> genes_size = coalesce(array_upper(genes, 1), 0);
> RAISE NOTICE 'Scanned % gene locations', genes_size;
>
> For 200,000 rows it takes 40 minutes.
>
> So, is there a way to dump the results of a query into an array quickly in
> plpgsql, or alternatively is there a way to read two results streams
> simultaneously?
try this:
select array(SELECT location.* FROM location, gene WHERE
location.subjectid = gene.id ORDER BY objectid, intermine_start,
intermine_end)) into genes;
merlin
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 15:09 Merlin Moncure <mmoncure@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Merlin Moncure @ 2009-04-03 15:09 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Fri, Apr 3, 2009 at 11:02 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> wrote:
>> genes = '{}';
>> next_new = 1;
>> FOR loc IN SELECT location.* FROM location, gene WHERE location.subjectid =
>> gene.id ORDER BY objectid, intermine_start, intermine_end LOOP
>> genes[next_new] = loc;
>> IF (next_new % 10000 = 0) THEN
>> RAISE NOTICE 'Scanned % gene locations', next_new;
>> END IF;
>> next_new = next_new + 1;
>> END LOOP;
>> genes_size = coalesce(array_upper(genes, 1), 0);
>> RAISE NOTICE 'Scanned % gene locations', genes_size;
>>
>> For 200,000 rows it takes 40 minutes.
>>
>> So, is there a way to dump the results of a query into an array quickly in
>> plpgsql, or alternatively is there a way to read two results streams
>> simultaneously?
>
> try this:
> select array(SELECT location.* FROM location, gene WHERE
> location.subjectid = gene.id ORDER BY objectid, intermine_start,
> intermine_end)) into genes;
one more time:
select array(SELECT location FROM location, gene WHERE
location.subjectid = gene.id ORDER BY objectid, intermine_start,
intermine_end)) into genes;
this will make array of location records. when you access the records
to do the merge, make sure to use () noation:
if (genes[x]).field > something then
...
merlin
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 15:15 Matthew Wakeling <matthew@flymine.org>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-03 15:15 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
On Fri, 3 Apr 2009, Merlin Moncure wrote:
> select array(SELECT location FROM location, gene WHERE
> location.subjectid = gene.id ORDER BY objectid, intermine_start,
> intermine_end)) into genes;
Yeah, that works nicely.
> this will make array of location records. when you access the records
> to do the merge, make sure to use () noation:
>
> if (genes[x]).field > something then
How is that different to genes[x].field?
Matthew
--
And the lexer will say "Oh look, there's a null string. Oooh, there's
another. And another.", and will fall over spectacularly when it realises
there are actually rather a lot.
- Computer Science Lecturer (edited)
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 15:18 Merlin Moncure <mmoncure@gmail.com>
parent: Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 32+ messages in thread
From: Merlin Moncure @ 2009-04-03 15:18 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Fri, Apr 3, 2009 at 11:15 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Merlin Moncure wrote:
>>
>> select array(SELECT location FROM location, gene WHERE
>> location.subjectid = gene.id ORDER BY objectid, intermine_start,
>> intermine_end)) into genes;
>
> Yeah, that works nicely.
>
>> this will make array of location records. when you access the records
>> to do the merge, make sure to use () noation:
>>
>> if (genes[x]).field > something then
>
> How is that different to genes[x].field?
ah, it isn't...in many cases where you access composite type fields,
() is required (especially in query). it isn't here, so you can
safely leave it off.
merlin
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 17:30 Simon Riggs <simon@2ndQuadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 32+ messages in thread
From: Simon Riggs @ 2009-04-03 17:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
> Matthew Wakeling <matthew@flymine.org> writes:
> > On Fri, 3 Apr 2009, Robert Haas wrote:
> >> Why not just use SQL to do the join?
>
> > Because the merge condition is:
>
> > WHERE l1.start <= l2.end AND l2.start <= l1.end
>
> > and merge joins in postgres only currently cope with the case where the
> > merge condition is an equals relationship.
(snip)
> I don't actually believe that a standard merge join algorithm will work
> with an intransitive join condition ...
I think it's a common enough problem that having a non-standard join
algorithm written for that case would be interesting indeed.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 17:33 Alvaro Herrera <alvherre@commandprompt.com>
parent: Simon Riggs <simon@2ndQuadrant.com>
2 siblings, 0 replies; 32+ messages in thread
From: Alvaro Herrera @ 2009-04-03 17:33 UTC (permalink / raw)
To: Simon Riggs <simon@2ndQuadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Simon Riggs wrote:
>
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
> > I don't actually believe that a standard merge join algorithm will work
> > with an intransitive join condition ...
>
> I think it's a common enough problem that having a non-standard join
> algorithm written for that case would be interesting indeed.
+42
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 17:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndQuadrant.com>
2 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2009-04-03 17:38 UTC (permalink / raw)
To: Simon Riggs <simon@2ndQuadrant.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>> I don't actually believe that a standard merge join algorithm will work
>> with an intransitive join condition ...
> I think it's a common enough problem that having a non-standard join
> algorithm written for that case would be interesting indeed.
Sounds like a great PhD thesis topic.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-03 19:03 Nathan Boley <npboley@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 32+ messages in thread
From: Nathan Boley @ 2009-04-03 19:03 UTC (permalink / raw)
To: pgsql-performance
> Uh, no, it wouldn't. Visually:
>
> L1 -------------------------
> L2 -----------
> L3 ---------------------
>
> R1 --------
>
> At L2, you'd conclude that you're done matching R1.
>
No, you should conclude that you're done matching L2. You conclude
you're done matching R1 when you reach L4 ( or there exists a j st
Lj.start > R1.end, or equivalently Lj is strictly greater than R1 )
FWIW, this is a very common problem in bioinformatics. I've mostly
implemented this in python and C. The code is available at
encodestatistics.org. Look in encode.py at the overlap method of the
feature_region class, or ( for the C version ) region_overlap in
block_bootstrap.c ( svn is more up to date for the C ).
-Nathan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-06 11:47 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-06 11:47 UTC (permalink / raw)
To: pgsql-performance
On Fri, 3 Apr 2009, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>>> I don't actually believe that a standard merge join algorithm will work
>>> with an intransitive join condition ...
>
>> I think it's a common enough problem that having a non-standard join
>> algorithm written for that case would be interesting indeed.
>
> Sounds like a great PhD thesis topic.
I agree it'd be very cool to have a non-standard join algorithm for this
built into Postgres. However it is nowhere near complicated enough for a
PhD thesis topic.
I'm just putting the finishing touches on a plpgsql implementation - in
order to perform the join on a asymmetric set of ranges, you just need to
keep two separate history lists as you sweep through the two incoming
streams. This would be sufficient for range constraints.
Matthew
--
Surely the value of C++ is zero, but C's value is now 1?
-- map36, commenting on the "No, C++ isn't equal to D. 'C' is undeclared
[...] C++ should really be called 1" response to "C++ -- shouldn't it
be called D?"
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-06 12:52 Matthew Wakeling <matthew@flymine.org>
parent: Simon Riggs <simon@2ndQuadrant.com>
2 siblings, 1 reply; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-06 12:52 UTC (permalink / raw)
To: pgsql-performance
On Fri, 3 Apr 2009, Simon Riggs wrote:
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>> Matthew Wakeling <matthew@flymine.org> writes:
>>> On Fri, 3 Apr 2009, Robert Haas wrote:
>>>> Why not just use SQL to do the join?
>>>
>>> Because the merge condition is:
>>>
>>> WHERE l1.start <= l2.end AND l2.start <= l1.end
>>>
>>> and merge joins in postgres only currently cope with the case where the
>>> merge condition is an equals relationship.
>>
>> I don't actually believe that a standard merge join algorithm will work
>> with an intransitive join condition ...
>
> I think it's a common enough problem that having a non-standard join
> algorithm written for that case would be interesting indeed.
I'm currently trying to persuade my boss to give me time to do some work
to implement this in Postgres. It's not something I will be able to start
right away, but maybe in a little while.
I'm currently seeing this as being able to mark overlap constraints ("&&"
in quite a few data types) as "OVERLAP_MERGES", and have the planner be
able to use the new merge join algorithm. So it wouldn't help with the
exact query above, but would if I rewrote it to use the bioseg or spacial
data types' overlap operators.
I will need a little help as I am not incredibly familiar with the
Postgres innards. Would someone be able to do that?
Matthew
--
Existence is a convenient concept to designate all of the files that an
executable program can potentially process. -- Fortran77 standard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-06 14:36 Robert Haas <robertmhaas@gmail.com>
parent: Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 32+ messages in thread
From: Robert Haas @ 2009-04-06 14:36 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Mon, Apr 6, 2009 at 8:52 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Simon Riggs wrote:
>>
>> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>>>
>>> Matthew Wakeling <matthew@flymine.org> writes:
>>>>
>>>> On Fri, 3 Apr 2009, Robert Haas wrote:
>>>>>
>>>>> Why not just use SQL to do the join?
>>>>
>>>> Because the merge condition is:
>>>>
>>>> WHERE l1.start <= l2.end AND l2.start <= l1.end
>>>>
>>>> and merge joins in postgres only currently cope with the case where the
>>>> merge condition is an equals relationship.
>>>
>>> I don't actually believe that a standard merge join algorithm will work
>>> with an intransitive join condition ...
>>
>> I think it's a common enough problem that having a non-standard join
>> algorithm written for that case would be interesting indeed.
>
> I'm currently trying to persuade my boss to give me time to do some work to
> implement this in Postgres. It's not something I will be able to start right
> away, but maybe in a little while.
>
> I'm currently seeing this as being able to mark overlap constraints ("&&" in
> quite a few data types) as "OVERLAP_MERGES", and have the planner be able to
> use the new merge join algorithm. So it wouldn't help with the exact query
> above, but would if I rewrote it to use the bioseg or spacial data types'
> overlap operators.
>
> I will need a little help as I am not incredibly familiar with the Postgres
> innards. Would someone be able to do that?
I can help review if you post a patch, even if it's WIP. But you
should post it to -hackers, not here.
...Robert
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 15:18 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-07 15:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, 3 Apr 2009, Tom Lane wrote:
>> ... alternatively is there a way to read two results streams
>> simultaneously?
>
> Use two cursors and FETCH from each as needed? In recent releases you
> can even scroll backwards, which you're going to need to do to make
> a merge join work.
What would be the syntax for putting a single row from a cursor into a
variable? I have tried:
FETCH INTO left left_cursor;
which says syntax error, and
left = FETCH left_cursor;
which gives the error 'ERROR: missing datatype declaration at or near "="'
Matthew
--
I've run DOOM more in the last few days than I have the last few
months. I just love debugging ;-) -- Linus Torvalds
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 15:23 justin <justin@emproshunts.com>
parent: Matthew Wakeling <matthew@flymine.org>
1 sibling, 1 reply; 32+ messages in thread
From: justin @ 2009-04-07 15:23 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Matthew Wakeling wrote:
> What would be the syntax for putting a single row from a cursor into a
> variable? I have tried:
>
> FETCH INTO left left_cursor;
>
> which says syntax error, and
>
> left = FETCH left_cursor;
>
> which gives the error 'ERROR: missing datatype declaration at or near
> "="'
>
> Matthew
>
Have to declare Left variable as record data type declaration part of
the function
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 15:33 Matthew Wakeling <matthew@flymine.org>
parent: justin <justin@emproshunts.com>
0 siblings, 2 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-07 15:33 UTC (permalink / raw)
To: justin <justin@emproshunts.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, 7 Apr 2009, justin wrote:
>> What would be the syntax for putting a single row from a cursor into a
>> variable? I have tried:
>>
>> FETCH INTO left left_cursor;
>>
>> which says syntax error, and
>>
>> left = FETCH left_cursor;
>>
>> which gives the error 'ERROR: missing datatype declaration at or near "="'
>
> Have to declare Left variable as record data type declaration part of the
> function
It is.
CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$
DECLARE
left location;
retval RECORD;
BEGIN
DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end;
left = FETCH left_cursor;
END;
$$ LANGUAGE plpgsql;
Matthew
--
"Prove to thyself that all circuits that radiateth and upon which thou worketh
are grounded, lest they lift thee to high-frequency potential and cause thee
to radiate also. " -- The Ten Commandments of Electronics
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 15:45 justin <justin@emproshunts.com>
parent: Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 32+ messages in thread
From: justin @ 2009-04-07 15:45 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Matthew Wakeling wrote:
<blockquote
cite="mid:alpine.DEB.2.00.0904071630420.791@aragorn.flymine.org"
type="cite">On Tue, 7 Apr 2009, justin wrote:
<br>
<blockquote type="cite">
<blockquote type="cite">What would be the syntax for putting a
single row from a cursor into a variable? I have tried:
<br>
<br>
FETCH INTO left left_cursor;
<br>
<br>
which says syntax error, and
<br>
<br>
left = FETCH left_cursor;
<br>
<br>
which gives the error 'ERROR: missing datatype declaration at or near
"="'
<br>
</blockquote>
<br>
Have to declare Left variable as record data type declaration part of
the function
<br>
</blockquote>
<br>
It is.
<br>
<br>
CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD
AS $$
<br>
DECLARE
<br>
left location;
<br>
retval RECORD;
<br>
BEGIN
<br>
DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM
location, gene WHERE location.id = gene.id ORDER BY objectid, start,
end;
<br>
left = FETCH left_cursor;
<br>
END;
<br>
$$ LANGUAGE plpgsql;
<br>
<br>
Matthew
<br>
</blockquote>
<br>
Change the type to Record<br>
from the help file<br>
<code class="command">FETCH</code> retrieves the next row from the
cursor into a target, which might be a <b>row variable, a record
variable, or a comma-separated </b>list of simple variables, just like
<code class="command">SELECT INTO</code>. If there is no next row, the
target is set to NULL(s). As with <code class="command">SELECT INTO</code>,
the special variable <code class="literal">FOUND</code> can be checked
to see whether a row was obtained or not<br>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 16:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Wakeling <matthew@flymine.org>
1 sibling, 1 reply; 32+ messages in thread
From: Tom Lane @ 2009-04-07 16:08 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: justin <justin@emproshunts.com>; pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$
> DECLARE
> left location;
> retval RECORD;
> BEGIN
> DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end;
> left = FETCH left_cursor;
> END;
> $$ LANGUAGE plpgsql;
Well, the DECLARE for the cursor should go in the DECLARE section,
and the syntax for the FETCH should be
FETCH cursorname INTO recordvariablename;
and I'm too lazy to check right now but I think you might be missing
an OPEN for the cursor.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 16:22 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-07 16:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: justin <justin@emproshunts.com>; pgsql-performance
On Tue, 7 Apr 2009, Tom Lane wrote:
> Matthew Wakeling <matthew@flymine.org> writes:
>> CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$
>> DECLARE
>> left location;
>> retval RECORD;
>> BEGIN
>> DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end;
>> left = FETCH left_cursor;
>> END;
>> $$ LANGUAGE plpgsql;
>
> Well, the DECLARE for the cursor should go in the DECLARE section,
> and the syntax for the FETCH should be
> FETCH cursorname INTO recordvariablename;
> and I'm too lazy to check right now but I think you might be missing
> an OPEN for the cursor.
Yeah, thanks to Justin I found the plpgsql docs for cursors. The main
cursors docs should really link there.
This seems to do what I want:
CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS
$$
DECLARE
left_cursor NO SCROLL CURSOR FOR SELECT location.* FROM location, gene WHERE location.subjectid = gene.id ORDER BY objectid, start, end;
left location;
BEGIN
OPEN left_cursor;
FETCH left_cursor INTO left;
END;
$$ LANGUAGE plpgsql;
Matthew
--
Lord grant me patience, and I want it NOW!
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 19:24 Merlin Moncure <mmoncure@gmail.com>
parent: Matthew Wakeling <matthew@flymine.org>
1 sibling, 1 reply; 32+ messages in thread
From: Merlin Moncure @ 2009-04-07 19:24 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>>>
>>> ... alternatively is there a way to read two results streams
>>> simultaneously?
>>
>> Use two cursors and FETCH from each as needed? In recent releases you
>> can even scroll backwards, which you're going to need to do to make
>> a merge join work.
>
> What would be the syntax for putting a single row from a cursor into a
> variable? I have tried:
>
> FETCH INTO left left_cursor;
according to the docs,
Examples:
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING
merlin
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-07 19:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2009-04-07 19:31 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Merlin Moncure <mmoncure@gmail.com> writes:
> On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling <matthew@flymine.org> wrote:
>> What would be the syntax for putting a single row from a cursor into a
>> variable? I have tried:
>>
>> FETCH INTO left left_cursor;
> according to the docs,
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING
Subsequent discussion showed that the problem was Matthew hadn't found
that page. I guess that at least the DECLARE CURSOR reference page
ought to have something like "if you are trying to use cursors in
plpgsql, see <link>". Matthew, where *were* you looking exactly?
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-08 10:35 Matthew Wakeling <matthew@flymine.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 32+ messages in thread
From: Matthew Wakeling @ 2009-04-08 10:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, 7 Apr 2009, Tom Lane wrote:
> Subsequent discussion showed that the problem was Matthew hadn't found
> that page. I guess that at least the DECLARE CURSOR reference page
> ought to have something like "if you are trying to use cursors in
> plpgsql, see <link>". Matthew, where *were* you looking exactly?
The DECLARE CURSOR page, and then guessing the INTO bit because that's how
SELECT works.
Matthew
--
for a in past present future; do
for b in clients employers associates relatives neighbours pets; do
echo "The opinions here in no way reflect the opinions of my $a $b."
done; done
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: plpgsql arrays
@ 2009-04-10 17:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 32+ messages in thread
From: Tom Lane @ 2009-04-10 17:57 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> On Tue, 7 Apr 2009, Tom Lane wrote:
>> Subsequent discussion showed that the problem was Matthew hadn't found
>> that page. I guess that at least the DECLARE CURSOR reference page
>> ought to have something like "if you are trying to use cursors in
>> plpgsql, see <link>". Matthew, where *were* you looking exactly?
> The DECLARE CURSOR page, and then guessing the INTO bit because that's how
> SELECT works.
I've added cross-references in the DECLARE and FETCH pages. I hope
that's sufficient to catch the attention of anyone trying to use cursors
...
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
end of thread, other threads:[~2009-04-10 17:57 UTC | newest]
Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-03 13:32 plpgsql arrays Matthew Wakeling <matthew@flymine.org>
2009-04-03 13:47 ` Robert Haas <robertmhaas@gmail.com>
2009-04-03 13:57 ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 14:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-03 14:14 ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-03 14:28 ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 14:45 ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 14:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-03 14:58 ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 19:03 ` Nathan Boley <npboley@gmail.com>
2009-04-03 17:30 ` Simon Riggs <simon@2ndQuadrant.com>
2009-04-03 17:33 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-04-03 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-06 11:47 ` Matthew Wakeling <matthew@flymine.org>
2009-04-06 12:52 ` Matthew Wakeling <matthew@flymine.org>
2009-04-06 14:36 ` Robert Haas <robertmhaas@gmail.com>
2009-04-03 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-07 15:18 ` Matthew Wakeling <matthew@flymine.org>
2009-04-07 15:23 ` justin <justin@emproshunts.com>
2009-04-07 15:33 ` Matthew Wakeling <matthew@flymine.org>
2009-04-07 15:45 ` justin <justin@emproshunts.com>
2009-04-07 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-07 16:22 ` Matthew Wakeling <matthew@flymine.org>
2009-04-07 19:24 ` Merlin Moncure <mmoncure@gmail.com>
2009-04-07 19:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-08 10:35 ` Matthew Wakeling <matthew@flymine.org>
2009-04-10 17:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-03 15:02 ` Merlin Moncure <mmoncure@gmail.com>
2009-04-03 15:09 ` Merlin Moncure <mmoncure@gmail.com>
2009-04-03 15:15 ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 15:18 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox