партицированная таблица vs обычная / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

партицированная таблица vs обычная

#38800021

pg_нуб

Гость

Добрый день

Делаю маленький тест. Есть два варианта таблицы LINEITEM из TPC-H :

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

CREATE TABLE LINEITEM
 (L_ORDERKEY       BIGINT          NOT NULL,
  L_PARTKEY        BIGINT          NOT NULL,
  L_SUPPKEY        BIGINT          NOT NULL,
  L_LINEBIGINT     INTEGER         NOT NULL,
  L_QUANTITY       BIGINT          NOT NULL,
  L_EXTENDEDPRICE  BIGINT          NOT NULL,
  L_DISCOUNT       BIGINT          NOT NULL,
  L_TAX            BIGINT          NOT NULL,
  L_RETURNFLAG     CHARACTER(1)    NOT NULL,
  L_LINESTATUS     CHARACTER(1)    NOT NULL,
  L_SHIPDATE       DATE            NOT NULL,
  L_COMMITDATE     DATE            NOT NULL,
  L_RECEIPTDATE    DATE            NOT NULL,
  L_SHIPINSTRUCT   VARCHAR(25)     NOT NULL,
  L_SHIPMODE       VARCHAR(10)     NOT NULL,
  L_COMMENT        VARCHAR(44)     NOT NULL
 )

1. LINEITEM - обычная с индексом по l_shipdate
2. LINEITEM_PART - партицированная по годам (6 партиций-таблиц по годам, на каждой индекс по l_shipdate + констрейнты, функция, триггер и т.д.)

В обе таблицы залито 300M записей

Запускаю q1.sql из бенчмарка :

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

SELECT
       L_RETURNFLAG,
       L_LINESTATUS,
       SUM(L_QUANTITY) AS SUM_QTY,
       SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,
        SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS SUM_DISC_PRICE,
        SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE,
        AVG(L_QUANTITY) AS AVG_QTY,
        AVG(L_EXTENDEDPRICE) AS AVG_PRICE,
        AVG(L_DISCOUNT) AS AVG_DISC,
        COUNT(*) AS COUNT_ORDER
FROM
           LINEITEM_PART
        --LINEITEM
WHERE
        L_SHIPDATE BETWEEN '1992-01-01' AND '1992-12-31'
GROUP BY
        L_RETURNFLAG,
        L_LINESTATUS
ORDER BY
        L_RETURNFLAG,
        L_LINESTATUS;

По обычной таблице рантайм 2 минуты, по партицированной 4 минуты :(

План по обычной таблице :

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

 QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Sort  (cost=3490130.32..3490130.32 rows=1 width=36)
   Sort Key: l_returnflag, l_linestatus
   ->  HashAggregate  (cost=3490130.29..3490130.31 rows=1 width=36)
         ->  Index Scan using idx1_lineitem on lineitem  (cost=0.57..1945411.81 rows=38617962 width=36)
               Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
(5 rows)

По партицированной :

Код: sql

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

 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Sort  (cost=11856557.71..11856557.72 rows=6 width=36)
   Sort Key: lineitem_part.l_returnflag, lineitem_part.l_linestatus
   ->  HashAggregate  (cost=11856557.53..11856557.63 rows=6 width=36)
         ->  Append  (cost=0.00..10318808.76 rows=38443719 width=36)
               ->  Seq Scan on lineitem_part  (cost=0.00..10318800.60 rows=38443718 width=36)
                     Filter: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
               ->  Index Scan using idx1_lineitem_1992 on lineitem_1992 (cost=0.14..8.16 rows=1 width=48)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
(8 rows)

Cудя по плану выбирается одна партиция lineitem_1992, а рантайм хуже в два раза.

ANALYZE по обеим таблицам делал.

Куда копать ?

...

Рейтинг:

0 / 0

09.11.2014, 20:21:33

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

партицированная таблица vs обычная

#38800058

какбе

Гость

pg_нуб,

Код: sql

Seq Scan on lineitem_part  (cost=0.00..10318800.60 rows=38443718 width=36)

какбе говорит нам, что все записи-писи в головной таблце-це присуцвтуют, с т.з. планировщика (а индекса над ней похоже нет) а в партции, напротив, ожЫдаецца 1 запись-пись

приведите explain analyze -- посмотрим, так ли это-то.

...

Рейтинг:

0 / 0

09.11.2014, 20:46:05

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

партицированная таблица vs обычная

#38800081

pg_нуб

Гость

какбе,

сделал EXPLAIN ANALYZE :

Код: sql

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

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=11856557.71..11856557.72 rows=6 width=36) (actual time=237390.917..237390.917 rows=2 loops=1)
   Sort Key: lineitem_part.l_returnflag, lineitem_part.l_linestatus
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=11856557.53..11856557.63 rows=6 width=36) (actual time=237390.828..237390.830 rows=2 loops=1)
         ->  Append  (cost=0.00..10318808.76 rows=38443719 width=36) (actual time=58666.769..136532.096 rows=38008923 loops=1)
               ->  Seq Scan on lineitem_part  (cost=0.00..10318800.60 rows=38443718 width=36) (actual time=58666.763..133414.978 rows=38008923 loops=1)
                     Filter: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
                     Rows Removed by Filter: 261996888
               ->  Index Scan using idx1_lineitem_1992 on lineitem_1992  (cost=0.14..8.16 rows=1 width=48) (actual time=0.023..0.023 rows=0 loops=1)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
 Total runtime: 237391.204 ms
