Оптимизировать запрос / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

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

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

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

Оптимизировать запрос

#39779757

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Есть запрос, который извлекает из 'Attachaments' данные одной строки, у которой наибольший id, у которой есть строки в Transactions и в Tickets:

Код: plsql

1.
2.
3.
4.

SELECT DISTINCT main.* 
   FROM Attachments main JOIN Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )  
   WHERE (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')  
   ORDER BY main.id DESC  LIMIT 1;

выполняется долго и ресурсов жрет. Выполняется часто.

Explain analyze:

Код: plsql

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

 Limit  (cost=6746117.47..6746117.51 rows=1 width=1262) (actual time=206765.365..206765.367 rows=1 loops=1)
   ->  Unique  (cost=6746117.47..6856085.41 rows=3141941 width=1262) (actual time=206765.361..206765.361 rows=1 loops=1)
         ->  Sort  (cost=6746117.47..6753972.33 rows=3141941 width=1262) (actual time=206765.352..206765.352 rows=1 loops=1)
               Sort Key: main.id, main.transactionid, main.parent, main.messageid, main.subject, main.filename, main.contentencoding, main.content, main.headers, main.creator, main.created, main.contentindex
               Sort Method: external sort  Disk: 12572352kB
               ->  Hash Join  (cost=2487691.16..4678061.88 rows=3141941 width=1262) (actual time=55067.178..170052.942 rows=9592684 loops=1)
                     Hash Cond: (transactions_1.objectid = tickets_2.id)
                     ->  Hash Join  (cost=2022688.99..4141512.66 rows=3210211 width=1266) (actual time=43503.099..137326.089 rows=9735191 loops=1)
                           Hash Cond: (main.transactionid = transactions_1.id)
                           ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1803372.37 rows=6476553 width=1262) (actual time=0.077..60094.969 rows=9735191 loops=1)
                           ->  Hash  (cost=1608253.45..1608253.45 rows=33154843 width=8) (actual time=43495.766..43495.766 rows=33245560 loops=1)
                                 Buckets: 4194304  Batches: 1  Memory Usage: 1298655kB
                                 ->  Seq Scan on transactions transactions_1  (cost=0.00..1608253.45 rows=33154843 width=8) (actual time=0.066..24418.915 rows=33245560 loops=1)
                                       Filter: ((objecttype)::text = 'RT::Ticket'::text)
                     ->  Hash  (cost=361915.06..361915.06 rows=8246969 width=4) (actual time=11561.688..11561.688 rows=8250463 loops=1)
                           Buckets: 1048576  Batches: 1  Memory Usage: 290056kB
                           ->  Seq Scan on tickets tickets_2  (cost=0.00..361915.06 rows=8246969 width=4) (actual time=0.018..6694.290 rows=8250463 loops=1)
                                 Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 208710.237 ms
(19 rows)

Time: 208715.372 ms

В 'Аttachments' - 31 млн, в 'Transactions' - 66 млн, в 'Tickets' - 8 млн строк.

Индексы по 'Attachments':

Код: plsql

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

(postgres@[local]:5432) [rtdb] > select indexdef from pg_indexes where tablename = 'attachments';
                                                                                                  indexdef                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE INDEX attachments3_ ON attachments USING btree (parent, transactionid)
 CREATE UNIQUE INDEX attachments_pkey ON attachments USING btree (id)
 CREATE INDEX contentindex_idx_ ON attachments USING gin (contentindex)
 CREATE INDEX attachments2_ ON attachments USING btree (transactionid)
 CREATE INDEX attachments_id_where_contentinde_not_null_content_type_text ON attachments USING btree (id DESC NULLS LAST) WHERE ((contentindex IS NOT NULL) AND ((contenttype)::text = 'text/plain'::text))
 CREATE INDEX attachments1_ ON attachments USING btree (parent)
(6 rows)

Индексы по 'Transactions':

Код: plsql

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

