Re: Speeding up a query.

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: Speeding up a query.
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C202FF6652@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответ на Speeding up a query.  ("Hartman, Matthew" <Matthew.Hartman@krcc.on.ca>)
Ответы Re: Speeding up a query.  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Список pgsql-performance
Matthew Hartman wrote:
> 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?

I don't understand your data model well enough to understand
the query, so I can only give you general hints (which you probably
already know):

- Frequently the biggest performance gains can be reached by
  a (painful) redesign. Can ou change the table structure in a way
  that makes this query less expensive?

- You have an index on matrix.xxxxx, right?

- Can you reduce the row count of the two subqueries by adding
  additional conditions that weed out rows that can be excluded
  right away?

- Maybe you can gain a little by changing the "select *" to
  "select id" in both subqueries and adding an additional join
  with matrix that adds the relevant columns in the end.
  I don't know the executor, so I don't know if that will help,
  but it would be a simple thing to test in an experiment.

Yours,
Laurenz Albe

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Albe Laurenz"
Дата:
Сообщение: Re: performance with query (OT)
Следующее
От: Stefan Kaltenbrunner
Дата:
Сообщение: Re: 8.4 COPY performance regression on Solaris