Разбиение на схемы / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Разбиение на схемы

6 сообщений из 6, страница 1 из 1

Разбиение на схемы

#39932544

Squamis

Гость

Добрый день!
Сделал копию базы и начал над ней ставить эксперименты.
Разбил основную "живую" схему на 6 схем (по годам), т.е. в каждой схеме количество таблиц одинаково и таблицы одинаковы!
Все основные запросы идут к текущей схеме 2020, однако иногда для начальства необходимо делать выборки за прошлые годы, соответственно необходимо обращаться к схемам 2019,2018...

Попробовал создать VIEW на все таблицы типа:

Код: plsql

1.
2.
3.

select a1, a2, ... from v2020.orders union all
select a1, a2, ... from v2019.orders union all
select a1, a2, ... from v2018.orders

и так на каждую таблицу. Столкнулся с УЖАСНЫМИ тормозами.
Но, если переписать запрос с явно указанными схемами, т.е. повторить запрос три раза, но указав разные схемы - отлично отрабатывает.

Не подскажете, в какую сторону двигаться, чтобы не "троить" запросы (обращаясь ко всем схемам)

P.S. Секционирование тоже попробовал, но не получилось, т.к. все таблицы имеют внешние ключи и пока Postgres к этому не совсем готов :)

Заранее спасибо!

...

Рейтинг:

0 / 0

29.02.2020, 16:57

| Ответить | Цитировать | Написать

Разбиение на схемы

#39932545

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Squamis,

Без полного текста запроса и результатов explain analyze (и для быстрого и для медленного случая) вам никто тут ничем не поможет, телепаты в отпуске.

...

Рейтинг:

0 / 0

29.02.2020, 17:08

| Ответить | Цитировать | Написать

Разбиение на схемы

#39932548

Squamis

Гость

Без VIEW

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.

explain analyze
select
    ord.fldidxord   as fldidxord,
    prd.fldidxprd   as fldidxprd,
    prd.fldidxprdp  as fldidxprdp
from
    v2000."orders"              ord
    inner join v2000."products" prd on (ord.fldidxord = prd.fldidxord)
where
        ord.fldidxord = any('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}')
    and prd.fldidxprd = fldidxprdp

union all

select
    ord.fldidxord   as fldidxord,
    prd.fldidxprd   as fldidxprd,
    prd.fldidxprdp  as fldidxprdp
from
    v2019."orders"              ord
    inner join v2019."products" prd on (ord.fldidxord = prd.fldidxord)
where
        ord.fldidxord = any('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}')
    and prd.fldidxprd = fldidxprdp

union all

select
    ord.fldidxord   as fldidxord,
    prd.fldidxprd   as fldidxprd,
    prd.fldidxprdp  as fldidxprdp
from
    v2020."orders"              ord
    inner join v2020."products" prd on (ord.fldidxord = prd.fldidxord)
where
        ord.fldidxord = any('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}')
    and prd.fldidxprd = fldidxprdp


order by
    fldidxprd

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