(postgres@[local]:5432) [rtdb] > select indexdef from pg_indexes where tablename = 'transactions';                                                                                  indexdef                                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX transactions_pkey ON transactions USING btree (id)
 CREATE INDEX transactions_objectid_idx_ ON transactions USING btree (objectid)
 CREATE INDEX transactions_id_where_objecttype ON transactions USING btree (id) WHERE ((objecttype)::text = 'RT::Ticket'::text)
 CREATE INDEX transactions_id_object_type_where_objecttype_rt_ticket ON transactions USING btree (id DESC, objecttype DESC) WHERE ((objecttype)::text = 'RT::Ticket'::text)
(4 rows)

Time: 3.497 ms

Предложил заменить его на другой, в котором, использую наибольшие 10 000 id:

Код: plsql

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

with nyt as (select id from attachments order by 1 desc limit 10000) 
SELECT DISTINCT main.* 
FROM Attachments main join nyt on nyt.id = main.id 
        JOIN Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId ) 
        JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )  
        WHERE (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')  
        ORDER BY main.id DESC  LIMIT 1;

выполняется за 240 ms.

Но! Требуют подменять в базе.

тогда я создал 2 вьюхи att_join_tran, att_join_tran_new на основе старого и нового запросов и хотел создать правило:

Код: plsql

1.
2.
3.

(postgres@[local]:5432) [rtdb] > create rule "MY_RETURN" as on select to att_join_tran do instead select * from att_join_tran_new;
ERROR:  "att_join_tran" is already a view
Time: 1.707 ms

В документации говорится: https://www.postgresql.org/docs/9.1/sql-createrule.html

table

The name (optionally schema-qualified) of the table OR VIEW (!) the rule applies to.

Непонятно как подменить запрос один - другим? (В Oracle - это решается materialized view.)
Версия 9.1

...

Рейтинг:

0 / 0

27.02.2019, 13:15

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

Оптимизировать запрос

#39779821

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

maxski,

вместо join и distinct в данном запросе следует использовать конструкцию exists, особенно если есть уверенность что нужные строки всегда найдутся сразу в конце таблицы attachments.

можно еще попробовать заменить distinct на distinct on(main.id), но не факт что это уберет hash join'ы.

...

Рейтинг:

0 / 0

27.02.2019, 14:16

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

Оптимизировать запрос

#39779828

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

maxski,

У вас запрос неверно написанный.
Делать JOIN чтобы он создавал дубликаты а потом делать distinct чтобы их убрать - так делать не надо (и нормально оно никогда работать не будет).

Правильно вот так вот

Код: plsql

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

SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT FROM Transactions Transactions_1  
  JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId ) 
  WHERE ( Transactions_1.id = main.TransactionId ) 
  AND (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;

И дальше его оптимизировать. Впрочем чудес ожидать не стоит 9.1 версия древняя как мамонты и такая же не сильно умная с т.з. оптимизации.

PS: То что база исходный ваш запрос нормально не выполняет - так и 12 версия его выполнять не будет. Не делайте так.

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

...

Рейтинг:

0 / 0

27.02.2019, 14:22

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

Оптимизировать запрос

#39779871

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Maxim Boguk, Alexius ,

спасибо, что откликнулись. Запрос не мой ("мопед не мой"), это Request Tracker 4. Он вроде как на лету генерит запросы. По этой причине, мне не дают его (запрос) переписать.
Мне было предложено оптимизировать на стороне базы. Поэтому я зашел со строны create rule:

Код: plsql

1.
2.
3.

(postgres@[local]:5432) [rtdb] > create rule "MY_RETURN" as on select to att_join_tran do instead select * from att_join_tran_new;
ERROR:  "att_join_tran" is already a view
Time: 1.707 ms

...

Рейтинг:

0 / 0

27.02.2019, 14:53

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

Оптимизировать запрос

#39779878

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

maxskiMaxim Boguk, Alexius ,

спасибо, что откликнулись. Запрос не мой ("мопед не мой"), это Request Tracker 4. Он вроде как на лету генерит запросы. По этой причине, мне не дают его (запрос) переписать.
Мне было предложено оптимизировать на стороне базы. Поэтому я зашел со строны create rule:

Код: plsql

1.
2.
3.

(postgres@[local]:5432) [rtdb] > create rule "MY_RETURN" as on select to att_join_tran do instead select * from att_join_tran_new;
ERROR:  "att_join_tran" is already a view
Time: 1.707 ms

Я так понял, что вьюха, на основе старого запроса att_join_tran не прошла потому, что в ней несколько таблиц присутствует (?).

Обещали "железо" для перехода на новую версию сервера. Вот пока жду.

Ни железо ни новая версия сервера ни rules при невозможности целиком переписать запрос - не помогут в данной конкретной ситуации.

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

...

Рейтинг:

0 / 0

27.02.2019, 15:01

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

Оптимизировать запрос

#39779905

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Maxim Boguk,

вот explain analyze для вашего запроса с exist (full scan 'tickets' - 8млн и full scan 'transactions' - 66 млн строк). И время выполнения 2мин. Вместо 4 мин на старом запросе.

Не сильно лучше. Прошу прощения за критику.

Код: plsql

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

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=465038.54..952781.61 rows=1 width=1262) (actual time=129648.591..129648.593 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=465038.54..3159015747024.90 rows=6476802 width=1262) (actual time=129648.589..129648.589 rows=1 loops=1)
         Join Filter: (main.transactionid = transactions_1.id)
         ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1803449.92 rows=6476802 width=1262) (actual time=0.020..0.020 rows=1 loops=1)
         ->  Materialize  (cost=465038.54..2975908.55 rows=32516161 width=4) (actual time=11589.104..116207.525 rows=32738480 loops=1)
               ->  Hash Join  (cost=465038.54..2813327.75 rows=32516161 width=4) (actual time=11589.100..87880.630 rows=32738480 loops=1)
                     Hash Cond: (transactions_1.objectid = tickets_2.id)
                     ->  Seq Scan on transactions transactions_1  (cost=0.00..1608211.30 rows=33193304 width=8) (actual time=0.008..26086.917 rows=33245813 loops=1)
                           Filter: ((objecttype)::text = 'RT::Ticket'::text)
                     ->  Hash  (cost=361889.30..361889.30 rows=8251939 width=4) (actual time=11586.131..11586.131 rows=8250769 loops=1)
                           Buckets: 1048576  Batches: 1  Memory Usage: 290067kB
                           ->  Seq Scan on tickets tickets_2  (cost=0.00..361889.30 rows=8251939 width=4) (actual time=0.012..6434.010 rows=8250769 loops=1)
                                 Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 130079.671 ms
