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")
Ответы: Re: Speeding up a query.  (Grzegorz Jaśkiewicz)
Список: pgsql-performance

Скрыть дерево обсуждения

performance with query  (Alberto Dalmaso, )
 Re: performance with query  (Joshua Tolley, )
 Re: performance with query  ("Kevin Grittner", )
 Re: performance with query  (Alberto Dalmaso, )
  Re: performance with query  (Matthew Wakeling, )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  (Tom Lane, )
     Re: performance with query  (Alberto Dalmaso, )
      Re: performance with query  (Tom Lane, )
      Re: performance with query (OT)  ("Albe Laurenz", )
    Re: performance with query  ("Kevin Grittner", )
  Re: performance with query  ("Kevin Grittner", )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  ("Kevin Grittner", )
    Re: performance with query  ("Kevin Grittner", )
     Re: performance with query  (Alberto Dalmaso, )
      Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  (Anthony Presley, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  ("Albe Laurenz", )
        Re: Speeding up a query.  (Grzegorz Jaśkiewicz, )
       Re: Speeding up a query.  (Alberto Dalmaso, )
        Re: Speeding up a query.  ("Kevin Grittner", )
        Re: Speeding up a query.  (Tom Lane, )
       Re: Speeding up a query.  (Merlin Moncure, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  (Simon Riggs, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
 Re: performance with query  (Alberto Dalmaso, )
 Re: performance with query  (Alberto Dalmaso, )
  Re: performance with query  ("Kevin Grittner", )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  ("Kevin Grittner", )
     Re: performance with query  (Alberto Dalmaso, )
      Re: performance with query  ("Kevin Grittner", )
       Re: performance with query  (Tom Lane, )
        Re: performance with query  ("Kevin Grittner", )

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 по дате сообщения:

От: Merlin Moncure
Дата:
Сообщение: Re: Speeding up a query.
От: Tom Lane
Дата:
Сообщение: Re: Index Scan taking long time