Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как ускорить запрос? / 21 сообщений из 21, страница 1 из 1
10.10.2006, 13:10
    #34044120
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Извините, случайно запостил недописанное сообщение. Повторяюсь.
Переносим БД из MSSQL. С PostgreSQL недавно начал работать, причём наскоком, толком и изучать некогда было. До сих пор достаточно успешно боролся с возникающими препятствиями, но сейчас поставлен в тупик.
Есть две таблицы (упрощу для наглядности, но ключи и связи такие же):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
 
create table t1 
(id_t1 serial not null, col1 int4 not null, col2 int4 not null, col3 int4 not null, col4 float, 
constraint pk_t1 primary key (id_t1),
constraint uq_t1 unique (col1, col2, col3));

create table t2
(id_t2 serial not null, id_t1 int4 not null, col1 float, col2 float, col3 float, 
constraint pk_t2 primary key (id_t2),
constraint uq_t2 unique (id_t1, col1),
constraint fk_t2__t1 foreign key (id_t1)
               references t1 (id_t1)
               on delete cascade);
и есть запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
select t1.col1, 
         t1.col2,
         t1.col3,
         t1.col4,
         (select min(t2.col1) from t2 where t2.id_t1 = t1.id_t1) as t2_col1_begin,
         (select max(t2.col1) from t2 where t2.id_t1 = t1.id_t1) as t2_col1_end
from t1
- для каждой строки из родительской t1 найти минимум и максимум из дочерней t2.
Сейчас в этих таблицах щедящие тестовые данные - в родительской 300, в дочерней 12000000. Записи дочерней почти равномерно распределены между родительскими.
В MSSQL никогда с этим кодом проблем не было при любом обёме данных - для каждой родительской строки по два перехода по индексу к крайним значениям второго поля справа и слева. Времени практически не занимает-миллисекуды.
В PostgreSQL идёт долгое и нудное сканирование индекса на основе ограничения уникальности - 2 раза. Времени - две минуты на свежайших статистиках. А чуть погодя - до двадцати минут. Ни в какие ворота не лезет.
Я уже и так и эдак.. Различные комбинации индексов, различные варианты запросов пробывал. Кто-нибудь значет, что тут можно подкрутить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 13:24
    #34044180
Vladimir Sitnikov
Vladimir Sitnikov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Для postgres<8.2,
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
select t1.col1, 
         t1.col2,
         t1.col3,
         t1.col4,
         (select t2.col1 from t2 where t2.id_t1 = t1.id_t1 order by t2.col1 limit  1 ) as t2_col1_begin,
         (select t2.col1 from t2 where t2.id_t1 = t1.id_t1 order by t2.col1 desc limit  1 ) as t2_col1_end
from t1

