Re: Speeding up a query.

On Tue, 2009-06-16 at 14:35 -0400, Hartman, Matthew wrote:

> The algorithm for packing appointments in respects each constraint and
> typically schedules a day of treatments (30-60) within 9-10 seconds on
> my workstation, down from 27 seconds initially. I would like to get it
> below 5 seconds if possible.
>
> I think what's slowing is down is simply the number of rows and joins.
> The algorithm creates a scheduling matrix with one row per 5 minute
> timeslot, per unit, per nurse assigned to the unit. That translates to
> 3,280 rows for the days I have designed in development (each day can
> change).

ISTM the efficiency of your algorithm is geometrically related to the
number of time slots into which appointments might fit. So reduce number
of possible time slots...

Assign the slot (randomly/hash/round robin) to either the morning or the
afternoon and then run exactly same queries just with half number of
time slots. That should reduce your execution time by one quarter
without using multiple CPUs for each morning/afternoon. Then run twice,
once for morning, once for afternoon.

You could parallelise this and run both at same time on different CPUs,
if the extra work is worthwhile, but it seems not, judging from your
requirements.

Another way would be to arrange all appointments that need odd number of
timeslots into pairs so that you have at most one appointment that needs
an odd number of timeslots. Then schedule appointments on 10 minute
boundaries, rounding up their timeslot requirement. (The single odd
timeslot appointment will always waste 1 timeslot).

Hope that helps.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support