"QUERY PLAN"
"Sort  (cost=1514.16..1514.17 rows=4 width=24) (actual time=1.598..1.622 rows=239 loops=1)"
"  Sort Key: prd.fldidxprd"
"  Sort Method: quicksort  Memory: 43kB"
"  ->  Append  (cost=2.28..1514.12 rows=4 width=24) (actual time=0.069..1.480 rows=239 loops=1)"
"        ->  Nested Loop  (cost=2.28..929.04 rows=2 width=24) (actual time=0.068..1.405 rows=239 loops=1)"
"              ->  Index Only Scan using orders_pkey on orders ord  (cost=0.41..25.97 rows=17 width=8) (actual time=0.019..0.088 rows=17 loops=1)"
"                    Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                    Heap Fetches: 4"
"              ->  Bitmap Heap Scan on products prd  (cost=1.87..53.11 rows=1 width=24) (actual time=0.023..0.071 rows=14 loops=17)"
"                    Recheck Cond: (fldidxord = ord.fldidxord)"
"                    Filter: (fldidxprd = fldidxprdp)"
"                    Rows Removed by Filter: 34"
"                    Heap Blocks: exact=440"
"                    ->  Bitmap Index Scan on products_idx2  (cost=0.00..1.87 rows=46 width=0) (actual time=0.011..0.011 rows=48 loops=17)"
"                          Index Cond: (fldidxord = ord.fldidxord)"
"        ->  Nested Loop  (cost=0.72..568.27 rows=1 width=24) (actual time=0.036..0.036 rows=0 loops=1)"
"              ->  Index Only Scan using orders_pkey on orders ord_1  (cost=0.29..27.15 rows=17 width=8) (actual time=0.036..0.036 rows=0 loops=1)"
"                    Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                    Heap Fetches: 0"
"              ->  Index Scan using products_fldidxord_idx on products prd_1  (cost=0.43..31.82 rows=1 width=24) (never executed)"
"                    Index Cond: (fldidxord = ord_1.fldidxord)"
"                    Filter: (fldidxprd = fldidxprdp)"
"        ->  Nested Loop  (cost=0.14..16.74 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=1)"
"              ->  Seq Scan on products prd_2  (cost=0.00..11.38 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=1)"
"                    Filter: (fldidxprd = fldidxprdp)"
"              ->  Index Only Scan using orders_pkey on orders ord_2  (cost=0.14..4.84 rows=1 width=8) (never executed)"
"                    Index Cond: ((fldidxord = prd_2.fldidxord) AND (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[])))"
"                    Heap Fetches: 0"
"Planning Time: 1.590 ms"
"Execution Time: 1.727 ms"

...

Рейтинг:

0 / 0

29.02.2020, 17:28

| Ответить | Цитировать | Написать

Разбиение на схемы

#39932549

Squamis

Гость

С VIEW

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

select
    ord.fldidxord   as fldidxord,
    prd.fldidxprd   as fldidxprd,
    prd.fldidxprdp  as fldidxprdp
from
    v2000."v$orders"              ord
    inner join v2000."v$products" prd on (ord.fldidxord = prd.fldidxord)
where
        ord.fldidxord = any('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}')
    and prd.fldidxprd = fldidxprdp
order by
    fldidxprd

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.