(14 rows)

Time: 130099.869 ms

(postgres@[local]:5432) [rtdb] > show work_mem;
 work_mem 
----------
 2GB
(1 row)

я бы воткнул бы max(id) для 'Attachments', если б 100% был бы уверен, что в такая строка найдется в Transactions и Tickets. Выполняется мгновенно (2ms). Но нет гарантии, что строка найдется.

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > explain analyze with maxid as (select max(id) as id from attachments)                      
SELECT DISTINCT main.*
FROM Attachments main join maxid on  main.id = maxid.id
        JOIN Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId )
        JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )
        WHERE (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
        ORDER BY main.id DESC  LIMIT 1;
                                                                                                     QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=25.99..26.03 rows=1 width=1262) (actual time=0.119..0.119 rows=0 loops=1)
   CTE maxid
     ->  Result  (cost=0.26..0.27 rows=1 width=0) (actual time=0.046..0.047 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.00..0.26 rows=1 width=4) (actual time=0.041..0.042 rows=1 loops=1)
                   ->  Index Scan Backward using attachments_pkey on attachments  (cost=0.00..8162276.03 rows=31868158 width=4) (actual time=0.039..0.039 rows=1 loops=1)
                         Index Cond: (id IS NOT NULL)
   ->  Unique  (cost=25.73..25.76 rows=1 width=1262) (actual time=0.116..0.116 rows=0 loops=1)
         ->  Sort  (cost=25.73..25.73 rows=1 width=1262) (actual time=0.114..0.114 rows=0 loops=1)
               Sort Key: main.id, main.transactionid, main.parent, main.messageid, main.subject, main.filename, main.contentencoding, main.content, main.headers, main.creator, main.created, main.contentindex
               Sort Method: quicksort  Memory: 25kB
               ->  Nested Loop  (cost=0.00..25.72 rows=1 width=1262) (actual time=0.080..0.080 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.00..19.33 rows=1 width=1266) (actual time=0.078..0.078 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.00..9.92 rows=1 width=1262) (actual time=0.076..0.076 rows=0 loops=1)
                                 ->  CTE Scan on maxid  (cost=0.00..0.02 rows=1 width=4) (actual time=0.050..0.052 rows=1 loops=1)
                                 ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..9.89 rows=1 width=1262) (actual time=0.018..0.018 rows=0 loops=1)
                                       Index Cond: (id = maxid.id)
                           ->  Index Scan using transactions_id_where_objecttype on transactions transactions_1  (cost=0.00..9.40 rows=1 width=8) (never executed)
                                 Index Cond: (id = main.transactionid)
                     ->  Index Scan using tickets5_ on tickets tickets_2  (cost=0.00..6.37 rows=1 width=4) (never executed)
                           Index Cond: (id = transactions_1.objectid)
                           Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 0.408 ms