(11 rows)

Создал индекс по мастер-таблице :

Код: sql

CREATE INDEX IDX1_LINEITEM_PART ON LINEITEM_PART (L_SHIPDATE ASC) TABLESPACE tbspace1;

Опять сделал EXPLAIN ANALYZE :

Код: sql

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

cat q1.out
                                                                                 QUERY PLAN                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3476497.98..3476498.00 rows=6 width=36) (actual time=123506.378..123506.379 rows=2 loops=1)
   Sort Key: lineitem_part.l_returnflag, lineitem_part.l_linestatus
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=3476497.80..3476497.90 rows=6 width=36) (actual time=123506.304..123506.306 rows=2 loops=1)
         ->  Append  (cost=0.57..1937607.08 rows=38472268 width=36) (actual time=0.023..21352.071 rows=38008923 loops=1)
               ->  Index Scan using idx1_lineitem_part on lineitem_part  (cost=0.57..1937598.91 rows=38472267 width=36) (actual time=0.022..18098.461 rows=38008923 loops=1)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
               ->  Index Scan using idx1_lineitem_1992 on lineitem_1992  (cost=0.14..8.16 rows=1 width=48) (actual time=5.886..5.886 rows=0 loops=1)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
 Total runtime: 123506.523 ms
(10 rows)

Теперь рантайм уровнялся с обычной таблицей.

...

Рейтинг:

0 / 0

09.11.2014, 21:29:04

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

партицированная таблица vs обычная

#38800105

какбе

Гость

pg_нубкакбе,

сделал EXPLAIN ANALYZE :

Код: sql

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

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=11856557.71..11856557.72 rows=6 width=36) (actual time=237390.917..237390.917 rows=2 loops=1)
   Sort Key: lineitem_part.l_returnflag, lineitem_part.l_linestatus
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=11856557.53..11856557.63 rows=6 width=36) (actual time=237390.828..237390.830 rows=2 loops=1)
         ->  Append  (cost=0.00..10318808.76 rows=38443719 width=36) (actual time=58666.769..136532.096 rows=38008923 loops=1)
               ->  Seq Scan on lineitem_part  (cost=0.00..10318800.60 rows=38443718 width=36) (actual time=58666.763..133414.978 rows=38008923 loops=1)
                     Filter: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
                     Rows Removed by Filter: 261996888
               ->  Index Scan using idx1_lineitem_1992 on lineitem_1992  (cost=0.14..8.16 rows=1 width=48) (actual time=0.023..0.023 rows=0 loops=1)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
 Total runtime: 237391.204 ms
(11 rows)

Создал индекс по мастер-таблице :

Код: sql

CREATE INDEX IDX1_LINEITEM_PART ON LINEITEM_PART (L_SHIPDATE ASC) TABLESPACE tbspace1;

Опять сделал EXPLAIN ANALYZE :

