ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация index scan
16 сообщений из 16, страница 1 из 1
Оптимизация index scan
    #33776522
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
Есть таблица (биллинг) в 280 млн. записей размером около 18 гигабайт (это на Firebird - на PostgreSQL было бы гигабайт на несколько больше). Создан индекс по дате-времени. Остальные поля сильно неуникальны и создавать по ним индекс нецелесообразно.
Есть настоятельная потребность выбирать большое количество записей (от 400 тыс. (часто) до 10 млн. (оч. редко)) по некоторым условиям, основным из которых является дата-время.
В Firerbird для таких страдальцев как я есть оптимизация индексного скана, которая скан по индексу реализует как последовательный скан страниц, на которых могут находится записи, удовлетворяющие условию поиска. Сказать, что это работает, значит ничего не сказать. Разница даже с "супер-пупер быстрым" MySQL на MyISAM оказалась 3 раза в пользу Firebird.
Есть ли какие-нибудь трюки на PostgreSQL, которые могли бы облегчить мне жизнь? В настоящий момент индексный скан по 2.5 миллионам записей на FB 1.5.3 и PG 8.1.1 занимает соответственно 21 и 150 сек. И что мне делать? Сидеть на FB и не дёргаться?
...
Рейтинг: 0 / 0
07.06.2006, 08:52
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33776724
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
автор(это на Firebird - на PostgreSQL было бы гигабайт на несколько больше).
авторВ настоящий момент индексный скан по 2.5 миллионам записей на FB 1.5.3 и PG 8.1.1 занимает соответственно 21 и 150 сек.

Я не пойму - тестил ли ты на тех же данных с той же структурой те же запросы или нет.
Кроме того не забудь перед первым запуском (после заливки данных) сделать analyze для всей базы - разница может быть колосальна.
...
Рейтинг: 0 / 0
07.06.2006, 10:21
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33776798
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Andrew SagulinЕсть настоятельная потребность выбирать большое количество записей (от 400 тыс. (часто) до 10 млн. (оч. редко))Передавать результирующие 400 тыс строк клиенту? Тогда возможно затраты на передачу данных будут больше, чем на сканирование индекса. Или использовать эти 400 тыс строк для последующих операций, например вычисления агрегатов, и отдавать клиенту мало строк результата?

Andrew Sagulin... скан по индексу реализует как последовательный скан страниц, на которых могут находится записи, удовлетворяющие условию поиска... Есть ли какие-нибудь трюки на PostgreSQL, которые могли бы облегчить мне жизнь?В точности такая штука есть в постгресе 8.1. Cм. доку Bitmap Heap Scan и Bitmap Index Scan

Andrew SagulinВ настоящий момент индексный скан по 2.5 миллионам записей на FB 1.5.3 и PG 8.1.1 занимает соответственно 21 и 150 сек.Смотрите планы выполнения запросов в постгресе. Включите/отключите возможности bitmap scan-ов с помощью enable_bitmapscan и сравните результаты.

Andrew SagulinИ что мне делать? Сидеть на FB и не дёргаться?Из вашего поста не понятно, почему вы ставите перед собой задачу слазить с FB.
...
Рейтинг: 0 / 0
07.06.2006, 10:43
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33777055
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
Прошу прощения, за лишнее сообщение - не совсем разобрался с цитированием, а удалить теперь не могу.

В FB не устраивает только отсутствие единой последовательной документации. А так, пока есть время и возможность экспериментировать, хочу попробовать разные СУБД, составить о них своё мнение, чтобы, когда "припрёт", можно было быстро "сорентироваться на местности".
Создание кластерного индекса с последующим analyze дало PG такую же производительность, как и FB. Меня это не совсем устраивает, но..., я думаю, тему можно закрыть, так как основные направления в неспешном чтении документации я получил, а остальное зависит только от меня.
Спасибо всем ответившим.
...
Рейтинг: 0 / 0
07.06.2006, 11:45
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33777612
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Не советую "Кластерный индекс" - толку мало (без периодической команды CLUSTER - но не думаю, что тебе захочется ее выполнять на многогигабайтовой таблице), а оптимизатор может "сбиться с толку".
Проблема была только в не сделанном ANALYZE.

Кстати - на сколько больше таблица в PG в сравнении с FB (я знаю, что больше, но насколько?)
...
Рейтинг: 0 / 0
07.06.2006, 13:39
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33777741
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
В моём случае:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE DOMAIN cause  AS int2  DEFAULT  16   NOT NULL;
CREATE DOMAIN conn_num  AS varchar( 34 );
CREATE DOMAIN dur AS int4  NOT NULL;
CREATE DOMAIN lno AS int2;
CREATE DOMAIN tgrp AS char( 6 );

CREATE TABLE conn
(
  datetime datetime,
  anum conn_num,
  bnum conn_num,
  dur dur,
  itgrp tgrp,
  ilno lno,
  otgrp tgrp,
  olno lno,
  cause cause
) 
WITHOUT OIDS;

CREATE INDEX conn_dt
  ON conn
  USING btree
  (datetime);

