Skip site navigation (1) Skip section navigation (2)

Re: Speeding up a query.

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: "Hartman, Matthew" <Matthew(dot)Hartman(at)krcc(dot)on(dot)ca>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speeding up a query.
Date: 2009-06-17 13:08:33
Message-ID: b42b73150906170608p2f1cc545lf0ded4113b723b7a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Jun 16, 2009 at 2:35 PM, Hartman,
Matthew<Matthew(dot)Hartman(at)krcc(dot)on(dot)ca> wrote:
> Good afternoon.
>
> I have developed an application to efficiently schedule chemotherapy
> patients at our hospital. The application takes into account several
> resource constraints (available chairs, available nurses, nurse coverage
> assignment to chairs) as well as the chair time and nursing time
> required for a regimen.
>
> 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).
>
> To determine the available slots, the algorithm finds the earliest slot
> that has an available chair and a count of the required concurrent
> intervals afterwards. So a 60 minute regimen requires 12 concurrent
> rows. This is accomplished by joining the table on itself. A second
> query is ran for the same range, but with respect to the nurse time and
> an available nurse. Finally, those two are joined against each other.
> Effectively, it is:
>
> Select *
> From   (
>        Select *
>        From matrix m1, matrix m2
>        Where m1.xxxxx = m2.xxxxx
>        ) chair,
>        (
>        Select *
>        From matrix m1, matrix m2
>        Where m1.xxxxx = m2.xxxxx
>        ) nurse
> Where chair.id = nurse.id
>
> With matrix having 3,280 rows. Ugh.
>
> I have tried various indexes and clustering approachs with little
> success. Any ideas?

how far in advance do you schedule?  As far as necessary?

How many chairs are there?  How many nurses are there?   This is a
tricky (read: interesting) problem.

merlin

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-06-17 14:23:20
Subject: Re: Index Scan taking long time
Previous:From: Heikki LinnakangasDate: 2009-06-17 12:46:03
Subject: Re: GiST index performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group