Re: [pgsql-ru-general] Массивы: REFERENCES и выборки
Re: [pgsql-ru-general] Массивы: REFERENCES и выборки
От:
Sergey Konoplev <gray.ru@gmail.com>
Дата:
2012/12/14 Dmitry E. Oboukhov :
> было три таблички
>
> orders
> drivers
>
> и
>
> orders_drivers - oid, did, dist, time
>
> за годы работы получается что orders_drivers скопилась огромная.
>
> ну и хочется ее свернуть в массивы композитных полей вида
> (did,dist,time)[] и класть эти массивчики в orders.
>
> фича в том что с ордером работа кратковременная, далее он в базе
> просто лежит.
>
> а вот джоин на водителей через промежуточную стомилионную таблицу
> orders_drivers уже тяжел.
Какой именно запрос тормозит, можно посмотреть на него, на explain
analyze и на индексы?
> но вот что хочется:
>
> 1. таки иметь FOREIGN (ну или если это невозможно то хотя бы CHECK, на
> проверку валидности did'ов (наличия их в drivers)
FK/CHECK не получится, триггерами делайте проверку.
> 2. иметь возможность выбрать только одно подзначение массива в массив,
> то есть записи
>
> 1, ..., {(23,222,0.5),(22,332,0.6)}
> 2, ..., {(11,222,27)}
>
> преобразовать выборкой в
>
> 1, ..., {23,22}
> 2, ..., {11}
>
> поодиночке понятно как это сделать. а внутри выборки есть возможность?
SELECT ..., array(SELECT did FROM unnest(drivers_orders_array)) FROM orders ...
> ну и последнее.
> иногда хочется выбрать orders по входящему набору did
>
> как такой столбик проиндексировать лучше?
Сделать отдельное поле drivers_orders_did_array и на него gin индекс.
Чтобы индекс работал см.
http://www.postgresql.org/docs/9.2/interactive/intarray.html.
> ну и похожая про индексы задача:
>
> таблица
>
> тема, сообщение, {метка1,метка2,метка3}
>
> метки хранятся прямо в текстовом виде (когда-то хранили опять же в
> отдельной таблице, потом из за нагрузки денормализовали)
> метки текстовые
>
>
> хочется отвечать на вопрос
>
> WHERE tags @> {метка1,метка2}
>
> как массивы лучше проиндексировать?
Опять же http://www.postgresql.org/docs/9.2/interactive/intarray.html.
>
> сейчас построили 5 разных индексов по 5 первым меткам...
>
> говорят что
> такое можно GIST/GIN индексом индексировать, но у меня что-то не
> получается правильно такой индекс построить по текстовому массиву.
> можно пример как этими гист/гин пользоваться?
> операции какие-то они хотят, где они описаны?
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :' : email: unera@debian.org jabber://UNera@uvw.ru
> `. `~' GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlDLuJkACgkQq4wAz/jiZTd2xACg5DAnSoq44ydR2WtLgMvOF6tA
> bJYAoJG9JH0WGooQP4NiAC5HlNUm+jm4
> =EroA
> -----END PGP SIGNATURE-----
>
--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp
Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
Re: Re: [pgsql-ru-general] Массивы: REFERENCES и выборки
>> было три таблички
>>
>> orders
>> drivers
>>
>> и
>>
>> orders_drivers - oid, did, dist, time
>>
>> за годы работы получается что orders_drivers скопилась огромная.
>>
>> ну и хочется ее свернуть в массивы композитных полей вида
>> (did,dist,time)[] и класть эти массивчики в orders.
>>
>> фича в том что с ордером работа кратковременная, далее он в базе
>> просто лежит.
>>
>> а вот джоин на водителей через промежуточную стомилионную таблицу
>> orders_drivers уже тяжел.
> Какой именно запрос тормозит, можно посмотреть на него, на explain
> analyze и на индексы?
SELECT
*
FROM
orders o
JOIN
orders_drivers od ON od.oid = o.id
JOIN
drivers d ON d.id = od.did
WHERE
d.чего-нибудь = чему-нибудь
индексы
o.id - primary
d.id - primary
od.did - btree
od.oid, od.did - btree, unique
d.чего-нибудь - тоже конечно индекс есть
ну и вторая задача от свертки, которую хотим получить - возможность
сносить все в архивную таблицу:
будет orders и orders_archive
вторая будет INHERITS от первой, ну и соответственно кронскриптом
orders что были 5 дней назад будут из первой во вторую выноситься :)
когда имеется дерево взаимозависимостей мне непонятно как такое делать
>> но вот что хочется:
>>
>> 1. таки иметь FOREIGN (ну или если это невозможно то хотя бы CHECK, на
>> проверку валидности did'ов (наличия их в drivers)
> FK/CHECK не получится, триггерами делайте проверку.
я так и понял, просто думал может таки есть какой вариант
>> 2. иметь возможность выбрать только одно подзначение массива в массив,
>> то есть записи
>>
>> 1, ..., {(23,222,0.5),(22,332,0.6)}
>> 2, ..., {(11,222,27)}
>>
>> преобразовать выборкой в
>>
>> 1, ..., {23,22}
>> 2, ..., {11}
>>
>> поодиночке понятно как это сделать. а внутри выборки есть возможность?
> SELECT ..., array(SELECT did FROM unnest(drivers_orders_array)) FROM orders ...
>> ну и последнее.
>> иногда хочется выбрать orders по входящему набору did
>>
>> как такой столбик проиндексировать лучше?
> Сделать отдельное поле drivers_orders_did_array и на него gin индекс.
> Чтобы индекс работал см.
> http://www.postgresql.org/docs/9.2/interactive/intarray.html.
это я читал. но у меня арреи все не интовые а текстовые.
соответственно когда пробуешь построить GIST/GIN индекс просит ops'ы
указать и непонятно какие их туда указывать
--
. ''`. Dmitry E. Oboukhov
: :’ : email: unera@debian.org jabber://UNera@uvw.ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
Re: [pgsql-ru-general] Re: [pgsql-ru-general] Массивы: REFERENCES и выборки
От:
Sergey Konoplev <gray.ru@gmail.com>
Дата:
2012/12/15 Dmitry E. Oboukhov :
>> Какой именно запрос тормозит, можно посмотреть на него, на explain
>> analyze и на индексы?
>
> SELECT
> *
> FROM
> orders o
> JOIN
> orders_drivers od ON od.oid = o.id
> JOIN
> drivers d ON d.id = od.did
> WHERE
> d.чего-нибудь = чему-нибудь
>
> индексы
> o.id - primary
> d.id - primary
> od.did - btree
> od.oid, od.did - btree, unique
> d.чего-нибудь - тоже конечно индекс есть
Всёдки нужен explain analyze.
> ну и вторая задача от свертки, которую хотим получить - возможность
> сносить все в архивную таблицу:
> будет orders и orders_archive
> вторая будет INHERITS от первой, ну и соответственно кронскриптом
> orders что были 5 дней назад будут из первой во вторую выноситься :)
Наследование не нужно, иначе при выборке из orders план будет
строиться с учётом партиций, можно просто create table ... (like ...)
использовать. Или можно сделать orders_base и наследовать от неё обе
партиции, если на определении полей хочется сэкономить.
> когда имеется дерево взаимозависимостей мне непонятно как такое делать
Руками, опять же через триггера.
>>> ну и последнее.
>>> иногда хочется выбрать orders по входящему набору did
>>>
>>> как такой столбик проиндексировать лучше?
>
>> Сделать отдельное поле drivers_orders_did_array и на него gin индекс.
>> Чтобы индекс работал см.
>> http://www.postgresql.org/docs/9.2/interactive/intarray.html.
>
> это я читал. но у меня арреи все не интовые а текстовые.
Хранить в tsvector вместо массивов, индексировать GIN/GIST, искать
tags @@ to_tsquery('tag1 & tag2').
> соответственно когда пробуешь построить GIST/GIN индекс просит ops'ы
> указать и непонятно какие их туда указывать
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :' : email: unera@debian.org jabber://UNera@uvw.ru
> `. `~' GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlDMMxwACgkQq4wAz/jiZTc5yACfUPtApHd8Rwng9tvYESdipVKx
> 0kIAmwebd1b9QZcPbyS1KLETrTgdAJZT
> =zXsM
> -----END PGP SIGNATURE-----
>
--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp
Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com