Форум программистов, компьютерный форум, киберфорум
PostgreSQL
Форум программистов и сисадминов Киберфорум > Форум программистов > Базы данных > PostgreSQL
Войти
Регистрация
Восстановить пароль
Блоги Сообщество Поиск Заказать работу  
 
2 / 2 / 1
Регистрация: 16.04.2022
Сообщений: 1,011

Долгий запрос

31.07.2024, 22:36. Показов 635. Ответов 8
Метки нет (Все метки)
Студворк — интернет-сервис помощи студентам
Здравствуйте!

Есть одна большая таблица test.myTable3 (не нормализованная) и две маленькие tempTable1 и tempTable2. Идет соединение с группировкой.

Имеется такой запрос.
Кликните здесь для просмотра всего текста
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
42
43
44
45
46
47
48
49
50
51
52
53

EXPLAIN(analyze,buffers)
SELECT
    t1."column4",
    t1."column3",
    t2."column1",
    t2."column2",
    SUM(t1."column26") AS "column26",
    t1."column25",
    t1."column38",
    CASE WHEN t1."column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END,
    t1."column24",
    t3."column2",
    t1."column35"
FROM
    test.myTable3 t1
LEFT JOIN
    tempTable1 t2
ON
    LOWER(t1."column49") = LOWER(t1."column49")
    AND
    LOWER(t1."column50") = LOWER(t1."column50")
LEFT JOIN
    tempTable2 t3
ON
    LOWER(t1."column37") = LOWER(t3."column1")
WHERE
    NOT(
        t3."column3" IS NULL
        AND
        t3."column4" IS NULL
    )
    AND
    t1."column3" = 'aaaa'
    AND
    t1."column58" = 2024 --Не статичное условие
    AND
    t1."column4" >= 202001
    AND
    t1."column9" = 'CCCC'
    AND
    t1."column30" = 'bbbbbbbb bbbbbbb' --not t1."column30" = 'aaaaaaa aaaaaaaa'
GROUP BY 
    t1."column4",
    t1."column3",
    t3."column2",
    t2."column1",
    t2."column2",
    t1."column38",
    t1."column24",
    t1."column25",
    t1."column35",
    CASE WHEN t1."column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END--t1."column37" 
;

Для эксперимента пока созданы два индекса для большой таблицы. Для маленьких таблиц индексы не создавались.
SQL
1
2
3
4
5
6
7
8