"QUERY PLAN"
"Nested Loop  (cost=818085.65..826462.38 rows=2498 width=24) (actual time=23268.234..23294.647 rows=239 loops=1)"
"  Join Filter: (ord.fldidxord = products.fldidxord)"
"  Rows Removed by Join Filter: 15494122"
"  ->  Unique  (cost=817999.65..818783.33 rows=9796 width=680) (actual time=20189.090..21679.831 rows=911433 loops=1)"
"        ->  Sort  (cost=817999.65..818024.14 rows=9796 width=680) (actual time=20189.084..20821.681 rows=911433 loops=1)"
"              Sort Key: products.fldidxprd, products.fldidxprdp, products.fldpg_user, products.fldpg_date, products.fldidxsync, products.fldidxfactory, products.fldidxord, products.fldidxhdr, products.fldidxconstrtype, products.fldidxplantunit, products.fldidxware, products.fldidxsystemp, products.fldidxsystemf, products.fldidxcolor1, products.fldidxcolor2, products.fldidxsashside, products.fldprd_nn, products.fldprd_dim, products.fldprd_pir, products.fldprd_cell, products.fldprd_price, products.fldprd_date, products.fldprd_bool, products.fldprd_ant2_dates, products.fldprd_ant2_dist, products.fldprd_ant3_dates, products.fldprd_ant3_dist, products.fldprd_txt, products.fldprd_qty, products.fldprd_scan, products.fldidxgasket"
"              Sort Method: external merge  Disk: 1373536kB"
"              ->  Append  (cost=0.00..813898.07 rows=9796 width=680) (actual time=435.779..2524.042 rows=911433 loops=1)"
"                    ->  Seq Scan on products  (cost=0.00..468860.71 rows=1459 width=1595) (actual time=435.778..2048.455 rows=126919 loops=1)"
"                          Filter: (fldidxprd = fldidxprdp)"
"                          Rows Removed by Filter: 143669"
"                    ->  Gather  (cost=1000.00..344879.05 rows=8336 width=1572) (actual time=0.647..410.811 rows=784514 loops=1)"
"                          Workers Planned: 6"
"                          Workers Launched: 6"
"                          ->  Parallel Seq Scan on products products_1  (cost=0.00..343045.45 rows=1389 width=1572) (actual time=57.797..545.822 rows=112073 loops=7)"
"                                Filter: (fldidxprd = fldidxprdp)"
"                                Rows Removed by Filter: 126602"
"                    ->  Seq Scan on products products_2  (cost=0.00..11.38 rows=1 width=680) (actual time=0.013..0.013 rows=0 loops=1)"
"                          Filter: (fldidxprd = fldidxprdp)"
"  ->  Materialize  (cost=86.00..87.28 rows=51 width=8) (actual time=0.000..0.001 rows=17 loops=911433)"
"        ->  Subquery Scan on ord  (cost=86.00..87.02 rows=51 width=8) (actual time=0.350..0.360 rows=17 loops=1)"
"              ->  HashAggregate  (cost=86.00..86.51 rows=51 width=466) (actual time=0.345..0.352 rows=17 loops=1)"
"                    Group Key: orders.fldidxord, orders.fldidxordp, orders.fldpg_user, orders.fldpg_date, orders.fldidxsync, orders.fldidxfactory, orders.fldidxext, orders.fldidxtab, orders.fldidxcard, orders.fldidxuser, orders.fldidxaddress, orders.fldidxwoxapp, orders.fldord_nr, orders.fldord_price, orders.fldord_bool, orders.fldord_date, orders.fldord_dbl, orders.fldord_txt"
"                    ->  Append  (cost=0.41..83.71 rows=51 width=466) (actual time=0.079..0.189 rows=17 loops=1)"
"                          ->  Index Scan using orders_pkey on orders  (cost=0.41..41.56 rows=17 width=558) (actual time=0.077..0.125 rows=17 loops=1)"
"                                Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                          ->  Index Scan using orders_pkey on orders orders_1  (cost=0.29..27.15 rows=17 width=553) (actual time=0.034..0.035 rows=0 loops=1)"
"                                Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                          ->  Bitmap Heap Scan on orders orders_2  (cost=3.70..14.23 rows=17 width=466) (actual time=0.024..0.024 rows=0 loops=1)"
"                                Recheck Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                                ->  Bitmap Index Scan on orders_pkey  (cost=0.00..3.69 rows=17 width=0) (actual time=0.019..0.019 rows=0 loops=1)"
"                                      Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"Planning Time: 2.351 ms"
"JIT:"
"  Functions: 37"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 12.566 ms, Inlining 314.915 ms, Optimization 303.609 ms, Emission 219.614 ms, Total 850.704 ms"
"Execution Time: 26544.742 ms"

...

Рейтинг:

0 / 0

29.02.2020, 17:29

| Ответить | Цитировать | Написать

Разбиение на схемы

#39932551

Squamis

Гость

А с выключенным JIT + VIEW

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.