Если не ошибаюсь, postgres 8.2 сам по себе будет производить такую трансформацию (min -> order by+limit).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 14:46
    #34044542
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Конечно же я так пробывал - ещё дольше работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 15:07
    #34044640
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
to Vladimir Sitnikov >> MSSQL тоже преобразует min() и max() в top 1 с одной и с другой стороны. Но делает это элегантно - сразу берёт по одной записи индекса. Postgres же, при использовании min() и max() просто сканирует индекс, а при использовании order by .. limit 1/ order by .. desc limit 1 выдаёт совсем уж грустный план - сперва выбирает всё, потом сортирует, потом берёт верхнюю строку.. :-(

Может индекс какой хитрый построить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 15:09
    #34044652
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Да, забыл сказать, у меня "Линтер-ВС" - СУБД на основе PostgreSQL 7.4.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 15:10
    #34044657
Кувалдин Роман
Кувалдин Роман
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Сергей АБДа, забыл сказать, у меня "Линтер-ВС" - СУБД на основе PostgreSQL 7.4.

Тогда вопросы к Линтеру - что они внутри накосячили такого, что индексы не работают.
П.С. Кстати, не все линтеры сделаны на базе Постгреса. Об этом даже на данном форуме писали.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 15:13
    #34044670
DeWiL
DeWiL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
а может подумать о версии 8.1?
все таки отличия есть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 16:13
    #34044913
4321ё
4321ё
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Сергей АБto Vladimir Sitnikov >> MSSQL тоже преобразует min() и max() в top 1 с одной и с другой стороны. Но делает это элегантно - сразу берёт по одной записи индекса. Postgres же, при использовании min() и max() просто сканирует индекс, а при использовании order by .. limit 1/ order by .. desc limit 1 выдаёт совсем уж грустный план - сперва выбирает всё, потом сортирует, потом берёт верхнюю строку.. :-(

Может индекс какой хитрый построить?
постройте для проверки _явные_ индексы по
(id_t1, col1)
и
(id_t1, (-col1))
(7.3 может потребовать оборачивания (-col1) в ф-ю)
и соответственно вместо
order by .. desc limit 1
пишите
order by (-col1) limit 1
(или ф-ю, если обернули).
и приведите таки планы запросов, а то ить не ясно, какие именно индексы использует постгрес (а то вже ж "обычный" индекс у него не используется для Like-оф 'xxxxx%', могабыть и индекс уникьев не годен для выборок, кто их знаить).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 17:33
    #34045144
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
to 4321ё >> Явные индексы тоже создавал, и составные, и по-отдельности - не помогает. Только вот с минусом не пробывал - щас проверил, так PostgreSQL использует для обоих сортировок, и в одну, и в другую сторону, индекс, который с минусом :-)
Ничего не понимаю..
В планах индекс по ограничению уникальности используется, как и явные индексы, когда они есть, но присходит для каждой строки родительской таблицы сканирование всех записей по условию связывания.
То есть в конечном итоге сканируется все 12000000 записей, по два раза, и то не за один проход.
Вот план для варианта только с ограничениями уникальности, и при использовании min/max (в плане тоже подправил имена объектов, чтобы похоже было на пример):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
Seq Scan on t1 (cost= 100000000 . 00 .. 202769368 . 30  rows= 304  width= 108 ) (actual time= 1177 . 375 .. 126534 . 295  rows= 304  loops= 1 )
  SubPlan
    ->  Aggregate  (cost= 169028 . 56 .. 169028 . 56  rows= 1  width= 8 ) (actual time= 79 . 135 .. 79 . 136  rows= 1  loops= 304 )
          ->  Index Scan using uq_t2 on t2 (cost= 0 . 00 .. 168898 . 68  rows= 51948  width= 8 ) (actual time= 0 . 021 .. 50 . 727  rows= 36568  loops= 304 )
                Index Cond: (id_t1 = $ 0 )
    ->  Aggregate  (cost= 169028 . 56 .. 169028 . 56  rows= 1  width= 8 ) (actual time= 336 . 972 .. 336 . 972  rows= 1  loops= 304 )
          ->  Index Scan using uq_t2 on t2 (cost= 0 . 00 .. 168898 . 68  rows= 51948  width= 8 ) (actual time= 2 . 903 .. 305 . 631  rows= 36568  loops= 304 )
                Index Cond: (id_t1 = $ 0 )
Total runtime:  126534 . 847  ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 17:43
    #34045184
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Кувалдин Роман
Тогда вопросы к Линтеру - что они внутри накосячили такого, что индексы не работают.
П.С. Кстати, не все линтеры сделаны на базе Постгреса. Об этом даже на данном форуме писали.
Самому интересно, почему так туго с индексами. А другой Линтер тоже пользовали, тот вообще - обнять и плакать..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 17:57
    #34045244
4321ё
4321ё
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
думаицца это проблема высокоинтелектуальности оптимизатора.
попробуйте набмануть оптимизатор примерно такими модификациями
Vladimir Sitnikov
[fix]select t1.col1,
t1.col2,
t1.col3,
t1.col4,
(select t2.col1 from t2 where
t2.id_t1 > t1.id_t1-1
order by t2.id_t1, t2.col1 limit 1) as t2_col1_begin,
(select t2.col1 from t2 where
t2.id_t1 < t1.id_t1 +1
order by t2.id_t1 DESC, t2.col1 DESC limit 1) as t2_col1_end
from t1
[/fix]
(надеюс, дробных значений в целом поле не бываит. хотя от >< и DESC можно уйти, в т.ч. используя индек с "-")

только не все применяйте сразу. пробуйте частями. (сначала только в ордер бай)


ЗЫ. и для 7.4. незачем приводить план запроса с max (и тестировать макс) Ибо про 7.4. заведомо известно, что max он не раскрывает в LIMIT (сам), а использует "агрегирование".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 18:01
    #34045264
4321ё
4321ё
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select t1.col1, 
         t1.col2,
         t1.col3,
         t1.col4,
         (select t2.col1 from t2 where
                  t2.id_t1 > t1.id_t1-1 
                  order by t2.id_t1, t2.col1 limit 1) as t2_col1_begin,
         (select t2.col1 from t2 where
                   t2.id_t1 < t1.id_t1 +1
                   order by  t2.id_t1 DESC, t2.col1 DESC limit 1) as t2_col1_end
from t1

поправляю форматирование
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2006, 18:25
    #34045367
Vladimir Sitnikov
Vladimir Sitnikov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Честно говоря, про 7.x сказать ничего не могу. 8-ка (8.1, вроде) явно выдаёт backward index scan на предложенной схеме, и, думаю, это один запрос к индексу (+проверка существования записи).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2006, 13:43
    #34047194
pavelvp
pavelvp
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Сергей АБА другой Линтер тоже пользовали, тот вообще - обнять и плакать.. Можно поинтересоваться, что заставило Вас плакать при работать с ЛИНТЕР?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.10.2006, 14:08
    #34050544
Кувалдин Роман
Кувалдин Роман
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
pavelvp Сергей АБА другой Линтер тоже пользовали, тот вообще - обнять и плакать.. Можно поинтересоваться, что заставило Вас плакать при работать с ЛИНТЕР?

Стопудово вопросы, сязанные с сертификацией по безопасности. ЛИНТЕР это единственная СУБД, имеющая 2 класс защищенности.
Кстати, если это так, то к постгресу ваша СУБД никакого отношения не имеет. Это совсем другой продукт.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.10.2006, 14:23
    #34050618
Кувалдин Роман
Кувалдин Роман
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Кувалдин РоманКстати, если это так, то к постгресу ваша СУБД никакого отношения не имеет. Это совсем другой продукт.

Слова разработчика

Кстати, по поводу ЛИНТЕР-а рекомендую обратиться прямо к pavelvp
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.10.2006, 23:49
    #34052206
Сергей АБ
Сергей АБ
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
По всему видно, что коллеги, высказывающие опасения по поводу причастности "моего Линтера" к PostgreSQL, в глаза не видели "не-Postgre-Линтер". Сразу оговорюсь - речь идёт о "Линтер-ВС" 6.0. Напутать что либо здесь невозможно - есть два Линтера-ВС, и они абсолютно разные - не перепутаешь, хотя даже цифры одинаковые!
Так вот "мой Линтер" запускается командой service postgresql start :-)
Первая, если можно так сказать, версия СУБД Линтер-ВС (Вооружённые Силы) была разработана в годах 2003-2004 ЗАО НПП "РЕЛЭКС", на базе Линтер 5.7, в Министерство Обороны поставлялась от имени ВНИИНС (НПО, институт, фирма?? -не помню). Затем году в 2004-2005 произошла подмена этой СУБД на совершенно другую, разработаную в самой ВНИИНС на базе PostgreSQL 7.4.
pavelvp - разработчик того, первого Линтера-ВС. Здравствуйте, pavelvp.
Тот топик, на который нас отправляет Кувалдин Роман -
Кувалдин Роман
Слова разработчика

