Re: Slow query performance on large table

Поиск
Список
Период
Сортировка
От Andreas Pflug
Тема Re: Slow query performance on large table
Дата
Msg-id 3E64D694.9050309@web.de
обсуждение исходный текст
Ответ на Re: Slow query performance on large table  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Slow query performance on large table  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Tom Lane wrote:

>"Paul McKay" <paul_mckay@clearwater-it.co.uk> writes:
>
>
>>The query I am executing is
>>Select time,value
>From measurement
>>Where assessment = ?
>>And time between ? and ?
>>
>>
>
>EXPLAIN ANALYZE would help you investigate this.  Is it using an
>indexscan?  On which index?  Does forcing use of the other index
>(by temporarily dropping the preferred one) improve matters?
>
>Possibly a two-column index on both assessment and time would be
>an improvement, but it's hard to guess without knowing anything
>about the selectivity of the two WHERE clauses.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>
>

Tom,

does this mean that a primary key alone might not be enough? As far as I
understood Paul, the PK looks quite as the newly created index does, so
"create index ind_meas on measurement (assessment,time)"  should perform
the same as "... primary key(assessment,time)".
Do possibly non-optimal indices (only assessment, only time as Paul
described earlier) screw up the optimizer, igoring the better option
usiing the PK? Obviously, the index used should be combined of
(assessment,time) but IMHO a PK should be enough.

regards,

Andreas


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

Предыдущее
От: "Paul McKay"
Дата:
Сообщение: Re: Slow query performance on large table
Следующее
От: Robert Treat
Дата:
Сообщение: Re: Slow query performance on large table