Код: sql

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

cat q1.out
                                                                                 QUERY PLAN                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3476497.98..3476498.00 rows=6 width=36) (actual time=123506.378..123506.379 rows=2 loops=1)
   Sort Key: lineitem_part.l_returnflag, lineitem_part.l_linestatus
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=3476497.80..3476497.90 rows=6 width=36) (actual time=123506.304..123506.306 rows=2 loops=1)
         ->  Append  (cost=0.57..1937607.08 rows=38472268 width=36) (actual time=0.023..21352.071 rows=38008923 loops=1)
               ->  Index Scan using idx1_lineitem_part on lineitem_part  (cost=0.57..1937598.91 rows=38472267 width=36) (actual time=0.022..18098.461 rows=38008923 loops=1)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
               ->  Index Scan using idx1_lineitem_1992 on lineitem_1992  (cost=0.14..8.16 rows=1 width=48) (actual time=5.886..5.886 rows=0 loops=1)
                     Index Cond: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
 Total runtime: 123506.523 ms
(10 rows)

Теперь рантайм уровнялся с обычной таблицей.

все правильно -- все записи у вас лежат в родительской таблице, а в партициях -- уй ночевал, проститезаржание

рисовать партицирующие триггера за вас пупкин буит ?
или вы думали, шо в постгресе партицирование искаропки ?

PS сделайте

Код: sql

SELECT COUNT(1) FROM ONLY lineitem_part

- может быть что и соображать начнёте

...

Рейтинг:

0 / 0

09.11.2014, 22:05:57

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

партицированная таблица vs обычная

#38800108

какбе

Гость

Просто напоминаю, что вы пользуетесь не "партицированием" (как таковым), а "наследованием".

"наследование" же -- это такая довольно мерворождённая пж фича, которую было трудно к чему-либо дельному приспособить, (т.к. индексов , уникью и т.п. констрайнтов по сквозным "семействам" в пж так и не реализовали) -- и вот из этого, проститезаржание, чумодана без ручки оказалось модно делать партицирование. но всё -- исключительно руками. (с кучей попутных бубнов и траблов).

...

Рейтинг:

0 / 0

09.11.2014, 22:14:33

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

партицированная таблица vs обычная

#38801916

pg_нуб

Гость

какбе,

При создании схемы партицирования упал create trigger... и все действительно легло в мастер-таблицу

Пересоздал нормально схему. Сделал ANALYZE и CLUSTER по всем таблицам-партициям.

Теперь explain analyze выдает :