(23 rows)

Time: 5.097 ms

Надо идти по пути уменьшения объёма извлечения. Поэтому, я в своем "костыле" (см парой постов выше) я ограничился 10тыс строк от верхнего конца. Это все же лучше, чем 31 млн )))

...

Рейтинг:

0 / 0

27.02.2019, 15:24

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

Оптимизировать запрос

#39779919

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Maxim Boguk,

у меня свободного места осталось 200ГБ из 3.5ТБ. и 1ГБ в день отщелкивается. Мне без нового "железа" никак не перейти на новую версию сервера.

...

Рейтинг:

0 / 0

27.02.2019, 15:34

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

Оптимизировать запрос

#39779959

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

maxski,

Интересно как...
А если перед explain моей версии сделать
set enable_hashjoin=off;
set enable_mergejoin=off;
то план нормальнее не становится?

PS: для теста чтобы проверить что это не проблема в слишком старой версии базы попробуйте упрощенную (неполную) версию запроса еще explain analyze сделать вида

Код: plsql

1.
2.
3.
4.
5.

SELECT main.*
FROM Attachments main
WHERE
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;

и вида

Код: plsql

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

SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT FROM Transactions Transactions_1  
  WHERE 
  ( Transactions_1.id = main.TransactionId ) AND (Transactions_1.ObjectType = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')

ORDER BY main.id DESC NULLS LAST LIMIT 1;

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

...

Рейтинг:

0 / 0

27.02.2019, 16:28

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

Оптимизировать запрос

#39779982

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Maxim Boguk,

первый:

Код: plsql

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

(postgres@[local]:5432) [rtdb] > explain analyze SELECT main.*
FROM Attachments main
WHERE
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;
                                                                                            QUERY PLAN                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.28 rows=1 width=1250) (actual time=0.074..0.075 rows=1 loops=1)
   ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1790239.93 rows=6421315 width=1250) (actual time=0.071..0.071 rows=1 loops=1)
 Total runtime: 0.134 ms
(3 rows)

Time: 7.169 ms

второй:

Код: plsql

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

