Почему в greenplum многораздельная таблица использует объединение nestedloop, а однораздельная таблица использует хеш-соединение

Question

Я создал две таблицы (A, B) с 100 столбцами, один и тот же DDL за исключением того, что B был разбит

CREATE TABLE A (
  id integer, ......, col integer,
  CONSTRAINT A_pkey PRIMARY KEY (id))
WITH (OIDS = FALSE)
TABLESPACE pg_default
DISTRIBUTED BY (id);

CREATE TABLE B (
  id integer, ......, col integer,
  CONSTRAINT B_pkey PRIMARY KEY (id))
WITH (OIDS = FALSE)
TABLESPACE pg_default
DISTRIBUTED BY (id)
PARTITION BY RANGE(id) 
  (START (1) END (2100000) EVERY (500000), 
   DEFAULT PARTITION extra 
  );

и импортировал те же данные (2000000 строк) в A и B. Затем я выполнил sql с A и B отдельно:

UPDATE A a SET a.col = c.col from C c where c.id = a.id
UPDATE B b SET b.col = c.col from C c where c.id = b.id

В результате A через минуту преуспел, но B занял много времени, и, наконец, произошла ошибка памяти:

ERROR:  Canceling query because of high VMEM usage.

Итак, я проверил EXPLAIN двух sql, я обнаружил, что A использовал Hash Join, но B использовал Nested-Loop Join.

Есть ли какая-то причина, почему в секционированной таблице используется объединение с вложенным циклом? Нет ли необходимости в использовании Greenplum для разделения таблиц при хранении миллионов данных?

Answer 1

Вы делаете несколько вещей, которые не рекомендуются, что может объяснить, почему вы видите вложенные циклы.

1. Избегайте ОБНОВЛЕНИЙ заявления в целом. Старая версия строки остается на диске плюс новая версия строки. Таким образом, если вы обновляете всю таблицу, вы фактически удваиваете физический размер используемого диска.
2. Я никогда не видел таблицы кучи, используемой для многораздельной таблицы. В Greenplum вы должны использовать в основном таблицы "Только добавление", особенно в больших таблицах, таких как многораздельная таблица.
3. Вы разделяете по ключу распределения. Это не рекомендуется и совсем не выгодно. Планируете ли вы фильтровать по диапазону идентификаторов? Это довольно необычно. Если это так, замените ключ распространения на что-то другое.
4. Я думал, что Pivotal отключил возможность создания первичного ключа на многораздельной таблице. Одно время это было запрещено. Я бы не рекомендовал вам создавать какие-либо первичные ключи, поскольку они просто занимают место, а оптимизатор обычно их не использует.

После исправления этих элементов я не могу воспроизвести проблему с вложенными циклами. Я использую версию 5.0.0 тоже.

    drop table if exists a;
    drop table if exists b;
    drop table if exists c;
    CREATE TABLE A 
    (id integer, col integer, mydate timestamp)
    WITH (appendonly=true)
    DISTRIBUTED BY (id);

    CREATE TABLE B 
    (id integer, col integer, mydate timestamp)
    WITH (appendonly=true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE(mydate) 
      (START ('2015-01-01'::timestamp) END ('2018-12-31'::timestamp) EVERY ('1 month'::interval), 
       DEFAULT PARTITION extra 
      );

    create table c
    (id integer, col integer, mydate timestamp)
    distributed by (id);

    insert into a
    select i, i+10, '2015-01-01'::timestamp + '1 day'::interval*i
    from generate_series(0, 2000) as i
    where '2015-01-01'::timestamp + '1 day'::interval*i < '2019-01-01'::timestamp;

    insert into b
    select i, i+10, '2015-01-01'::timestamp + '1 day'::interval*i
    from generate_series(0, 2000) as i
    where '2015-01-01'::timestamp + '1 day'::interval*i < '2019-01-01'::timestamp;

    insert into c
    select i, i+10, '2015-01-01'::timestamp + '1 day'::interval*i
    from generate_series(0, 2000) as i
    where '2015-01-01'::timestamp + '1 day'::interval*i < '2019-01-01'::timestamp;


    explain UPDATE A a SET col = c.col from C c where c.id = a.id;
    /*
    "Update  (cost=0.00..862.13 rows=1 width=1)"
    "  ->  Result  (cost=0.00..862.00 rows=1 width=34)"
    "        ->  Split  (cost=0.00..862.00 rows=1 width=30)"
    "              ->  Hash Join  (cost=0.00..862.00 rows=1 width=30)"
    "                    Hash Cond: public.a.id = c.id"
    "                    ->  Table Scan on a  (cost=0.00..431.00 rows=1 width=26)"
    "                    ->  Hash  (cost=431.00..431.00 rows=1 width=8)"
    "                          ->  Table Scan on c  (cost=0.00..431.00 rows=1 width=8)"
    "Settings:  optimizer_join_arity_for_associativity_commutativity=18"
    "Optimizer status: PQO version 2.42.0"
    */

    explain UPDATE B b SET col = c.col from C c where c.id = b.id;
    /*
    "Update  (cost=0.00..862.13 rows=1 width=1)"
    "  ->  Result  (cost=0.00..862.00 rows=1 width=34)"
    "        ->  Split  (cost=0.00..862.00 rows=1 width=30)"
    "              ->  Hash Join  (cost=0.00..862.00 rows=1 width=30)"
    "                    Hash Cond: public.a.id = c.id"
    "                    ->  Table Scan on a  (cost=0.00..431.00 rows=1 width=26)"
    "                    ->  Hash  (cost=431.00..431.00 rows=1 width=8)"
    "                          ->  Table Scan on c  (cost=0.00..431.00 rows=1 width=8)"
    "Settings:  optimizer_join_arity_for_associativity_commutativity=18"
    "Optimizer status: PQO version 2.42.0"

    */