CREATE INDEX myTable3_idx2 ON test.myTable3 USING btree 
("column58", (CASE WHEN "column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END),(LOWER("column37")), (LOWER("column49")),(LOWER("column50")))
WHERE "column3" = 'aaaa' AND "column4" > 202001 AND "column9" = 'CCCC' AND  "column30" = 'bbbbbbbb bbbbbbb';
 
CREATE INDEX myTable3_idx3 ON test.myTable3 USING btree 
("column3", "column4", "column24", "column25", "column35", "column38", "column58", (CASE WHEN "column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END),
(LOWER("column37")), (LOWER("column49")), (LOWER("column50")))
WHERE "column9" = 'CCCC' AND "column30" = 'bbbbbbbb bbbbbbb';
План запроса
Кликните здесь для просмотра всего текста
Code
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

GroupAggregate  (cost=13482790.42..13615059.31 rows=3112209 width=1318) (actual time=469057.807..490824.767 rows=70200 loops=1)
  Group Key: t1.column4, t3.column2, t2.column1, t2.column2, t1.column38, t1.column24, t1.column25, t1.column35, (CASE WHEN ((t1.column37)::text = 'aaaa'::text) THEN 'aaa'::text ELSE 'aaaaaa'::text END)
  Buffers: shared read=947721, local hit=3, temp read=109780 written=109789
  ->  Sort  (cost=13482790.42..13490570.95 rows=3112209 width=1293) (actual time=469057.467..483885.877 rows=8845200 loops=1)
        Sort Key: t1.column4, t3.column2, t2.column1, t2.column2, t1.column38, t1.column24, t1.column25, t1.column35, (CASE WHEN ((t1.column37)::text = 'aaaa'::text) THEN 'aaa'::text ELSE 'aaaaaa'::text END)
        Sort Method: external merge  Disk: 878240kB
        Buffers: shared read=947721, local hit=3, temp read=109780 written=109789
        ->  Nested Loop Left Join  (cost=320052.61..12018818.60 rows=3112209 width=1293) (actual time=4564.129..392971.570 rows=8845200 loops=1)
              Join Filter: ((lower((t1.column49)::text) = lower((t1.column49)::text)) AND (lower((t1.column50)::text) = lower((t1.column50)::text)))
              Buffers: shared read=947721, local hit=3
              ->  Hash Join  (cost=320052.61..2596594.47 rows=3112209 width=601) (actual time=4564.094..354800.123 rows=8845200 loops=1)
                    Hash Cond: (lower((t1.column37)::text) = lower((t3.column1)::text))
                    Buffers: shared read=947721, local hit=2
                    ->  Bitmap Heap Scan on mytable3 t1  (cost=320041.03..2509885.64 rows=8892027 width=85) (actual time=4563.680..319267.330 rows=8845200 loops=1)
                          Recheck Cond: (((column3)::text = 'aaaa'::text) AND (column4 >= 202001) AND (column58 = 2024) AND ((column9)::text = 'CCCC'::text) AND ((column30)::text = 'bbbbbbbb bbbbbbb'::text))
                          Heap Blocks: exact=813257
                          Buffers: shared read=947721
                          ->  Bitmap Index Scan on mytable3_idx3  (cost=0.00..317818.03 rows=8892027 width=0) (actual time=4238.606..4238.606 rows=8845200 loops=1)
                                Index Cond: (((column3)::text = 'aaaa'::text) AND (column4 >= 202001) AND (column58 = 2024))
                                Buffers: shared read=134464
                    ->  Hash  (cost=10.70..10.70 rows=70 width=1032) (actual time=0.389..0.390 rows=132 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 16kB
                          Buffers: local hit=2
                          ->  Seq Scan on temptable2 t3  (cost=0.00..10.70 rows=70 width=1032) (actual time=0.030..0.093 rows=132 loops=1)
                                Filter: ((column3 IS NOT NULL) OR (column4 IS NOT NULL))
                                Buffers: local hit=2
              ->  Materialize  (cost=0.00..11.65 rows=110 width=694) (actual time=0.000..0.000 rows=1 loops=8845200)
                    Buffers: local hit=1
                    ->  Seq Scan on temptable1 t2  (cost=0.00..11.10 rows=110 width=694) (actual time=0.022..0.023 rows=1 loops=1)
                          Buffers: local hit=1
Planning:
  Buffers: shared hit=7 read=1 dirtied=2
Planning Time: 83.493 ms
Execution Time: 490934.062 ms

Очень долго выгружает. Как будто индекс неправильно создан. После чтения индекса опять сканирует таблицу.

Использовать вложенный запрос для большой таблицы нет смысла, потребует много дополнительной памяти.

Скажите, как возможно ускорить? Как правильно нужно написать индекс?
0
IT_Exp
Эксперт
34794 / 4073 / 2104
Регистрация: 17.06.2006
Сообщений: 32,602
Блог
 31.07.2024, 22:36
Ответы с готовыми решениями:

Долгий DISTINCT
Здравствуйте! Есть большая таблица из 60 столбцов и 1 млрд строк. Есть индекс с составными полями (10 колонок). Когда запускаю...

Долгий запрос
MSSQL-2008. Есть таблица в котором около 4 млн записей. В которой поле DateOper(тип datetime2(7)) проиндексировано. Если делать такой...

оочень долгий запрос
Здравствуйте , столкнулся с такой проблемой : есть очень большая таблица, надо организовать постраничный вывод, т к сразу вывести все...

8
2 / 2 / 1
Регистрация: 16.04.2022
Сообщений: 1,011
01.08.2024, 08:52  [ТС]
Цитата Сообщение от MConst Посмотреть сообщение
После чтения индекса опять сканирует таблицу.
Почему так происходит? Что в индексе неправильно?
0
912 / 286 / 57
Регистрация: 01.06.2023
Сообщений: 807
01.08.2024, 09:00
А зачем два индекса? В таблицу пошел так как нужны были данные которых нет в покрывающем индексе

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

а еще у Вас не верная статистика по таблице temptable1 ожидается 110 строк а пришло 1
0
2 / 2 / 1
Регистрация: 16.04.2022
Сообщений: 1,011
01.08.2024, 10:27  [ТС]
Цитата Сообщение от Swa111 Посмотреть сообщение
В таблицу пошел так как нужны были данные которых нет в покрывающем индексе
А какие это данные, которых нет? Вроде все поля и выражения указал в создании индекса.
0
912 / 286 / 57
Регистрация: 01.06.2023
Сообщений: 807
01.08.2024, 10:54
Колонка 9 и 30
0
2 / 2 / 1
Регистрация: 16.04.2022
Сообщений: 1,011
01.08.2024, 14:54  [ТС]
Цитата Сообщение от MConst Посмотреть сообщение
После чтения индекса опять сканирует таблицу.
Цитата Сообщение от Swa111 Посмотреть сообщение
Колонка 9 и 30
Добавил
SQL
1
2
3
4
5
6
7

CREATE INDEX myTable3_idx4 ON test.myTable3 USING btree 
(
    "column3", "column4", "column9", "column24", "column25", "column30", "column35", "column38", "column58",
    (CASE WHEN "column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END),
    (LOWER("column37")), (LOWER("column49")), (LOWER("column50"))
) WHERE
    "column3" = 'aaaa' AND "column4" >= 202001 AND "column9" = 'CCCC' AND "column30" = 'bbbbbbbb bbbbbbb';
Запускаю тот же запрос
Кликните здесь для просмотра всего текста
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
42
43
44
45
46
47
48
49
50
51
52

EXPLAIN(analyze,buffers)
SELECT
    t1."column4",
    t1."column3",
    t2."column1",
    t2."column2",
    SUM(t1."column26") AS "column26",
    t1."column25",
    t1."column38",
    CASE WHEN t1."column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END,
    t1."column24",
    t3."column2",
    t1."column35"
FROM
    test.myTable3 t1
LEFT JOIN
    tempTable1 t2
ON
    LOWER(t1."column49") = LOWER(t1."column49")
    AND
    LOWER(t1."column50") = LOWER(t1."column50")
LEFT JOIN
    tempTable2 t3
ON
    LOWER(t1."column37") = LOWER(t3."column1")
WHERE
    NOT(
        t3."column3" IS NULL
        AND
        t3."column4" IS NULL
    )
    AND
    t1."column3" = 'aaaa'
    AND
    t1."column58" = 2024 --Не статичное условие
    AND
    t1."column4" >= 202001
    AND
    t1."column9" = 'CCCC'
    AND
    t1."column30" = 'bbbbbbbb bbbbbbb' --not t1."column30" = 'aaaaaaa aaaaaaaa'
GROUP BY 
    t1."column4",
    t1."column3",
    t3."column2",
    t2."column1",
    t2."column2",
    t1."column38",
    t1."column24",
    t1."column25",
    t1."column35",
    CASE WHEN t1."column37" = 'aaaa' THEN 'aaa' ELSE 'aaaaaa' END--t1."column37"

План запроса
Кликните здесь для просмотра всего текста
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

GroupAggregate  (cost=13482790.42..13615059.31 ROWS=3112209 width=1318) (actual TIME=485092.881..506222.652 ROWS=70200 loops=1)
  GROUP KEY: t1.column4, t3.column2, t2.column1, t2.column2, t1.column38, t1.column24, t1.column25, t1.column35, (CASE WHEN ((t1.column37)::text = 'aaaa'::text) THEN 'aaa'::text ELSE 'aaaaaa'::text END)
  Buffers: shared READ=947721, LOCAL hit=3, temp READ=109780 written=109789
  ->  Sort  (cost=13482790.42..13490570.95 ROWS=3112209 width=1293) (actual TIME=485092.584..499882.377 ROWS=8845200 loops=1)
        Sort KEY: t1.column4, t3.column2, t2.column1, t2.column2, t1.column38, t1.column24, t1.column25, t1.column35, (CASE WHEN ((t1.column37)::text = 'aaaa'::text) THEN 'aaa'::text ELSE 'aaaaaa'::text END)
        Sort Method: external MERGE  Disk: 878240kB
        Buffers: shared READ=947721, LOCAL hit=3, temp READ=109780 written=109789
        ->  Nested Loop LEFT JOIN  (cost=320052.61..12018818.60 ROWS=3112209 width=1293) (actual TIME=15573.197..408535.844 ROWS=8845200 loops=1)
              JOIN FILTER: ((LOWER((t1.column49)::text) = LOWER((t1.column49)::text)) AND (LOWER((t1.column50)::text) = LOWER((t1.column50)::text)))
              Buffers: shared READ=947721, LOCAL hit=3
              ->  Hash JOIN  (cost=320052.61..2596594.47 ROWS=3112209 width=601) (actual TIME=15573.147..369965.589 ROWS=8845200 loops=1)
                    Hash Cond: (LOWER((t1.column37)::text) = LOWER((t3.column1)::text))
                    Buffers: shared READ=947721, LOCAL hit=2
                    ->  Bitmap Heap Scan ON mytable3 t1  (cost=320041.03..2509885.64 ROWS=8892027 width=85) (actual TIME=15572.908..333995.660 ROWS=8845200 loops=1)
                          Recheck Cond: (((column3)::text = 'aaaa'::text) AND (column4 >= 202001) AND (column58 = 2024) AND ((column9)::text = 'CCCC'::text) AND ((column30)::text = 'bbbbbbbb bbbbbbb'::text))
                          Heap Blocks: exact=813257
                          Buffers: shared READ=947721
                          ->  Bitmap INDEX Scan ON mytable3_idx3  (cost=0.00..317818.03 ROWS=8892027 width=0) (actual TIME=15238.079..15238.080 ROWS=8845200 loops=1)
                                INDEX Cond: (((column3)::text = 'aaaa'::text) AND (column4 >= 202001) AND (column58 = 2024))
                                Buffers: shared READ=134464
                    ->  Hash  (cost=10.70..10.70 ROWS=70 width=1032) (actual TIME=0.218..0.219 ROWS=132 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 16kB
                          Buffers: LOCAL hit=2
                          ->  Seq Scan ON temptable2 t3  (cost=0.00..10.70 ROWS=70 width=1032) (actual TIME=0.022..0.049 ROWS=132 loops=1)
                                FILTER: ((column3 IS NOT NULL) OR (column4 IS NOT NULL))
                                Buffers: LOCAL hit=2
              ->  Materialize  (cost=0.00..11.65 ROWS=110 width=694) (actual TIME=0.000..0.000 ROWS=1 loops=8845200)
                    Buffers: LOCAL hit=1
                    ->  Seq Scan ON temptable1 t2  (cost=0.00..11.10 ROWS=110 width=694) (actual TIME=0.023..0.023 ROWS=1 loops=1)
                          Buffers: LOCAL hit=1
Planning:
  Buffers: shared hit=54 READ=2 dirtied=4
Planning TIME: 9.813 ms
Execution TIME: 506330.002 ms

Как видим также. И еще вначале использует не индекс myTable3_idx4, а myTable3_idx3.

Кстати, удалил индекс myTable3_idx3 и план запроса использует внчале myTable3_idx4, потом также сканирует таблицу.
0
912 / 286 / 57
Регистрация: 01.06.2023
Сообщений: 807
01.08.2024, 15:17
еще не хватает колонки 26
0
2 / 2 / 1
Регистрация: 16.04.2022
Сообщений: 1,011
01.08.2024, 17:40  [ТС]
Также вначале читает индекс, а потом сканирует таблицу.

Здесь похоже только один вариант - использовать циклы, то есть выгружать частями.
0
912 / 286 / 57
Регистрация: 01.06.2023
Сообщений: 807
02.08.2024, 10:56
SQL
1
2
3
4
5
6

LEFT JOIN
    tempTable1 t2
ON
    LOWER(t1."column49") = LOWER(t1."column49")
    AND
    LOWER(t1."column50") = LOWER(t1."column50")
У Вас ошибка в запросе, в способе соединении нет таблицы t2
0
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
BasicMan
Эксперт
29316 / 5623 / 2384
Регистрация: 17.02.2009
Сообщений: 30,364
Блог
 02.08.2024, 10:56
Помогаю со студенческими работами здесь

Долгий запрос в SQL
Добрый день! Делаю запрос в БД, объем получаемого списка 38 мБ. время уходит 2 минуты, можно как то ускорить процесс? def zapros(): ...

Долгий update
Доброго дня. Объясните, пожалуйста, где я туплю. Есть вот такой запрос: UPDATE otto_article LEFT JOIN otto_main USING (ItemID)...

Долгий ребилд индексов
Всем привет. У меня есть 1С и сервер MS SQL и идет очень долгий ребилд индексов больше чем один день. В связи с этим я решил набросать...

Долгий коннект к базе
Всем доброго времени суток. Имеется ib 6.0 на Win 2003. Прога, написанная на билдере. 20 юзеров. При коннекте с утра конект идёт около...

MAintenancePlan очень долгий
Долго делается обычный план обслуживания для 1с, который используют везде, на других серверах такой же план выполняется очень быстро. ...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
9
Ответ Создать тему
Новые блоги и статьи
PhpStorm 2025.3: WSL Terminal всегда стартует в ~
and_y87 14.12.2025
PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .
Access
VikBal 11.12.2025
Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.
Новый ноутбук
volvo 07.12.2025
Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro
 Музыка, написанная Искусственным Интеллектом
volvo 04.12.2025
Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .
От async/await к виртуальным потокам в Python
IndentationError 23.11.2025
Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов
Argus19 22.11.2025
Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .
Сколько Государство потратило денег на меня, обеспечивая инсулином.
Programma_Boinc 20.11.2025
Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .
Ломающие изменения в C#.NStar Alpha
Etyuhibosecyu 20.11.2025
Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .
Мысли в слух
kumehtar 18.11.2025
Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .
Создание Single Page Application на фреймах
krapotkin 16.11.2025
Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .
КиберФорум - форум программистов, компьютерный форум, программирование
Поддержать - Реклама - Условия использования - Обратная связь
Powered by vBulletin
Copyright ©2000 - 2025, CyberForum.ru