Средняя длина записи
на FB: 68 байт
на PG: 94 байта

средняя длина ключа по datetime (в расчёте на одну запись):
FB: 7,3 байта
PG: 22 байта

Сколько это будет в расчёте на 280 млн. записей можно посчитать:
FB: около 20 с небольшим гигабайт
PG: около 31 гигабайта
...
Рейтинг: 0 / 0
07.06.2006, 14:06
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33777748
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
Вдогонку:

Код: plaintext
1.
CREATE DOMAIN datetime AS timestamp NOT NULL;
...
Рейтинг: 0 / 0
07.06.2006, 14:09
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33777938
postt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
postt
Участник
[quot автор]В точности такая штука есть в постгресе 8.1. Cм. доку Bitmap Heap Scan и Bitmap Index Scan[quot автор]
Можно поподробнее.
...
Рейтинг: 0 / 0
07.06.2006, 14:52
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33778040
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
postt авторВ точности такая штука есть в постгресе 8.1. Cм. доку Bitmap Heap Scan и Bitmap Index ScanМожно поподробнее.http://www.postgresql.org/docs/8.1/static/performance-tips.html

Here the planner has decided to use a two-step plan: the bottom plan node visits an index to find the locations of rows matching the index condition, and then the upper plan node actually fetches those rows from the table itself. Fetching the rows separately is much more expensive than sequentially reading them, but because not all the pages of the table have to be visited, this is still cheaper than a sequential scan. (The reason for using two levels of plan is that the upper plan node sorts the row locations identified by the index into physical order before reading them, so as to minimize the costs of the separate fetches. The "bitmap" mentioned in the node names is the mechanism that does the sorting.)
...
Рейтинг: 0 / 0
07.06.2006, 15:21
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33779406
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
Дабы поставить небольшую, нежирную такую, точку...
Bitmap scan ни в какую не хотел включаться до выполнения команды

Код: plaintext
cluster conn_dt on conn

Однако, как тут правильно заметили, для многогиговой базы это не выход. Я подозреваю, что оптимизатор за деревьями не видит леса: из-за небольшой неупорядоченности данных по времени, не может определить, что они тем не менее очень хорошо кластеризованы по дате (в силу того, что данные заливаются в базу посуточно). Чтобы помочь оптимизатору, был создан индекс

Код: plaintext
create index conn_date on conn(cast(datetime as date));

После этого на выборках типа:
Код: plaintext
1.
select ... from conn where cast(datetime as date) between ... and ...;

PG показал просто чудеса производительности: скорость считывания данных была более 30 мегабайт в секунду, т.е. практически равна производительности дисковой системы!
...
Рейтинг: 0 / 0
08.06.2006, 08:56
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33779582
st_serg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
st_serg
Участник
а если попробовать что нить вроде "5.9.2. Implementing Partitioning"? что нить измениться? интересно
...
Рейтинг: 0 / 0
08.06.2006, 10:03
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33779800
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Andrew Sagulin
Код: plaintext
1.
create index conn_date on conn(cast(datetime as date));
После этого на выборках типа:
Код: plaintext
1.
select ... from conn where cast(datetime as date) between ... and ...;
PG показал просто чудеса производительности: скорость считывания данных была более 30 мегабайт в секунду, т.е. практически равна производительности дисковой системы!

Респект !!!
А я дураком был, использовал timestamptz - теперь он никак индекс ни по date_trunc ни по cast(... as date) делать не хочет. Ну и фиг. Пока скорость устраивает (кстати такая же: 1млн за 40-60 сек для данных вне кеша (прошлый месяц))
Зато для текущего месяца сумма по абонентам - 250т за 1 секунду (под конец месяца соотв-но за 2-3 секунды). Размер shared_buffers = 60000, work_mem = 16384. На машине гиг оперативки.
...
Рейтинг: 0 / 0
08.06.2006, 11:03
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33780891
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
st_serg
а если попробовать что нить вроде "5.9.2. Implementing Partitioning"? что нить измениться? интересно


О! Это то, что доктор прописал!Предварительные испытания показали, что это действительно работает. Небольшое усложнение программы для заливки данных (пара-тройку команд DDL) полностью окупается отсутствием индекса по дате-времени и очень быстрой работой запросов на выборку. Будем двигаться в этом направлении.
...
Рейтинг: 0 / 0
08.06.2006, 15:19
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33783757
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
Испытания на реальном материале показали весьма странное поведение partitioning: первый запрос на каком-либо интервале выполняет долго (при этом скорость чтения с винчестера около 5 мегабайт в секунду). Повторный запрос выполняется очень быстро (трансфер с винта около 50 мег в секунду). Стоит поменять запрос, и опять - первый раз тормоза, второй - всё летает.
Во всех случаях план выполнения запроса один и тоже, нагрузка на процессор - не более 10%. Влияние кеша ОС исключено: считываются данные объёмом более 1 Гбайта при объёме кеша ОС не более 400 мегабайт.
Пробовал делать union таблиц вручную - тоже всё просто летает.
"Ничего не понимаю"...
...
Рейтинг: 0 / 0
09.06.2006, 15:40
| Ответить | Цитировать | Написать  
Оптимизация index scan
    #33783898
Andrew Sagulin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Sagulin
Гость
Немного прояснилось, из-за чего падает скорость чтения, но почему так происходит, не знаю.
В общем, файл-монитор показал что при последовательном чтении файлов таблиц наблюдается запись (!) в предыдущие уже считанные таблицы (но не все). Vacuum? А зачем? Мусора-то нет: пустые таблицы заполнялись командами insert и никаких update. Analyze по всем таблицам делал. Может мне кто-нибудь прояснить причину такого поведения PG?
...
Рейтинг: 0 / 0
09.06.2006, 16:19
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Оптимизация index scan
    #35350314
john222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
john222
Гость
Есть простая таблица с полем tsvector и GIN индексом.
Записей 7млн. Индекс получился размером 500Мб.
Сервер 2 ядра, 2 винта в raid0, RAM 6Gb.

Настройки PG:
shared_buffers = 2000MB
temp_buffers = 32MB
work_mem = 64MB
effective_cache_size = 2000MB

Запрос вида:
Код: plaintext
1.
SELECT id_name FROM plainto_tsquery('linux') q, name WHERE lexem @@ q ORDER BY ts_rank (lexem,q) DESC LIMIT  200
первый раз:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Limit  (cost= 23895 . 58 .. 23896 . 08  rows= 200  width= 104 ) (actual time= 8292 . 885 .. 8293 . 012  rows= 200  loops= 1 )
  ->  Sort  (cost= 23895 . 58 .. 23914 . 42  rows= 7536  width= 104 ) (actual time= 8292 . 883 .. 8292 . 932  rows= 200  loops= 1 )
        Sort Key: (ts_rank(name.lexem, q.q))
        Sort Method:  top-N heapsort  Memory: 34kB
        ->  Nested Loop  (cost= 325 . 26 .. 23569 . 88  rows= 7536  width= 104 ) (actual time= 30 . 825 .. 8253 . 318  rows= 59897  loops= 1 )
              ->  Function Scan on plainto_tsquery q  (cost= 0 . 00 .. 0 . 01  rows= 1  width= 32 ) (actual time= 0 . 026 .. 0 . 027  rows= 1  loops= 1 )
              ->  Bitmap Heap Scan on name  (cost= 325 . 26 .. 23456 . 82  rows= 7536  width= 72 ) (actual time= 30 . 779 .. 8170 . 821  rows= 59897  loops= 1 )
                    Recheck Cond: (name.lexem @@ q.q)
                    ->  Bitmap Index Scan on "lexem-gin"  (cost= 0 . 00 .. 323 . 38  rows= 7536  width= 0 ) (actual time= 9 . 904 .. 9 . 904  rows= 59897  loops= 1 )
                          Index Cond: (name.lexem @@ q.q)
Total runtime:  8293 . 160  ms

второй:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Limit  (cost= 23895 . 58 .. 23896 . 08  rows= 200  width= 104 ) (actual time= 105 . 396 .. 105 . 508  rows= 200  loops= 1 )
  ->  Sort  (cost= 23895 . 58 .. 23914 . 42  rows= 7536  width= 104 ) (actual time= 105 . 395 .. 105 . 436  rows= 200  loops= 1 )
        Sort Key: (ts_rank(name.lexem, q.q))
        Sort Method:  top-N heapsort  Memory: 34kB
        ->  Nested Loop  (cost= 325 . 26 .. 23569 . 88  rows= 7536  width= 104 ) (actual time= 12 . 508 .. 86 . 130  rows= 59897  loops= 1 )
              ->  Function Scan on plainto_tsquery q  (cost= 0 . 00 .. 0 . 01  rows= 1  width= 32 ) (actual time= 0 . 026 .. 0 . 027  rows= 1  loops= 1 )
              ->  Bitmap Heap Scan on name  (cost= 325 . 26 .. 23456 . 82  rows= 7536  width= 72 ) (actual time= 12 . 470 .. 44 . 412  rows= 59897  loops= 1 )
                    Recheck Cond: (name.lexem @@ q.q)
                    ->  Bitmap Index Scan on "lexem-gin"  (cost= 0 . 00 .. 323 . 38  rows= 7536  width= 0 ) (actual time= 9 . 175 .. 9 . 175  rows= 59897  loops= 1 )
                          Index Cond: (name.lexem @@ q.q)
Total runtime:  105 . 606  ms

free
Код: plaintext
1.
2.
3.
4.
             total       used       free     shared    buffers     cached
Mem:        6044924      2974612      3070312            0       149220      1986652 
-/+ buffers/cache:      838740      5206184 
Swap:        489940          172       489768

Свободной памяти полно, и было бы хорошо если после первого запроса база или ОС
закинула в память весь 500Мб индекс. Но по факту получаеться что индекс попадает в память маленькими кусочками при каждом новом запросе.
Как можно сказать базе чтоб GIN индекс всегда держала в памяти? Время поиска должно быть не более 0.3 сек.
...
Рейтинг: 0 / 0
03.06.2008, 08:44
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация index scan
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]