"QUERY PLAN"
"Sort  (cost=413559.80..413565.98 rows=2475 width=24) (actual time=36928.091..36928.101 rows=239 loops=1)"
"  Sort Key: products.fldidxprd"
"  Sort Method: quicksort  Memory: 43kB"
"  ->  Hash Join  (cost=412485.79..413420.29 rows=2475 width=24) (actual time=36915.676..36928.041 rows=239 loops=1)"
"        Hash Cond: (products.fldidxord = ord.fldidxord)"
"        ->  Unique  (cost=412292.27..413068.59 rows=9704 width=680) (actual time=35760.009..36854.885 rows=910990 loops=1)"
"              ->  Sort  (cost=412292.27..412316.53 rows=9704 width=680) (actual time=35760.008..36440.721 rows=910990 loops=1)"
"                    Sort Key: products.fldidxprd, products.fldidxprdp, products.fldpg_user, products.fldpg_date, products.fldidxsync, products.fldidxfactory, products.fldidxord, products.fldidxhdr, products.fldidxconstrtype, products.fldidxplantunit, products.fldidxware, products.fldidxsystemp, products.fldidxsystemf, products.fldidxcolor1, products.fldidxcolor2, products.fldidxsashside, products.fldprd_nn, products.fldprd_dim, products.fldprd_pir, products.fldprd_cell, products.fldprd_price, products.fldprd_date, products.fldprd_bool, products.fldprd_ant2_dates, products.fldprd_ant2_dist, products.fldprd_ant3_dates, products.fldprd_ant3_dist, products.fldprd_txt, products.fldprd_qty, products.fldprd_scan, products.fldidxgasket"
"                    Sort Method: external merge  Disk: 1371304kB"
"                    ->  Append  (cost=1000.00..408730.65 rows=9704 width=680) (actual time=0.948..28794.909 rows=910990 loops=1)"
"                          ->  Gather  (cost=1000.00..58542.60 rows=1349 width=1597) (actual time=0.948..91.028 rows=126476 loops=1)"
"                                Workers Planned: 2"
"                                Workers Launched: 2"
"                                ->  Parallel Seq Scan on products  (cost=0.00..57407.70 rows=562 width=1597) (actual time=0.043..109.768 rows=42159 loops=3)"
"                                      Filter: (fldidxprd = fldidxprdp)"
"                                      Rows Removed by Filter: 47742"
"                          ->  Gather  (cost=1000.00..350031.12 rows=8354 width=1572) (actual time=17.298..28639.700 rows=784514 loops=1)"
"                                Workers Planned: 2"
"                                Workers Launched: 2"
"                                ->  Parallel Seq Scan on products products_1  (cost=0.00..348195.72 rows=3481 width=1572) (actual time=9.066..29505.148 rows=261505 loops=3)"
"                                      Filter: (fldidxprd = fldidxprdp)"
"                                      Rows Removed by Filter: 295405"
"                          ->  Seq Scan on products products_2  (cost=0.00..11.38 rows=1 width=680) (actual time=0.008..0.008 rows=0 loops=1)"
"                                Filter: (fldidxprd = fldidxprdp)"
"        ->  Hash  (cost=192.88..192.88 rows=51 width=8) (actual time=0.427..0.431 rows=17 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"              ->  Subquery Scan on ord  (cost=191.86..192.88 rows=51 width=8) (actual time=0.413..0.420 rows=17 loops=1)"
"                    ->  HashAggregate  (cost=191.86..192.37 rows=51 width=466) (actual time=0.413..0.418 rows=17 loops=1)"
"                          Group Key: orders.fldidxord, orders.fldidxordp, orders.fldpg_user, orders.fldpg_date, orders.fldidxsync, orders.fldidxfactory, orders.fldidxext, orders.fldidxtab, orders.fldidxcard, orders.fldidxuser, orders.fldidxaddress, orders.fldidxwoxapp, orders.fldord_nr, orders.fldord_price, orders.fldord_bool, orders.fldord_date, orders.fldord_dbl, orders.fldord_txt"
"                          ->  Append  (cost=0.29..189.57 rows=51 width=466) (actual time=0.051..0.141 rows=17 loops=1)"
"                                ->  Index Scan using orders_pkey on orders  (cost=0.29..86.72 rows=17 width=558) (actual time=0.049..0.097 rows=17 loops=1)"
"                                      Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                                ->  Index Scan using orders_pkey on orders orders_1  (cost=0.42..87.08 rows=17 width=553) (actual time=0.036..0.036 rows=0 loops=1)"
"                                      Index Cond: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"                                ->  Seq Scan on orders orders_2  (cost=0.00..15.00 rows=17 width=466) (actual time=0.005..0.005 rows=0 loops=1)"
"                                      Filter: (fldidxord = ANY ('{167487232,167490558,167531055,167533507,167547630,167550026,167550133,167552398,167552889,167555007,167555015,167556706,167570797,167571075,167571753,167582189,167582887}'::bigint[]))"
"Planning Time: 73.277 ms"
"Execution Time: 37548.356 ms"

...

Рейтинг:

0 / 0

29.02.2020, 17:38

| Ответить | Цитировать | Написать

Разбиение на схемы

#39932555

Squamis

Гость

Наверное нашел причину.
У меня во VIEW было соединение через union, а не через union all.
Теперь летает

...

Рейтинг:

0 / 0

29.02.2020, 18:05

| Ответить | Цитировать | Написать

6 сообщений из 6, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Разбиение на схемы

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&fpage=31&tid=1994796]:	0ms
get settings:	10ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	48ms
get topic data:	13ms
get forum data:	2ms
get page messages:	77ms
get tp. blocked users:	2ms
others:	315ms

total:	488ms