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 much fewer rows than
an open-ended index scan.
BTW: About aggregates: they can return arrays, but I can't imagine what you can
group by on... May be windowing functions from 8.4
could help.
A normal function seems the best way to go about this - they can return
multiple rows.
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. I
am considering recoding it as a C function instead.
1. The docs say that returning multiple rows from plpgsql waits until the
whole lot are done before returning any. Does this happen with the C
functions too?
2. What sort of speedup would I be likely to see?
3. How do I RAISE NOTICE in a C function?
Also, if your maximum length (select max(end-start) from location) is low
enough, you can try adding some more constraints to make
optimizer happy (have it more precise row count to select correct plan).
Alas:
select min(start), max(start), min(end), max(end), max(end - start) from
location;
min | max | min | max | max
-----+----------+-----+----------+----------
1 | 61544858 | 1 | 61545105 | 21512431
(1 row)
Matthew
--
I suppose some of you have done a Continuous Maths course. Yes? Continuous
Maths? <menacing stares from audience> Whoah, it was like that, was it!
-- Computer Science Lecturer
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
