Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Добрый день, подскажите, плиз, можно ли из таблицы tb1
Картинка с другого сайта.
+

DROP TABLE public.tb1;
CREATE TABLE public.tb1
(
    idtb1 serial,
    n1 numeric,
    n2 numeric,
    CONSTRAINT tb1_pkey PRIMARY KEY (idtb1)
)
TABLESPACE pg_default;

insert into tb1(n1, n2) values
(2, 5),
(1, 3),
(8, 1),
(1, 3),
(5, 1),
(3,12),
(3, 2),
(1,12),
(8, 1);


select * from tb1 order by idtb1;


одним запросом, с параметром, например =1, получить:

3
8
5
12

или

3
12
8
5

К сообщению приложен файл. Размер - 12Kb
6 фев 21, 19:46    [22276441]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Alex_Wong,

можно ли уйти от UNION, т.к. таблица большая и важна скорость:
select distinct n2 from tb1 where n1 =1 UNION select distinct n1 from tb1 where n2 =1;
6 фев 21, 20:04    [22276453]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Alex_Wong,

как получить одним запросом массив с такой последовательностью элементов: [3, 8, 5, 12]
?
6 фев 21, 21:56    [22276506]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
vyegorov
Member

Откуда: Баньоло-ин-Пьяно
Сообщений: 1227
Alex_Wong,

select array_agg(distinct case when n1=1 then n2 else n1 end) from tb1 where (n1=1 or n2=1);
 array_agg
------------
 {3,5,8,12}
(1 row)
6 фев 21, 22:50    [22276523]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
vyegorov,

круто, спасибо
6 фев 21, 23:23    [22276535]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
vyegorov,

на 3 млн. записей
   select array_agg : ~ 270 ms
        vs
   select union     : ~ 340 ms

+

[img=]

+

[img=]


К сообщению приложен файл. Размер - 34Kb
7 фев 21, 00:20    [22276548]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Alex_Wong,

К сообщению приложен файл. Размер - 52Kb
7 фев 21, 00:21    [22276549]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4581
Alex_Wong,

Таки вам надо быстро?
Тогда надо именно через union делать
только убрать distinct конечно из обоих подзапросов

select n2 from tb1 where n1 =1 UNION select n1 from tb1 where n2 =1;


ну и индексы по n1 и n2 сделать конечно



без union + индексы быстро не получится.

Точнее я могу придумать запрос без union но он тоже для скорости индекс потребует:
create index tb1_key on tb1  using GIN ((array[n1, n2]));
set enable_seqscan to 0;
explain analyze select array_agg(distinct case when n1=1 then n2 else n1 end) from tb1 where array[1::numeric] <@ array[n1, n2];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=12.02..12.03 rows=1 width=32) (actual time=0.095..0.097 rows=1 loops=1)
-> Bitmap Heap Scan on tb1 (cost=8.00..12.01 rows=1 width=64) (actual time=0.039..0.044 rows=6 loops=1)
Recheck Cond: ('{1}'::numeric[] <@ ARRAY[n1, n2])
Heap Blocks: exact=1
-> Bitmap Index Scan on tb1_key (cost=0.00..8.00 rows=1 width=0) (actual time=0.025..0.026 rows=6 loops=1)
Index Cond: (ARRAY[n1, n2] @> '{1}'::numeric[])
Planning Time: 0.252 ms
Execution Time: 0.191 ms
(8 rows)

будет ли он быстрее чем 2 индекса + union - зависит от распределения ваших данных.

ps: я бы еще заметил что если вам надо БЫСТРО то использование numeric вместо integer - решение плохое.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

Сообщение было отредактировано: 7 фев 21, 00:51
7 фев 21, 00:55    [22276552]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Maxim Boguk,

спасибо за помощь и подсказки, select union на скринах выше делал без distinct
добавил в таблицу 100 млн
   select array_agg     : ~ 8062 ms
        vs
   select union         : ~ 11300 ms
        vs
   array[1::numeric]    : ~ 13800 ms 


пробую индексы:
create index tb1_key on tb1 using GIN ((array[n1, n2]));
set enable_seqscan to 0;

потом numeric -> integer
thanks

К сообщению приложен файл. Размер - 34Kb
7 фев 21, 01:26    [22276554]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4581
Alex_Wong,

у вас какой тип данных сейчас integer или numeric
если уже integer то естественно запрос надо писать как

select array_agg(distinct case when n1=1 then n2 else n1 end) from tb1 where array[1] <@ array[n1, n2];


для быстрого union вам нужны индексы по n1 и n2
а вы их не сделали... так union точнее условия n1=1 и n2=1 быстро работать не будут.


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

Сообщение было отредактировано: 7 фев 21, 01:25
7 фев 21, 01:30    [22276555]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Maxim Boguk,

пока делаю на numeric, но однозначно будет integer т.к. это ключи
на индексах ваш запрос лучший: от 30 ms до 120 ms на пяти запросах,

а вот select array_agg просел в два раза .. select union без изменений:
   select array_agg     : ~ 22000 ms
        vs
   select union         : ~ 11300 ms
        vs
   array[1::numeric]    : ~ (30 - 120) ms


К сообщению приложен файл. Размер - 43Kb
7 фев 21, 01:50    [22276556]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
Alex_Wong
Member

Откуда:
Сообщений: 176
Maxim Boguk, vyegorov,

автор
ps: я бы еще заметил что если вам надо БЫСТРО то использование numeric вместо integer - решение плохое


Вы правы, убедился на штатных настройках postgresql-12, винт hdd7200wd, ~103млн записей с индексами по fk_ключам на integer:
  explain analyze select array_agg(distinct case when n1=88 then n2 else n1 end) from tb2 where array[88] <@ array[n1, n2];
  холодный    2ой         3й         4й         5й         6й
  120.775     14.951      15.060     14.751     14.702     14.828 


  EXPLAIN ANALYZE select n2 from tb2 where array[88] <@ array[n1, n2] UNION select n1 from tb2 where array[88] <@ array[n1, n2];
  1ый          2ой         3й         4й         5й         6й
  32.359       33.215      34.817     31.230     32.074     32.820     


union vs array_agg : проседает в два раза, при равных прочих,
если правильно понимаю, то на большой таблице с любым распределением данных (на ssd и правильных настройках), -
расклад в пользу union не изменится,

спасибо
7 фев 21, 12:53    [22276608]     Ответить | Цитировать Сообщить модератору
 Re: получить данные из двух столбцов таблицы одним запросом  [new]
vyegorov
Member

Откуда: Баньоло-ин-Пьяно
Сообщений: 1227
Alex_Wong,

Не надо картинками EXPLAIN-ы вставлять, текстом с форматированием.
7 фев 21, 13:45    [22276630]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить