Не как не пойму правильный механизм работы с индексами. Вобщем суть:
Есть некая таблица с кучей полей, одно из которых vg_id - numeric(10,0) >= 0
Создал btree (hash тоже пробовал) индекс по этому полю. Записей в таблице порядка 5 000 000 (для теста, в реальных условиях планирутся примерно 100 лямов).
Простой запрос select count(*) from tablename where vg_id = 123 выполняется порядка 8 сек.

Объясните как этого избежать. Для простого подсчёта количества записей, как я понял, он использует не только индекс, но и заким то фигом сканирует саму таблицу (чего логично предположить делать смысла нет, я же просто хочу знать сколько записей удовлетворяет запросу, а не данные записи выбирать собираюсь). Я вообщее всю жизнь с mysql работал, а тут вот пришлось с postgresql повозиться, вожусь всего неделю, так что прошу не пиннать сильно :)

Т.е. надо правильно понимать что это особенность PostgreSQL такая, что он не может вычислить количество строк используя один лишь индекс и не сканируя при это сами данные (например найдя в btree индексе текущую позицию, получить в метаданных адрес следующей отличающующейся позиции вычесть адрес текущей и поделить на размер записи получив их количество)? И что "ждать чуда" надо в следующей версии (у мну 8.3.3.)?
Или всётаки есть какие то способы разрешения ситуации иными способами. Вообще кто как решает задачу когда нужно получить просто количество записей удовлетворяющих условию, чтоб например построить тот же список страниц например... Или например задача когда у меня в табличке 90 лямов записей, мне нужно получить сумму по некому полю "А" у которых поле vg_id = 123 при всём при том что записей у которых vg_id = 123 менее 0.1% (90к) он это делает аж:

Или может подскажете, возможно есть некая возможность отсортировать исходные данные? Т.е. например в mysql у меня была возможность сделать нечто такое:
в 3 часа утра я выполнял следующий запрос:
ALTER TABLE tablename ORDER BY vg_id; Это приводило к тому что таблица пересоздавалась на hdd отсортированная по полю vg_id
В результате это приводило к тому что все записи находились в исходниках на хдд группами по vg_id. Т.к. все запросы в условиях всегда содержали vg_id + наличие индекса, резко ускоряло работу, т.к. бд скажем весит 15 гиг, но записи с vg_id скажем 123 это всего навсего 0.1% от всех записей, т.е. 15мб. В результате, используя индекс, бд точнчо знала что записи с vg_id =123 на диске находятся только с N по N+15 гиг адреса в файле данных, и читала только часть этого файла, при чём не прерывную часть из 15 мб на диске в которых были записи с vg_id = 123 не читая остальных 14 гигов данных. Возможно ли как то тут реализовать подобное? Или придётся делать таблицу "NEW" которая будет унаследована от родительской и в которую вставлять новые данные, а потом в 3 утра делать INSERT INTO parent (SELECT * FROM child ORDER BY vg_id); после чего TRUNCATE TABLE child;
??
Вообще какие есть методы работы с большими объёмами данных?

eddie
медленный count - это особенность postgresql, с ней можно только смириться.

если запросы на count одни и те же - можно завести таблицу с заранее подсчитанными значениями и модифицировать её триггерами при добавлении/удалении записей.

