Re: Optimize date query for large child tables: GiST or GIN?

Поиск
Список
Период
Сортировка
От David Jarvis
Тема Re: Optimize date query for large child tables: GiST or GIN?
Дата
Msg-id AANLkTik288XsL9ie_N7QwGHjYiQ3xGY4iTNtucfsCe3v@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Optimize date query for large child tables: GiST or GIN?  (Alvaro Herrera <alvherre@commandprompt.com>)
Список pgsql-performance
Дерево обсуждения
Hi,

Hmm, that's nice, though I cannot but wonder whether the exclusive lock
required by CLUSTER is going to be a problem in the long run.

Not an issue; the inserts are one-time (or very rare; at most: once a year).
 
Hm, keep in mind that if the station clause alone is not selective
enough, scanning it may be too expensive.  The current three column

The seven child tables (split on category ID) have the following indexes:
  1. Primary key (unique ID, sequence)
  2. Station ID (table data is physically inserted by station ID order)
  3. Station ID, Date, and Category ID (this index is CLUSTER'ed)
I agree that the last index is probably all that is necessary. 99% of the searches use the station ID, date, and category. I don't think PostgreSQL necessarily uses that last index, though.

Dave

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

Предыдущее
От: David Jarvis
Дата:
Сообщение: Re: Optimize date query for large child tables: GiST or GIN?
Следующее
От: Tom Wilcox
Дата:
Сообщение: Re: requested shared memory size overflows size_t