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

@MConst · Регистрация: 16.04.2022

Author24 — интернет-сервис помощи студентам

Здравствуйте!

Есть одна большая таблица 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

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

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

Скажите, как возможно ускорить? Как правильно нужно написать индекс?

@MConst · 01.08.2024, 08:52 **[ТС]**

Сообщение от MConst

После чтения индекса опять сканирует таблицу.

Почему так происходит? Что в индексе неправильно?

@Swa111 · 01.08.2024, 09:00

А зачем два индекса? В таблицу пошел так как нужны были данные которых нет в покрывающем индексе

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

а еще у Вас не верная статистика по таблице temptable1 ожидается 110 строк а пришло 1

@MConst · 01.08.2024, 10:27 **[ТС]**

Сообщение от Swa111

В таблицу пошел так как нужны были данные которых нет в покрывающем индексе

А какие это данные, которых нет? Вроде все поля и выражения указал в создании индекса.

@Swa111 · 01.08.2024, 10:54

Колонка 9 и 30

@MConst · 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, потом также сканирует таблицу.

@Swa111 · 01.08.2024, 15:17

еще не хватает колонки 26

@MConst · 01.08.2024, 17:40 **[ТС]**

Также вначале читает индекс, а потом сканирует таблицу.

Здесь похоже только один вариант - использовать циклы, то есть выгружать частями.

@Swa111 · 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

Новые блоги и статьи Все статьи Все блоги /
JWT аутентификация в Java Javaican 21.04.2025 JWT (JSON Web Token) представляет собой открытый стандарт (RFC 7519), который определяет компактный и самодостаточный способ передачи информации между сторонами в виде JSON-объекта. Эта информация. . .	Спринты Agile: Планирование, выполнение, ревью и ретроспектива EggHead 21.04.2025 Спринты — сердцевина Agile-методологии, позволяющая командам создавать работающий продукт итерационно, с постоянной проверкой гипотез и адаптацией к изменениям. В основе концепции спринтов лежит. . .	Очередные открытия мега простых чисел, сделанные добровольцами с помощью домашних компьютеров Programma_Boinc 21.04.2025 Очередные открытия мега простых чисел, сделанные добровольцами с помощью домашних компьютеров. 3 марта 2025 года, в результате обобщенного поиска простых чисел Ферма в PrimeGrid был найден. . .	Система статов в Unity GameUnited 20.04.2025 Статы — фундаментальный элемент игрового дизайна, который определяет характеристики персонажей, предметов и других объектов в игровом мире. Будь то показатель силы в RPG, скорость передвижения в. . .	Статические свойства и методы в TypeScript run.dev 20.04.2025 TypeScript прочно занял своё место в системе современной веб-разработки. Этот строго типизированный язык программирования не просто расширяет возможности JavaScript — он делает разработку более. . .
Batch Transform и Batch Gizmo Drawing API в Unity GameUnited 20.04.2025 В мире разработки игр и приложений на Unity производительность всегда была критическим фактором успеха. Создатели игр постоянно балансируют между визуальной привлекательностью и плавностью работы. . .	Звук в Unity: Рандомизация с Audio Random Container GameUnited 20.04.2025 В современных играх звуковое оформление часто становится элементом, который либо полностью погружает игрока в виртуальный мир, либо разрушает атмосферу за считанные минуты. Представьте: вы исследуете. . .	Максимальная производительность C#: Советы, тестирование и заключение stackOverflow 20.04.2025 Погружение в мир микрооптимизаций C# открывает перед разработчиком целый арсенал мощных техник. Но как определить, где и когда их применять? Ответ начинается с точных измерений и профилирования. . . .	Максимальная производительность C#: Предсказание ветвлений stackOverflow 20.04.2025 Третий ключевой аспект низкоуровневой оптимизации — предсказание ветвлений. Эта тема менее известна среди разработчиков, но её влияние на производительность может быть колоссальным. Чтобы понять. . .	Максимальная производительность C#: Векторизация (SIMD) stackOverflow 20.04.2025 Помимо работы с кэшем, другим ключевым аспектом низкоуровневой оптимизации является векторизация вычислений. SIMD (Single Instruction, Multiple Data) позволяет обрабатывать несколько элементов данных. . .

@Swa111 760 / 277 / 57 Регистрация: 01.06.2023 Сообщений: 764
	01.08.2024, 09:00
	А зачем два индекса? В таблицу пошел так как нужны были данные которых нет в покрывающем индексе нормализуйте таблицы, при создании покрывающего индекса необходимо указать все используемые столбцы, но возможно потеряет смысл так как будет иметь такой же размер (сопостовимую скорость извлечения) а еще у Вас не верная статистика по таблице temptable1 ожидается 110 строк а пришло 1 0

@Swa111 760 / 277 / 57 Регистрация: 01.06.2023 Сообщений: 764
	01.08.2024, 10:54
	Колонка 9 и 30 0

@Swa111 760 / 277 / 57 Регистрация: 01.06.2023 Сообщений: 764
	01.08.2024, 15:17
	еще не хватает колонки 26 0

@MConst 2 / 2 / 1 Регистрация: 16.04.2022 Сообщений: 961
	01.08.2024, 17:40 [ТС]
	Также вначале читает индекс, а потом сканирует таблицу. Здесь похоже только один вариант - использовать циклы, то есть выгружать частями. 0