Ыхх :(
Жаль, ну да ладно, это я ещё могу обойти, хотябы тем же триггером и отдельной таблицей, к сожалению это не единственная проблема.
Я там второй раз выше не правильный запрос привёл, не то скопипастил, изначально планировалось:
EXPLAIN ANALYZE SELECT sum(in) FROM tablename where vg_id = 123; Но результат вобщем то получается тот же (173185.578 ms).
К сожалению полей которые будут участвовать в подсчёте несколько и вариантов как самих считаемых полей, так и дополнительных условий к vg_id будет тоже много разных, так что триггер при инсерт и апдейт тут не поможет. :(

Но у меня "родилась" ещё одна интересная идея, не знаю как оно поведёт себя на практике, пока не экспериментировал, попробую на выходных, а то и так уже дыру в винте протёр экспериментами, но суть её заключается в следующем:
В таблице порядка 16 полей (некоторые переменной длины!), но условия выборки всегда будут затрагивать только 4 из них. Остальные будут "выбираться/считаться" и т.д. А ещё как известно первичный ключ в любой бд работает очень быстро. Следовательно разделив таблицу на 2, в одной 4 поля по которым будет происходить выборка + ключевое поле, а во второй ключевое поле для связи с первой и все остальные "большие" поля. Тем самым уменьшу размер читаемых базой файлов по которым происходит логическая выборка, и через left join соединю их в запросе. В результате запрос будет в условии по первой табличке в которой запись будет фиксированной длины, и которая будет сравнительно маленькая, после чего имея только нужные точные уникальные ID по уникальному ключу она при обработке LEFT JOIN вытащит данные из второй таблицы. Что должно в теории работать очень быстро. Особенно если это связать с предложенной KRED'ом кластеризацией. И сегментированием:

где-то-в-инетеСегментирование данных можно организовать с помощью наследования (TABLE INHERITANCE) и CE (CONSTRAINT EXCLUSION). Идея состоит в том, чтобы иметь родительскую таблицу (класс), которая определяет основной набор атрибутов и таблицы, которые наследуют структуру родительской таблицы, но имеющие определенные ограничения на параметр, по которому проводится сегментирование. Механизм наследования в PostgreSQL обеспечивает выполнение запроса по всем таблицам автоматически, при этом наличие CE позволяет просматривать только те таблицы, которые удовлетворяют условию на параметр. Типичная ситуация, когда сегментирование идет по времени, например, для хранение журналов веб-серверов.

---------- :)

KRED
посмотрите в сторону "cluster table...."

http://www.postgresql.org/docs/8.3/static/sql-cluster.html

Удачи.

Спасибо, очень помогло :) Время выполнения основных запросов упало до менее 1 сек.
Вот только есть одно "но", по той ссылке в подзаголовке "Note" написано что эта кластеризация может очень долго выполняться на больших таблицах, а также если делать её в "вакууме" что планёр может не оптимально это делать, и что порой лучше бывает делать её самому путём "INSERT FROM SELECT" с сортировкой по нужному полю. Если есть у кого опыт в данном вопросе очень хотелось бы выслушать опытных людей. А также я пока не нашёл можно ли каким нибудь образом, не применяя полного копирования данных во временные таблицы, сделать кластеризацию не по индексу в текущей таблице, а скажем у меня 2 связанные таблички по ключам, используя индекс во второй по какому то полю отсортировать данные в них обоих.

KRED
Как вариант предложу прикрутит сюда ещо и партиционирование ... новые данные заливаеш в новые партиции после чего их "сластеризируеш" , а старые лежат на соседних партициях и не меняются. Это конечно если ты данные в основном добавляеш и не изменяеш их рандомно ... , но как мне кажется ты же какето "логи" анализируеш.

Хотя если ты ещо и один из основных поисковых критериев разделиш по партициям то скорость ещо немного увеличится.

http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html

Да, вы правы это в каком то смысле "логи", изменяться они правда тоже будут, но будет это один раз в жизни, в течении первых суток, за которым уже можно сделать кластеризацию и партиционирование. На данный момент пока остановился на том что буду сначала складывать всё новое в таблицу "new" (которая тоже будет частью партиции) откуда уже после обработки данные переносить по партициям, которые уже не будут меняться, отсортированные внутри партиции по vg_id, и разделённые по датам (к сожалению по основному критерию vg_id разделить не получится, это хоть и int идентификатор, но "предсказать" его нельзя даже по диапазону, т.к. одних может быть очень много). Пока хочу поэкспериментировать, найти т.н. "золотую середину" в размерах этих частей по дате. Правда про партиционирование я уже читал, только там где я об этом читал, это называли сегментированием, о чём выше я уже писал, но сути это не меняет, всё равно спасибо за совет :)

*** но "предсказать" его нельзя даже по диапазону, т.к. одних может быть очень много, а других очень мало).***

P.S. чёта я не найду кнопки редактирования :(