Код: sql

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

 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2828180.04..2828180.06 rows=6 width=36) (actual time=120231.786..120231.786 rows=2 loops=1)
   Sort Key: lineitem_part.l_returnflag, lineitem_part.l_linestatus
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=2828179.86..2828179.97 rows=6 width=36) (actual time=120231.704..120231.707 rows=2 loops=1)
         ->  Append  (cost=0.00..1307822.86 rows=38008925 width=36) (actual time=0.009..18395.441 rows=38008923 loops=1)
               ->  Seq Scan on lineitem_part  (cost=0.00..0.00 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
               ->  Seq Scan on lineitem_1992  (cost=0.00..1307822.86 rows=38008924 width=36) (actual time=0.007..15093.602 rows=38008923 loops=1)
                     Filter: ((l_shipdate >= '1992-01-01'::date) AND (l_shipdate <= '1992-12-31'::date))
 Total runtime: 120231.982 ms
(10 rows)

те.же 120 сек., что и на непартицированной

...

Рейтинг:

0 / 0

11.11.2014, 12:55:37

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

партицированная таблица vs обычная

#38801988

какбе

Гость

pg_нуб,
и это -- правильно. основные затарты -- те же 38008923 чтений произвольным доступом, что и в случае непартицированно.

-- затраты на планирование растут с числом партиций. И весьма.
++ затраты на чтение индексов -- немного падают.

выигрыши будут в запросах не вдоль индексов, требующих фулл-скана партиции (прочитать партицию дешевле, чем всю таблицу). Или в других похожих случаях.

основная задача -- ускорить DROP|TRUNCATE[вместо DELETE], REINDEX, vacuum отдельной партиции и т.п.
но в случае DROP "на ходу"-- имеем попаданчик
http://www.dbtalk.net/mailing-database-pgsql-bugs/bugs-expected-behaviour-ddl-query-640124.html
, так и не решённый гурьями по сей день (в общем -- усё плёхо).

ЗЫ сплошь и рядом будем иметь попаданец при плане типа {no merge} APPEND [index-scans] + Sort + LIMIT N -- там люди не знают, что достаточно проаппендить M*{ORDER BY .... LIMIT N} и взять после сорта опять {ORDER BY .... LIMIT N} -- им это недоступно в принципе-- будут проаппенжены полные индекс-сканы, вся хрень просортирована, и только потом применён лимит

...

Рейтинг:

0 / 0

11.11.2014, 13:22:32

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

партицированная таблица vs обычная

#38802758

какбе

Гость

выше, какбе, наврал

на новый план я и не посмотрел -- там у вас таки фул скан пратиции, и занимает он 15 секунд.
Индекс скан по полной табле занимал 18 секунд -- т.е. 3 секунды вы выиграли.

а вот где сидит от 15--18 до 120 -- в ваших планах не вижу.
Hash aggregate начинается якобы уже со 120, а аппенд перед ним кончается 18-ю. могабыть у кого глаз позрячей -- найдут

...

Рейтинг:

0 / 0

11.11.2014, 20:29:55

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

партицированная таблица vs обычная

#38802816

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

pg_нуб,

а покажите вывод `EXPLAIN (analyze, buffers, verbose)` -- поможет прояснить ситуацию с HashAggregate.

...

Рейтинг:

0 / 0

11.11.2014, 21:40:13

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

партицированная таблица vs обычная

#38802838

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

какбевыше, какбе, наврал

на новый план я и не посмотрел -- там у вас таки фул скан пратиции, и занимает он 15 секунд.
Индекс скан по полной табле занимал 18 секунд -- т.е. 3 секунды вы выиграли.

а вот где сидит от 15--18 до 120 -- в ваших планах не вижу.
Hash aggregate начинается якобы уже со 120, а аппенд перед ним кончается 18-ю. могабыть у кого глаз позрячей -- найдут

собственно на вычисление всей этой пачки аггрегатов через hashagg эти самые 102 секунды и уходят и вряд ли это станет быстрее от партиционирования.

PS: в выводе
(actual time=120231.704..120231.707 rows=2 loops=1)
первое число - когда выдали первую строку результата (а не когда начали считать)
второе число - когда выдали последнюю строку результата...

не ясно какое время вы хотите получить для сложного пересчета 40M строк... помоему очень приличный результат

...

Рейтинг:

0 / 0

11.11.2014, 22:29:35

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

партицированная таблица vs обычная

#38804109

Artemiy

Участник

Сообщения: 1 318

Рейтинг: 0 / 0

Ребята, а вы не в курсе нет ли планов сделать нормальное партиционирование "из коробки", как в оракле, например.

...

Рейтинг:

0 / 0

12.11.2014, 21:01:50

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

партицированная таблица vs обычная

#38804110

какбе

Гость

ArtemiyРебята, а вы не в курсе нет ли планов сделать нормальное партиционирование "из коробки", как в оракле, например.если верить
Голосуем за новые фичи PG

то вроде бы давно в процессе допиливания.
но дело это кропотливое

...

Рейтинг:

0 / 0

12.11.2014, 21:13:01

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

партицированная таблица vs обычная

#38804282

rovan

Гость

Вот здесь идет активное обсуждение того, каким должно быть новое партиционирование. У меня сложилось ощущение, что оно будет не на наследовании. А так - поживём, увидим.

...

Рейтинг:

0 / 0

13.11.2014, 08:51:34

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

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

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

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

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

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

start [/forum/topic.php?fid=53&msg=38800058&tid=1998360]:	0ms
get settings:	4ms
get forum list:	10ms
check forum access:	3ms
check topic access:	3ms
track hit:	170ms
get topic data:	7ms
get forum data:	2ms
get page messages:	44ms
get tp. blocked users:	1ms
others:	194ms

total:	438ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы