Thread: Re: [pgsql-ru-general] Массивы: REFERENCES и выборки
2012/12/14 Dmitry E. Oboukhov <unera@debian.org>: > было три таблички > > 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
>> было три таблички >> >> 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
Attachment
Re: [pgsql-ru-general] Re: [pgsql-ru-general] Массивы: REFERENCES и выборки
From
Sergey Konoplev
Date:
2012/12/15 Dmitry E. Oboukhov <unera@debian.org>: >> Какой именно запрос тормозит, можно посмотреть на него, на 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