получить данные из двух столбцов таблицы одним запросом / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

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

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

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

получить данные из двух столбцов таблицы одним запросом

#40042672

Alex_Wong

Гость

Добрый день, подскажите, плиз, можно ли из таблицы tb1

Код: plsql

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

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

...

Рейтинг:

0 / 0

06.02.2021, 19:46

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

получить данные из двух столбцов таблицы одним запросом

#40042683

Alex_Wong

Гость

Alex_Wong,

можно ли уйти от UNION, т.к. таблица большая и важна скорость:

Код: plsql

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

...

Рейтинг:

0 / 0

06.02.2021, 20:04

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

получить данные из двух столбцов таблицы одним запросом

#40042715

Alex_Wong

Гость

Alex_Wong,

как получить одним запросом массив с такой последовательностью элементов: [3, 8, 5, 12]
?

...

Рейтинг:

0 / 0

06.02.2021, 21:56

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

получить данные из двух столбцов таблицы одним запросом

#40042726

vyegorov

Участник

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

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

Рейтинг: 0 / 0

Alex_Wong,

Код: sql

1.
2.
3.
4.
5.

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)

...

Рейтинг:

0 / 0

06.02.2021, 22:50

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

получить данные из двух столбцов таблицы одним запросом

#40042733

Alex_Wong

Гость

vyegorov,

круто, спасибо

...

Рейтинг:

0 / 0

06.02.2021, 23:23

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

получить данные из двух столбцов таблицы одним запросом

#40042740

Alex_Wong

Гость

vyegorov,

на 3 млн. записей

Код: plsql

1.
2.
3.

   select array_agg : ~ 270 ms
        vs
   select union     : ~ 340 ms

[img=]

...

Рейтинг:

0 / 0

07.02.2021, 00:20

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

получить данные из двух столбцов таблицы одним запросом

#40042741

Alex_Wong

Гость

Alex_Wong,

...

Рейтинг:

0 / 0

07.02.2021, 00:21

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

получить данные из двух столбцов таблицы одним запросом

#40042744

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alex_Wong,

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

Код: plsql

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

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

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

Точнее я могу придумать запрос без union но он тоже для скорости индекс потребует:

Код: plaintext

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

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

...

Рейтинг:

0 / 0

07.02.2021, 00:55

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

получить данные из двух столбцов таблицы одним запросом

#40042745

Alex_Wong

Гость

Maxim Boguk,

спасибо за помощь и подсказки, select union на скринах выше делал без distinct
добавил в таблицу 100 млн

Код: plsql

1.
2.
3.
4.
5.

   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

...

Рейтинг:

0 / 0

07.02.2021, 01:26

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

получить данные из двух столбцов таблицы одним запросом

#40042746

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alex_Wong,

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

Код: plsql

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

...

Рейтинг:

0 / 0

07.02.2021, 01:30

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

получить данные из двух столбцов таблицы одним запросом

#40042747

Alex_Wong

Гость

Maxim Boguk,

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

а вот select array_agg просел в два раза .. select union без изменений:

Код: plsql

1.
2.
3.
4.
5.

   select array_agg     : ~ 22000 ms
        vs
   select union         : ~ 11300 ms
        vs
   array[1::numeric]    : ~ (30 - 120) ms

...

Рейтинг:

0 / 0

07.02.2021, 01:50

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

получить данные из двух столбцов таблицы одним запросом

#40042779

Alex_Wong

Гость

Maxim Boguk, vyegorov,

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

Вы правы, убедился на штатных настройках postgresql-12, винт hdd7200wd, ~103млн записей с индексами по fk_ключам на integer:

Код: plsql

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

  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 не изменится,

спасибо

...

Рейтинг:

0 / 0

07.02.2021, 12:53

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

получить данные из двух столбцов таблицы одним запросом

#40042788

vyegorov

Участник

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

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

Рейтинг: 0 / 0

Alex_Wong,

Не надо картинками EXPLAIN-ы вставлять, текстом с форматированием.

...

Рейтинг:

0 / 0

07.02.2021, 13:45

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&fpage=16&tid=1994209]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	40ms
get topic data:	14ms
get forum data:	2ms
get page messages:	54ms
get tp. blocked users:	2ms
others:	13ms

total:	159ms