(postgres@[local]:5432) [rtdb] > explain analyze SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT 1 FROM Transactions Transactions_1  
  WHERE 
  ( Transactions_1.id = main.TransactionId ) AND (Transactions_1.ObjectType = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;
                                                                                               QUERY PLAN                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..9.57 rows=1 width=1250) (actual time=0.094..0.094 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=0.00..61433107.01 rows=6421318 width=1250) (actual time=0.093..0.093 rows=1 loops=1)
         ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1822397.80 rows=6421318 width=1250) (actual time=0.014..0.014 rows=1 loops=1)
         ->  Index Scan using transactions_id_where_objecttype on transactions transactions_1  (cost=0.00..9.27 rows=1 width=4) (actual time=0.069..0.069 rows=1 loops=1)
               Index Cond: (id = main.transactionid)
 Total runtime: 0.145 ms
(6 rows)

Time: 2.356 ms

и ваш запрос с set ...= off

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > explain analyze SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT 1 FROM Transactions Transactions_1  
  JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId ) 
  WHERE ( Transactions_1.id = main.TransactionId ) 
  AND (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;
                                                                                                  QUERY PLAN                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..490673.74 rows=1 width=1250) (actual time=256425.012..256425.013 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=0.00..3150789317133.24 rows=6421353 width=1250) (actual time=256425.008..256425.008 rows=1 loops=1)
         Join Filter: (main.transactionid = transactions_1.id)
         ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1832049.50 rows=6421353 width=1250) (actual time=0.029..0.029 rows=1 loops=1)
         ->  Materialize  (cost=0.00..216407848.22 rows=32709318 width=4) (actual time=0.118..241963.376 rows=32739812 loops=1)
               ->  Nested Loop  (cost=0.00..216244301.63 rows=32709318 width=4) (actual time=0.111..212361.406 rows=32739812 loops=1)
                     ->  Index Scan using transactions_id_where_objecttype on transactions transactions_1  (cost=0.00..3037047.69 rows=33399577 width=8) (actual time=0.081..35305.579 rows=33247145 loops=1)
                     ->  Index Scan using tickets5_ on tickets tickets_2  (cost=0.00..6.37 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=33247145)
                           Index Cond: (id = transactions_1.objectid)
                           Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 257373.584 ms
(11 rows)

Time: 257375.881 ms

...

Рейтинг:

0 / 0

27.02.2019, 16:53

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

Оптимизировать запрос

#39779988

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

maxski,

а если еще для полной версии запроса сделать enable_material=off
?

...

Рейтинг:

0 / 0

27.02.2019, 17:01

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

Оптимизировать запрос

#39779991

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

maxski,

Да чтобы exists с join внутри работал быстро нужен хотя бы 9.2.
Увы тут вы уже на ограничения 9.1 попадаете.

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

...

Рейтинг:

0 / 0

27.02.2019, 17:05

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

Оптимизировать запрос

#39780027

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Maxim Bogukmaxski,

а если еще для полной версии запроса сделать enable_material=off
?

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > set enable_material=off;
SET
Time: 0.321 ms
(postgres@[local]:5432) [rtdb] > explain analyze SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT 1 FROM Transactions Transactions_1  
  JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId ) 
  WHERE ( Transactions_1.id = main.TransactionId ) 
  AND (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;
                                                                                               QUERY PLAN                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..216672060.06 rows=1 width=1250) (actual time=386450.711..386450.712 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=0.00..1391462552892300.50 rows=6421975 width=1250) (actual time=386450.708..386450.708 rows=1 loops=1)
         Join Filter: (main.transactionid = transactions_1.id)
         ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1832237.11 rows=6421975 width=1250) (actual time=0.042..0.042 rows=1 loops=1)
         ->  Nested Loop  (cost=0.00..216263638.62 rows=32673692 width=4) (actual time=0.104..371338.272 rows=32740385 loops=1)
               ->  Index Scan using transactions_id_where_objecttype on transactions transactions_1  (cost=0.00..3037160.79 rows=33399577 width=8) (actual time=0.063..46996.070 rows=33247718 loops=1)
               ->  Index Scan using tickets5_ on tickets tickets_2  (cost=0.00..6.37 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=33247718)
                     Index Cond: (id = transactions_1.objectid)
                     Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 386450.799 ms
(10 rows)

Time: 386453.288 ms

Вот (внизу) attachments.id, которые имеют строки в Transactions и в Tickets. Видно, что в каждой десятке есть пара-тройка id.
Среди десяток - я не встретил пропусков (на последней 1000). Ну и соответственно в каждой сотне есть хотя бы один id (со строками в Transactions и в Tickets). Не говоря уже про последние 10 тыс id. т.е. выбрав последние 10тыс attachments.id у нас будет практически 100% вероятность, что будет хотя бы один id cо строками в Transactions и в Tickets. Смотрим:

Время выполнения запроса 52ms, на версии 9.1

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > explain analyze with nyt as (select id from attachments order by 1 desc limit 10000)
SELECT DISTINCT main.*
FROM Attachments main join nyt on nyt.id = main.id
        JOIN Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId )
        JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )
        WHERE (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
        ORDER BY main.id DESC  LIMIT 1;
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=122828.08..122828.12 rows=1 width=1250) (actual time=793.155..793.156 rows=1 loops=1)
   CTE nyt
     ->  Limit  (cost=0.00..2591.23 rows=10000 width=4) (actual time=0.056..338.286 rows=10000 loops=1)
           ->  Index Scan Backward using attachments_pkey on attachments  (cost=0.00..8259097.16 rows=31873229 width=4) (actual time=0.054..329.337 rows=10000 loops=1)
   ->  Unique  (cost=120236.85..120271.29 rows=984 width=1250) (actual time=793.152..793.152 rows=1 loops=1)
         ->  Sort  (cost=120236.85..120239.31 rows=984 width=1250) (actual time=793.147..793.147 rows=1 loops=1)
               Sort Key: main.id, main.transactionid, main.parent, main.messageid, main.subject, main.filename, main.contentencoding, main.content, main.headers, main.creator, main.created, main.contentindex
               Sort Method: quicksort  Memory: 5208kB
               ->  Nested Loop  (cost=0.00..120187.93 rows=984 width=1250) (actual time=4.467..783.675 rows=2727 loops=1)
                     ->  Nested Loop  (cost=0.00..113765.20 rows=1006 width=1254) (actual time=4.435..753.659 rows=2727 loops=1)
                           ->  Nested Loop  (cost=0.00..94799.40 rows=2015 width=1250) (actual time=4.401..393.368 rows=2727 loops=1)
                                 ->  CTE Scan on nyt  (cost=0.00..200.00 rows=10000 width=4) (actual time=0.060..350.669 rows=10000 loops=1)
                                 ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..9.45 rows=1 width=1250) (actual time=0.003..0.003 rows=0 loops=10000)
                                       Index Cond: (id = nyt.id)
                           ->  Index Scan using transactions_id_where_objecttype on transactions transactions_1  (cost=0.00..9.40 rows=1 width=8) (actual time=0.129..0.130 rows=1 loops=2727)
                                 Index Cond: (id = main.transactionid)
                     ->  Index Scan using tickets5_ on tickets tickets_2  (cost=0.00..6.37 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=2727)
                           Index Cond: (id = transactions_1.objectid)
                           Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 834.907 ms
(20 rows)

Time: 838.396 ms

Код: 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.

37387683 |   1
 37387688 |   1
 37387693 |   1
 37387696 |   1
 37387700 |   1
 37387701 |   1
 37387702 |   1
 37387704 |   1
 37387707 |   1
 37387710 |   1
 37387714 |   1
 37387718 |   1
 37387721 |   1
 37387724 |   1
 37387728 |   1
 37387732 |   1
 37387735 |   1
 37387739 |   1
 37387743 |   1
 37387746 |   1
 37387750 |   1
 37387755 |   1
 37387759 |   1
 37387762 |   1
 37387768 |   1
 37387773 |   1
 37387777 |   1
 37387780 |   1
 37387784 |   1
 37387788 |   1
 37387791 |   1
 37387794 |   1
 37387797 |   1
 37387800 |   1
 37387804 |   1
 37387808 |   1
 37387811 |   1
 37387814 |   1
 37387817 |   1
 37387821 |   1
 37387825 |   1
 37387829 |   1
 37387833 |   1
 37387836 |   1
 37387839 |   1
 37387842 |   1
 37387846 |   1
 37387850 |   1