Кстати, по поводу ЛИНТЕР-а рекомендую обратиться прямо к pavelvp инициирован мною же, в далёком 2004. Тогда отмазаться от перехода с MSSQL на Линтер удалось, сейчас же пришло время вернуть долги, но переходом уже на новый Линтер-ВС.
pavelvp, плакать нам тогда пришлось от осознания, какой же объём работы предстояло бы выполнить при миграции с SQL Server 2000, и ещё не известно, получилось ли бы. У Вас замечательный продукт, но очень специфичен. Я уже не помню всех тонкостей, но помню неподдельную радость в глазах и чуство, что теперь-то горы свернём, когда нам принесли "новую версию" Линтера-ВС, по сути - Постгрес. С ним тоже немало заморочек, но всё решаемо.
P.S. С проблемой, давшей начало данному разговору, я решил не заморачиваться. При выборе от одной (что чаще всего), до пары десятков строк из родительской таблицы, время доступа приемлемое. А когда нужно всё прочитать (что тоже не редкость) - придётся ждать, что ж делать - пока ничего не помогло, а дальше время тратить - непозволительная роскошь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2006, 01:53
    #34052265
Кувалдин Роман
Кувалдин Роман
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Ж-)
Я - дятел. И не посмотрел, кто топик начинал :-(


=====================================
Страну, в которой все ходят на бровях,
на колени не поставишь...
=====================================
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2006, 15:22
    #34054012
pavelvp
pavelvp
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Сергей АБЗдравствуйте, pavelvp.
Приветствую!У Вас замечательный продукт, Спасибо :-)
но очень специфичен. Я уже не помню всех тонкостей... Вот это как раз самое интересное. Очень жаль что Вы уже не помните тонкостей...
Мне не понятно что в ЛИНТЕР такого специфичного.
ИМХО PostgreSQL намного более специфичен. По крайней мере начать работать с ЛИНТЕР можно гораздо быстрее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2006, 11:30
    #34079446
VAKis
VAKis
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
Извините за вопрос.
Я в постгресе абсолютный новичек. В оракле соображаю. Имеется ли в постгресе возможность секционирования таблиц (или что-то подобное разбиению на разделы как в оракле). Начальник достал вобще, а копаться и искать времени нет. Заранее спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2006, 13:40
    #34080057
st_serg
st_serg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить запрос?
VAKisИзвините за вопрос.
Я в постгресе абсолютный новичек. В оракле соображаю. Имеется ли в постгресе возможность секционирования таблиц (или что-то подобное разбиению на разделы как в оракле). Начальник достал вобще, а копаться и искать времени нет. Заранее спасибо.
использовать поиск по форуму? посмотреть документацию (5.9. Partitioning)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как ускорить запрос? / 21 сообщений из 21, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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