...

Рейтинг:

0 / 0

27.02.2019, 18:02

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

Оптимизировать запрос

#39780043

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

maxski,

а так?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

explain analyze SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT 1 FROM Transactions Transactions_1  
  JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId ) 
  WHERE ( Transactions_1.id = main.TransactionId ) 
  AND (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType || '' = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;

explain analyze SELECT DISTINCT ON(main.id) main.* 
   FROM Attachments main JOIN Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )  
   WHERE (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')  
   ORDER BY main.id DESC  LIMIT 1;

...

Рейтинг:

0 / 0

27.02.2019, 18:41

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

Оптимизировать запрос

#39780100

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,

второй вариант выполнился мгновенно:

Код: plsql

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

postgres@[local]:5432) [rtdb] > explain analyze SELECT DISTINCT ON(main.id) main.* 
   FROM Attachments main JOIN Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )  
   WHERE (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')  
   ORDER BY main.id DESC  LIMIT 1;
                                                                                      QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..28.74 rows=1 width=1259) (actual time=0.875..0.875 rows=1 loops=1)
   ->  Unique  (cost=0.00..90110734.34 rows=3134980 width=1259) (actual time=0.874..0.874 rows=1 loops=1)
         ->  Nested Loop  (cost=0.00..90102896.89 rows=3134980 width=1259) (actual time=0.871..0.871 rows=1 loops=1)
               ->  Nested Loop  (cost=0.00..69640186.25 rows=3204845 width=1263) (actual time=0.845..0.845 rows=1 loops=1)
                     ->  Index Scan Backward using attachments_pkey on attachments main  (cost=0.00..9226857.96 rows=6418248 width=1259) (actual time=0.811..0.811 rows=1 loops=1)
                           Filter: ((contentindex IS NOT NULL) AND ((contenttype)::text = 'text/plain'::text))
                     ->  Index Scan using transactions_id_where_objecttype on transactions transactions_1  (cost=0.00..9.40 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=1)
                           Index Cond: (id = main.transactionid)
               ->  Index Scan using tickets5_ on tickets tickets_2  (cost=0.00..6.37 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=1)
                     Index Cond: (id = transactions_1.objectid)
                     Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 0.933 ms
(12 rows)

Time: 15.006 ms

...

Рейтинг:

0 / 0

27.02.2019, 20:37

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

Оптимизировать запрос

#39780102

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,

с exist'ом ужк приводил выше. Еще раз.

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > explain analyze SELECT main.*
FROM Attachments main
WHERE
EXISTS (
  SELECT 1 FROM Transactions Transactions_1  
  JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId ) 
  WHERE ( Transactions_1.id = main.TransactionId ) 
  AND (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType || '' = 'RT::Ticket')
)
AND
(main.ContentIndex IS NOT NULL) AND (main.ContentType = 'text/plain')
ORDER BY main.id DESC NULLS LAST LIMIT 1;
                                                                                               QUERY PLAN                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=465560.24..470468.08 rows=1 width=1259) (actual time=187938.445..187938.445 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=465560.24..31500242866.23 rows=6418247 width=1259) (actual time=187938.443..187938.443 rows=1 loops=1)
         Join Filter: (main.transactionid = transactions_1.id)
         ->  Index Scan using attachments_id_where_contentinde_not_null_content_type_text on attachments main  (cost=0.00..1954710.53 rows=6418247 width=1259) (actual time=0.116..0.116 rows=1 loops=1)
         ->  Materialize  (cost=465560.24..2250109.13 rows=327151 width=4) (actual time=14603.380..174132.324 rows=32730048 loops=1)
               ->  Hash Join  (cost=465560.24..2248473.37 rows=327151 width=4) (actual time=14603.371..141647.563 rows=32730048 loops=1)
                     Hash Cond: (transactions_1.objectid = tickets_2.id)
                     ->  Seq Scan on transactions transactions_1  (cost=0.00..1775461.10 rows=334442 width=8) (actual time=0.014..47474.055 rows=33237381 loops=1)
                           Filter: (((objecttype)::text || ''::text) = 'RT::Ticket'::text)
                     ->  Hash  (cost=362378.36..362378.36 rows=8254550 width=4) (actual time=14598.780..14598.780 rows=8252170 loops=1)
                           Buckets: 1048576  Batches: 1  Memory Usage: 290116kB
                           ->  Seq Scan on tickets tickets_2  (cost=0.00..362378.36 rows=8254550 width=4) (actual time=0.021..7582.485 rows=8252170 loops=1)
                                 Filter: ((status)::text <> 'deleted'::text)
 Total runtime: 188866.974 ms
(14 rows)

...

Рейтинг:

0 / 0

27.02.2019, 20:39

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

Оптимизировать запрос

#39780113

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

maxski,

у меня немного другой exists был. используйте вариант с distinct on значит.

...

Рейтинг:

0 / 0

27.02.2019, 21:12

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

Оптимизировать запрос

#39780202

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,

Спасибо за отличный вариант!

...

Рейтинг:

0 / 0

28.02.2019, 10:02

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

Оптимизировать запрос

#39780850

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Небольшое дополнение по поводу моего "костыля" с выборкой последних 10 тыс строк из attachments:

Проанализировав данные в attachments, я пришел к тому же выводу, что и раньше, что в последних 10 тыс строк найдется хотя бы одно одна строка имеющая строку(строки) в Tickets и в Transactions:

Ниже вывод запроса подсчитавшего пропуски в Attachments:

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > with
  t as
    (
SELECT main.id as id
FROM Attachments main
WHERE
EXISTS (
  SELECT 1 FROM Transactions Transactions_1
  JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId )
  WHERE ( Transactions_1.id = main.TransactionId )
  AND (Tickets_2.Status != 'deleted') AND (Transactions_1.ObjectType = 'RT::Ticket')
) ORDER BY main.id DESC NULLS LAST LIMIT 100000000
  ),
min_max as (select max(t.id) as max_id, min(t.id) as min_id from t),
g as (select generate_series(min_max.min_id,min_max.max_id) as id from min_max)
select min(id) as begin_interval, max(id) as end_interval, count(1)
from (select foo.id ,  foo.id - row_number() over(order by id) as grp_id
        from (select  id from g EXCEPT select id  from t ) foo ) qoo group by grp_id having count(1) > 100  order by 1;
 begin_interval | end_interval | count 
----------------+--------------+-------
       31482604 |     31482783 |   180
       31482814 |     31482998 |   185
       31483101 |     31483234 |   134
       31483281 |     31483506 |   226
       31483510 |     31483854 |   345
       31483870 |     31484061 |   192
       31484077 |     31484227 |   151
       31484340 |     31484543 |   204
       31484545 |     31485009 |   465
       31485027 |     31485137 |   111
       31485700 |     31485842 |   143
       33357720 |     33357913 |   194
       33358002 |     33358139 |   138
       33358387 |     33358511 |   125
       35355237 |     35355360 |   124
(15 rows)

Time: 218699.951 ms

Может кому пригодится аналогично подсчитать пропуски в последовательностях.

...

Рейтинг:

0 / 0

01.03.2019, 11:32

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&fpage=43&tid=1995313]:	0ms
get settings:	11ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	39ms
get topic data:	13ms
get forum data:	3ms
get page messages:	58ms
get tp. blocked users:	2ms
others:	16ms

total:	163ms