Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите оптимизировать запрос / 25 сообщений из 54, страница 1 из 3
  1  все
26.09.2008, 02:05
    #35561005
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Две таблицы. В первой - сотни миллионов записей. Во второй - миллионы. В обоих имеется поле типа text с уникальным индексом по нему.
Задача: нужно из третьей таблицы (тоже имеет поле типа text с уникальным индексом) выбрать записи, где значение этого поля не встречается ни в первой, ни во второй таблицах.
Я делаю:
SELECT field1 FROM table3 WHERE not EXISTS (SELECT 1 FROM table1 WHERE field1 = table3.field1) AND NOT EXISTS (SELECT 1 FROM table2 WHERE field1 = table3.field1)

Выполняется ну очень долго, когда в 3-й таблице десятки тысяч записей.
Прекрасно понимаю, что запрос очень тяжелый, но, возможно, есть возможность его изменить/оптимизировать...
Подскажите, как.

Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 02:31
    #35561007
DAISER
DAISER
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
может OUTER JOIN потом проверка на NULL?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 02:37
    #35561008
DAISER
DAISER
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
SELECT field1 FROM table3 t3 left join table1 t1 using(field1) left join table2 t2 using(field1) where not t1.field1 is null and not t2.field1 is null;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 02:58
    #35561011
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
ещё вот так можно: http://www.postgresql.org/docs/current/static/queries-union.html

Код: plaintext
1.
2.
3.
4.
5.
6.
select field1 from table3
except (
  select field1 from table2
  union
  select field1 from table1
)

--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 12:02
    #35561746
Funny_Falcon
Funny_Falcon
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Думаю DIASER имел в виду
Код: plaintext
1.
2.
SELECT field1 FROM table3 t3 left join table1 t1 using(field1) left join table2 t2 using(field1)
where t1.field1 is null and t2.field1 is null;
Еще вариант ( может быть чуть чуть быстрее, чем у DIASER, а может и нет )
Код: plaintext
1.
2.
3.
SELECT field1 FROM table3 t3 left join table1 t1 using(field1) where t1.field1 is null
INTERSECT
SELECT field1 FROM table3 t3 left join table2 t2 using(field1) where t2.field1 is null;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 12:21
    #35561824
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
нет там NULL вообще. абсолютно точно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 14:34
    #35562278
DAISER
DAISER
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
tier.ruнет там NULL вообще. абсолютно точно.
Когда LEFT JOIN сделаете -- появятся. И как раз в тех случаях, когда я для table3 нет записи в table1 и/или table2.

http://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-FROM

PostgreSQL Documentation LEFT OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.09.2008, 15:40
    #35562521
Jelis
Jelis
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
А еще, пожалуста,покжите планы (explain analyze select ...) , и вашего запроса, и тех что посоветовали! Интересно посмотреть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2008, 21:30
    #35564283
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Ни один из советов, к сожалению, не помог ускорить запрос.
Все тесты запускал для table3 с 20K записей.
Мой вариант выполняется строго ~10 мин.
План (уже для table3 со 100 записями):
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table3 (cost=0.00..9572.40 rows=25 width=83) (actual time=151.129..3649.923 rows=89 loops=1)
Filter: ((NOT (subplan)) AND (NOT (subplan)))
SubPlan
-> Index Scan using table1_field1_key on table1 (cost=0.00..87.28 rows=1 width=0) (actual time=25.057..25.057 rows=0 loops=100)
Index Cond: (field1 = $0)
-> Index Scan using table2_field1_key on table2 (cost=0.00..8.42 rows=1 width=0) (actual time=11.428..11.428 rows=0 loops=100)
Index Cond: (field1 = $0)
Total runtime: 3650.167 ms
(8 rows)

Теперь посоветованные.
Итак:
select field1 from table3
except (
select field1 from table2
union
select field1 from table1
)
это - очевидная чушь. памяти надо занять многие гигабайты, чтобы выполнить подзапрос. все-таки запускал для эксперимента. окончания не дождался за час. плана не будет.
перестартанул pg для чистоты эксперимента.

Следующий:
SELECT field1 FROM table3 t3 left join table1 t1 using(field1) left join table2 t2 using(field1)
where t1.field1 is null and t2.field1 is null;
Не дождался окончания за 30 мин. обрубил.
Но, для меньшего размера (100 записей) table3 работает . привожу план:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..5202.47 rows=25 width=83) (actual time=1069.094..13140.841 rows=89 loops=1)
Filter: (t1.field1 IS NULL)
-> Nested Loop Left Join (cost=0.00..837.76 rows=50 width=83) (actual time=263.194..3885.419 rows=100 loops=1)
Filter: (t2.field1 IS NULL)
-> Seq Scan on table3 t3 (cost=0.00..3.00 rows=100 width=83) (actual time=0.034..0.215 rows=100 loops=1)
-> Index Scan using table2_field1_key on table2 t2 (cost=0.00..8.34 rows=1 width=74) (actual time=38.847..38.847 rows=0 loops=100)
Index Cond: (t3.field1 = t2.field1)
-> Index Scan using table1_field1_key on table1 t1 (cost=0.00..87.28 rows=1 width=66) (actual time=92.548..92.548 rows=0 loops=100)
Index Cond: (t3.field1 = t1.field1)
Total runtime: 13141.092 ms
(10 rows)

Ну что, господа, есть еще идеи, что можно тут сделать?
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2008, 04:12
    #35564386
DAISER
DAISER
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Тут оптимизация запросов врядли поможет. Хотелось бы увидеть планы выполения, только без analyze. Analyze -- дело случая, от нагрузки на сервер и прочего зависит. Просто explain покажет самый быстрый запрос. По тем планами, что вы показали, ваш запрос действительно быстрее. Ускорить его врядли получится. Надо менять саму базу. Какие индескы у вас используются? b-tree? Попробуйте создать hash-индесы для table1.field1 и table2.field1.

http://www.postgresql.org/docs/current/static/indexes-types.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2008, 10:18
    #35564533
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
tier.ruВсе тесты запускал для table3 с 20K записей.
Мой вариант выполняется строго ~10 мин.покажите explain analyze этого запроса

tier.ruПлан (уже для table3 со 100 записями)планы для таблиц гораздо меньшего размера не интересны

tier.ruэто - очевидная чушь. памяти надо занять многие гигабайты, чтобы выполнить подзапрос.вы разработчик постгреса? знаете какие данные надо держать в памяти, а какие можно свопить на диск, и т.д.?

tier.ruвсе-таки запускал для эксперимента. окончания не дождался за час. плана не будет.
...
Не дождался окончания за 30 мин. обрубил.в этом случае показывайте explain без analyze

DAISERТут оптимизация запросов врядли поможет.-1

DAISERХотелось бы увидеть планы выполения, только без analyze.просто не читайте данные "(actual ...)", вот и будет explain без analyze
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2008, 20:29
    #35566291
DAISER
DAISER
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Собственное исследование:

3 таблицы. table1, table2, table3.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
test=# \d table1
                         Table "public.table1"
 Column |  Type  |                      Modifiers
--------+--------+-----------------------------------------------------
 id     | bigint | not null default nextval('table1_id_seq'::regclass)
 f1     | text   |
 f2     | text   |
 f3     | text   |
 f4     | text   |
 f5     | text   |
 f6     | text   |
 f7     | text   |
 f8     | text   |
 f9     | text   |
Indexes:
    "table1_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "table1_fk" FOREIGN KEY (id) REFERENCES tableref(id) ON UPDATE CASCADE ON DELETE CASCADE

"table2" и "table3" аналогичной структуры. Все поля в таблицах заполнены.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
test=# select count(*) from table1;
  count
---------
  2000000 
( 1  row)

test=# select count(*) from table2;
  count
---------
  3000000 
( 1  row)

test=# select count(*) from table3;
 count
-------
  50000 
( 1  row)

Запрос автора темы:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
test=# explain analyze select t3.id from table3 t3 where not exists(select  1  from table1 t1 where id = t3.id) and not exists(select  1  from table2 t2 where id = t3.id);
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on table3 t3  (cost= 0 . 00 .. 858492 . 00  rows= 12500  width= 8 ) (actual time= 18 . 289 .. 1673 . 585  rows= 49472  loops= 1 )
   Filter: ((NOT (subplan)) AND (NOT (subplan)))
   SubPlan
     ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 8 . 60  rows= 1  width= 0 ) (actual time= 0 . 012 .. 0 . 012  rows= 0  loops= 50000 )
           Index Cond: (id = $ 0 )
     ->  Index Scan using table1_pkey on table1 t1  (cost= 0 . 00 .. 8 . 52  rows= 1  width= 0 ) (actual time= 0 . 012 .. 0 . 012  rows= 0  loops= 50000 )
           Index Cond: (id = $ 0 )
 Total runtime:  1759 . 231  ms
( 8  rows)

Мой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
test=# explain analyze select t3.id from table3 t3 left join table1 t1 using (id) left join table2 t2 using(id) where t2.id is null and t1.id is null;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost= 136147 . 36 .. 349075 . 36  rows= 12500  width= 8 ) (actual time= 35276 . 038 .. 35276 . 038  rows= 0  loops= 1 )
   Filter: (t2.id IS NULL)
   ->  Hash Left Join  (cost= 136147 . 36 .. 148061 . 36  rows= 25000  width= 8 ) (actual time= 35276 . 032 .. 35276 . 032  rows= 0  loops= 1 )
         Hash Cond: (t3.id = t1.id)
         Filter: (t1.id IS NULL)
         ->  Seq Scan on table3 t3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 0 . 019 .. 114 . 303  rows= 50000  loops= 1 )
         ->  Hash  (cost= 103334 . 16 .. 103334 . 16  rows= 2000016  width= 8 ) (actual time= 31243 . 219 .. 31243 . 219  rows= 2000000  loops= 1 )
               ->  Seq Scan on table1 t1  (cost= 0 . 00 .. 103334 . 16  rows= 2000016  width= 8 ) (actual time= 31 . 346 .. 24592 . 940  rows= 2000000  loops= 1 )
   ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 8 . 03  rows= 1  width= 8 ) (never executed)
         Index Cond: (t3.id = t2.id)
 Total runtime:  35278 . 239  ms
( 11  rows)

Ёш:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
test=# explain analyze select id from table3 except (
  select id from table2
  union
  select id from table1);
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 SetOp Except  (cost= 1592425 . 18 .. 1617675 . 18  rows= 505000  width= 8 ) (actual time= 244428 . 882 .. 244710 . 593  rows= 49472  loops= 1 )
   ->  Sort  (cost= 1592425 . 18 .. 1605050 . 18  rows= 5050000  width= 8 ) (actual time= 222543 . 531 .. 232832 . 606  rows= 5049564  loops= 1 )
         Sort Key: "*SELECT* 1".id
         Sort Method:  external sort  Disk: 118416kB
         ->  Append  (cost= 0 . 00 .. 961117 . 42  rows= 5050000  width= 8 ) (actual time= 24 . 728 .. 184572 . 740  rows= 5049564  loops= 1 )
               ->  Subquery Scan "*SELECT* 1"  (cost= 0 . 00 .. 3084 . 00  rows= 50000  width= 8 ) (actual time= 24 . 724 .. 965 . 799  rows= 50000  loops= 1 )
                     ->  Seq Scan on table3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 24 . 716 .. 801 . 140  rows= 50000  loops= 1 )
               ->  Result  (cost= 933033 . 42 .. 958033 . 42  rows= 5000000  width= 8 ) (actual time= 120155 . 248 .. 169067 . 746  rows= 4999564  loops= 1 )
                     ->  Unique  (cost= 933033 . 42 .. 958033 . 42  rows= 5000000  width= 8 ) (actual time= 120155 . 234 .. 152921 . 409  rows= 4999564  loops= 1 )
                           ->  Sort  (cost= 933033 . 42 .. 945533 . 42  rows= 5000000  width= 8 ) (actual time= 120155 . 224 .. 135082 . 599  rows= 5000000  loops= 1 )
                                 Sort Key: table2.id
                                 Sort Method:  external merge  Disk: 97704kB
                                 ->  Append  (cost= 0 . 00 .. 308334 . 00  rows= 5000000  width= 8 ) (actual time= 32 . 385 .. 80210 . 943  rows= 5000000  loops= 1 )
                                       ->  Seq Scan on table2  (cost= 0 . 00 .. 155000 . 00  rows= 3000000  width= 8 ) (actual time= 32 . 381 .. 46147 . 665  rows= 3000000  loops= 1 )
                                       ->  Seq Scan on table1  (cost= 0 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 23 . 380 .. 18966 . 059  rows= 2000000  loops= 1 )
 Total runtime:  245042 . 599  ms
( 16  rows)

Funny_Falcon:
Код: plaintext
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.
test=# explain analyze SELECT id FROM table3 t3 left join table1 t1 using(id) where t1.id is null
INTERSECT
SELECT id FROM table3 t3 left join table2 t2 using(id) where t2.id is null;
                                                                                QUERY PLAN                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 SetOp Intersect  (cost= 366897 . 24 .. 367147 . 24  rows= 5000  width= 8 ) (actual time= 109677 . 974 .. 110121 . 156  rows= 49472  loops= 1 )
   ->  Sort  (cost= 366897 . 24 .. 367022 . 24  rows= 50000  width= 8 ) (actual time= 109675 . 030 .. 109839 . 909  rows= 99472  loops= 1 )
         Sort Key: "*SELECT* 1".id
         Sort Method:  quicksort  Memory: 5934kB
         ->  Append  (cost= 136147 . 00 .. 362994 . 83  rows= 50000  width= 8 ) (actual time= 30429 . 604 .. 109070 . 711  rows= 99472  loops= 1 )
               ->  Subquery Scan "*SELECT* 1"  (cost= 136147 . 00 .. 148311 . 00  rows= 25000  width= 8 ) (actual time= 30429 . 600 .. 34595 . 186  rows= 50000  loops= 1 )
                     ->  Hash Left Join  (cost= 136147 . 00 .. 148061 . 00  rows= 25000  width= 8 ) (actual time= 30429 . 591 .. 34435 . 488  rows= 50000  loops= 1 )
                           Hash Cond: (t3.id = t1.id)
                           Filter: (t1.id IS NULL)
                           ->  Seq Scan on table3 t3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 25 . 305 .. 142 . 227  rows= 50000  loops= 1 )
                           ->  Hash  (cost= 103334 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 30403 . 112 .. 30403 . 112  rows= 2000000  loops= 1 )
                                 ->  Seq Scan on table1 t1  (cost= 0 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 9 . 553 .. 23881 . 827  rows= 2000000  loops= 1 )
               ->  Subquery Scan "*SELECT* 2"  (cost= 210394 . 54 .. 214683 . 83  rows= 25000  width= 8 ) (actual time= 72728 . 839 .. 74183 . 797  rows= 49472  loops= 1 )
                     ->  Merge Left Join  (cost= 210394 . 54 .. 214433 . 83  rows= 25000  width= 8 ) (actual time= 72728 . 829 .. 74024 . 530  rows= 49472  loops= 1 )
                           Merge Cond: (t3.id = t2.id)
                           Filter: (t2.id IS NULL)
                           ->  Index Scan using table3_pkey on table3 t3  (cost= 0 . 00 .. 3393 . 26  rows= 50000  width= 8 ) (actual time= 22 . 544 .. 1285 . 434  rows= 50000  loops= 1 )
                           ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 202915 . 58  rows= 3000000  width= 8 ) (actual time= 84 . 845 .. 66509 . 991  rows= 3000000  loops= 1 )
 Total runtime:  110207 . 660  ms
( 19  rows)

Создаем hash-индексы

Код: plaintext
1.
2.
3.
4.
5.
6.
test=# create index table3_hash_idx on table3 using hash(id);
CREATE INDEX
test=# create index table2_hash_idx on table2 using hash(id);
CREATE INDEX
test=# create index table1_hash_idx on table1 using hash(id);
CREATE INDEX

Запрос автора:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
test=# explain analyze select t3.id from table3 t3 where not exists(select  1  from table1 t1 where id = t3.id) and not exists(select  1  from table2 t2 where id = t3.id);
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on table3 t3  (cost= 0 . 00 .. 858492 . 00  rows= 12500  width= 8 ) (actual time= 19 . 509 .. 2243 . 110  rows= 49472  loops= 1 )
   Filter: ((NOT (subplan)) AND (NOT (subplan)))
   SubPlan
     ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 8 . 60  rows= 1  width= 0 ) (actual time= 0 . 014 .. 0 . 014  rows= 0  loops= 50000 )
           Index Cond: (id = $ 0 )
     ->  Index Scan using table1_pkey on table1 t1  (cost= 0 . 00 .. 8 . 52  rows= 1  width= 0 ) (actual time= 0 . 015 .. 0 . 015  rows= 0  loops= 50000 )
           Index Cond: (id = $ 0 )
 Total runtime:  2346 . 373  ms
( 8  rows)

Мой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
test=# explain analyze select t3.id from table3 t3 left join table1 t1 using (id) left join table2 t2 using(id) where t2.id is null and t1.id is null;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost= 136147 . 00 .. 349075 . 00  rows= 12500  width= 8 ) (actual time= 30016 . 651 .. 34328 . 900  rows= 49472  loops= 1 )
   Filter: (t2.id IS NULL)
   ->  Hash Left Join  (cost= 136147 . 00 .. 148061 . 00  rows= 25000  width= 8 ) (actual time= 29933 . 095 .. 33305 . 204  rows= 50000  loops= 1 )
         Hash Cond: (t3.id = t1.id)
         Filter: (t1.id IS NULL)
         ->  Seq Scan on table3 t3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 25 . 126 .. 149 . 392  rows= 50000  loops= 1 )
         ->  Hash  (cost= 103334 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 29863 . 427 .. 29863 . 427  rows= 2000000  loops= 1 )
               ->  Seq Scan on table1 t1  (cost= 0 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 9 . 567 .. 23683 . 422  rows= 2000000  loops= 1 )
   ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 8 . 03  rows= 1  width= 8 ) (actual time= 0 . 014 .. 0 . 014  rows= 0  loops= 50000 )
         Index Cond: (t3.id = t2.id)
 Total runtime:  34420 . 689  ms
( 11  rows)

Результаты выполнения других двух запросов тоже не изменились. Так что с индексами я погорячился. Что ещё можно попробовать?

P.S.: у меня запрос tier.ru выполнялся 1.5-2.5 сек (50 000 записей в table3). Конфигурация, только не смейтесь, P-III 800MHz/384Mb/OpenSUSE 10.2. PostgreSQL 8.3.4, work_mem = 8MB.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 10:24
    #35566875
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
я чё-то не понял, почему вы hash-индексы по полю id создаете?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 11:09
    #35567037
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
DAISERМой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
test=# explain analyze select t3.id from table3 t3 left join table1 t1 using (id) left join table2 t2 using(id) where t2.id is null and t1.id is null;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost= 136147 . 36 .. 349075 . 36  rows= 12500  width= 8 ) (actual time= 35276 . 038 .. 35276 . 038  rows= 0  loops= 1 )
   Filter: (t2.id IS NULL)
   ->  Hash Left Join  (cost= 136147 . 36 .. 148061 . 36  rows= 25000  width= 8 ) (actual time= 35276 . 032 .. 35276 . 032  rows= 0  loops= 1 )
         Hash Cond: (t3.id = t1.id)
         Filter: (t1.id IS NULL)
         ->  Seq Scan on table3 t3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 0 . 019 .. 114 . 303  rows= 50000  loops= 1 )
         ->  Hash  (cost= 103334 . 16 .. 103334 . 16  rows= 2000016  width= 8 ) (actual time= 31243 . 219 .. 31243 . 219  rows= 2000000  loops= 1 )
               ->  Seq Scan on table1 t1  (cost= 0 . 00 .. 103334 . 16  rows= 2000016  width= 8 ) (actual time= 31 . 346 .. 24592 . 940  rows= 2000000  loops= 1 )
   ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 8 . 03  rows= 1  width= 8 ) (never executed)
         Index Cond: (t3.id = t2.id)
 Total runtime:  35278 . 239  ms
( 11  rows)
(actual ... rows=0 ...) - ваш запрос выдал 0 строк, что-то не так.

лучше получить результаты тестов от автора, они могут отличаться от ваших, потому что например таблицы замусорены удаленными строками или статистика не собрана или собрана менее подробно или по другим причинам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 11:46
    #35567171
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Вообще фигня какая-то в тестах DAISER. Почему во всех ваших запросах фигурирует поле-счетчик id, в то время как у меня - текстовое поле field1?

\d table1
Table "public.table1"
Column | Type | Modifiers
--------------+-----------------------------+----------------------------------------------------------
id | integer | not null default nextval('table1_id_seq'::regclass)
field1 | text |
field2 | text |
cdate | timestamp without time zone | default now()
field3 | text |
flags | integer | default 0
status | smallint | not null default (-10)
field4 | text |
pr | integer |
field5 | integer | default 0
Indexes:
"table1_pkey" PRIMARY KEY, btree (id)
"table1_field1_key" UNIQUE, btree (field1)
"table1_field3_index" UNIQUE, btree (field3)
"table1_status_index" btree (status)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 11:52
    #35567184
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
OFFTOPIC: DAISER, для ускорения этих запросов надо избавиться от HashJoin по t1 и от MergeJoin по t2 в пользу NestedLoop
DAISERМой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
test=# explain analyze select t3.id from table3 t3 left join table1 t1 using (id) left join table2 t2 using(id) where t2.id is null and t1.id is null;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost= 136147 . 36 .. 349075 . 36  rows= 12500  width= 8 ) (actual time= 35276 . 038 .. 35276 . 038  rows= 0  loops= 1 )
   Filter: (t2.id IS NULL)
   ->  Hash Left Join  (cost= 136147 . 36 .. 148061 . 36  rows= 25000  width= 8 ) (actual time= 35276 . 032 .. 35276 . 032  rows= 0  loops= 1 )
         Hash Cond: (t3.id = t1.id)
         Filter: (t1.id IS NULL)
         ->  Seq Scan on table3 t3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 0 . 019 .. 114 . 303  rows= 50000  loops= 1 )
         ->  Hash  (cost= 103334 . 16 .. 103334 . 16  rows= 2000016  width= 8 ) (actual time= 31243 . 219 .. 31243 . 219  rows= 2000000  loops= 1 )
               ->  Seq Scan on table1 t1  (cost= 0 . 00 .. 103334 . 16  rows= 2000016  width= 8 ) (actual time= 31 . 346 .. 24592 . 940  rows= 2000000  loops= 1 )
   ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 8 . 03  rows= 1  width= 8 ) (never executed)
         Index Cond: (t3.id = t2.id)
 Total runtime:  35278 . 239  ms
( 11  rows)

Funny_Falcon:
Код: plaintext
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.
test=# explain analyze SELECT id FROM table3 t3 left join table1 t1 using(id) where t1.id is null
INTERSECT
SELECT id FROM table3 t3 left join table2 t2 using(id) where t2.id is null;
                                                                                QUERY PLAN                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 SetOp Intersect  (cost= 366897 . 24 .. 367147 . 24  rows= 5000  width= 8 ) (actual time= 109677 . 974 .. 110121 . 156  rows= 49472  loops= 1 )
   ->  Sort  (cost= 366897 . 24 .. 367022 . 24  rows= 50000  width= 8 ) (actual time= 109675 . 030 .. 109839 . 909  rows= 99472  loops= 1 )
         Sort Key: "*SELECT* 1".id
         Sort Method:  quicksort  Memory: 5934kB
         ->  Append  (cost= 136147 . 00 .. 362994 . 83  rows= 50000  width= 8 ) (actual time= 30429 . 604 .. 109070 . 711  rows= 99472  loops= 1 )
               ->  Subquery Scan "*SELECT* 1"  (cost= 136147 . 00 .. 148311 . 00  rows= 25000  width= 8 ) (actual time= 30429 . 600 .. 34595 . 186  rows= 50000  loops= 1 )
                     ->  Hash Left Join  (cost= 136147 . 00 .. 148061 . 00  rows= 25000  width= 8 ) (actual time= 30429 . 591 .. 34435 . 488  rows= 50000  loops= 1 )
                           Hash Cond: (t3.id = t1.id)
                           Filter: (t1.id IS NULL)
                           ->  Seq Scan on table3 t3  (cost= 0 . 00 .. 2584 . 00  rows= 50000  width= 8 ) (actual time= 25 . 305 .. 142 . 227  rows= 50000  loops= 1 )
                           ->  Hash  (cost= 103334 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 30403 . 112 .. 30403 . 112  rows= 2000000  loops= 1 )
                                 ->  Seq Scan on table1 t1  (cost= 0 . 00 .. 103334 . 00  rows= 2000000  width= 8 ) (actual time= 9 . 553 .. 23881 . 827  rows= 2000000  loops= 1 )
               ->  Subquery Scan "*SELECT* 2"  (cost= 210394 . 54 .. 214683 . 83  rows= 25000  width= 8 ) (actual time= 72728 . 839 .. 74183 . 797  rows= 49472  loops= 1 )
                     ->  Merge Left Join  (cost= 210394 . 54 .. 214433 . 83  rows= 25000  width= 8 ) (actual time= 72728 . 829 .. 74024 . 530  rows= 49472  loops= 1 )
                           Merge Cond: (t3.id = t2.id)
                           Filter: (t2.id IS NULL)
                           ->  Index Scan using table3_pkey on table3 t3  (cost= 0 . 00 .. 3393 . 26  rows= 50000  width= 8 ) (actual time= 22 . 544 .. 1285 . 434  rows= 50000  loops= 1 )
                           ->  Index Scan using table2_pkey on table2 t2  (cost= 0 . 00 .. 202915 . 58  rows= 3000000  width= 8 ) (actual time= 84 . 845 .. 66509 . 991  rows= 3000000  loops= 1 )
 Total runtime:  110207 . 660  ms
( 19  rows)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 11:54
    #35567188
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
tier.ru, почему не показываете explain analyze?

LeXa NalBat tier.ruВсе тесты запускал для table3 с 20K записей.
Мой вариант выполняется строго ~10 мин.покажите explain analyze этого запроса
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 12:00
    #35567206
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
показал же.
чтоб не искали в предыдущих постах, вот ще раз:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table3 (cost=0.00..9572.40 rows=25 width=83) (actual time=151.129..3649.923 rows=89 loops=1)
Filter: ((NOT (subplan)) AND (NOT (subplan)))
SubPlan
-> Index Scan using table1_field1_key on table1 (cost=0.00..87.28 rows=1 width=0) (actual time=25.057..25.057 rows=0 loops=100)
Index Cond: (field1 = $0)
-> Index Scan using table2_field1_key on table2 (cost=0.00..8.42 rows=1 width=0) (actual time=11.428..11.428 rows=0 loops=100)
Index Cond: (field1 = $0)
Total runtime: 3650.167 ms
(8 rows)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 12:04
    #35567212
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
tier.ruпоказал же... actual rows=89 ...не то. обещали "20K записей" и "~10 мин". как я писал, "планы для таблиц гораздо меньшего размера не интересны".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 12:16
    #35567245
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
Я не настолько силен в базах данных, но по-моему, это один х.
Но, все-таки покажу щас. Подождите.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 12:42
    #35567347
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table3 (cost=0.00..1928003.20 rows=5000 width=70) (actual time=100781.649..490673.590 rows=61 loops=1)
Filter: ((NOT (subplan)) AND (NOT (subplan)))
SubPlan
-> Index Scan using table1_field1_key on table1 (cost=0.00..87.99 rows=1 width=0) (actual time=22.942..22.942 rows=1 loops=19999)
Index Cond: (field1 = $0)
-> Index Scan using table2_field1_key on table2 (cost=0.00..8.38 rows=1 width=0) (actual time=1.580..1.580 rows=0 loops=20000)
Index Cond: (field1 = $0)
Total runtime: 490673.753 ms
(8 rows)

И теперь, если не трудно, объясните мне, малограмотному, чем вас не устроил план запроса по 100 записям и зачем надо было выполнять его еще по 20K?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 12:58
    #35567401
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
tier.ru-> Index Scan using table1_field1_key on table1 (actual time=22.942..22.942 rows=1)кажется, что поиск по индексу работает медленно. попробуйте drop index table1_field1_key, vacuum full table1, create index table1_field1_key. и покажите получившийся explain analyze.

tier.ruИ теперь, если не трудно, объясните мне, малограмотному, чем вас не устроил план запроса по 100 записям и зачем надо было выполнять его еще по 20K?в частности для того, чтобы разбираясь с проблемой, щупать её саму, а не тестовый пример.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 13:27
    #35567477
tier.ru
tier.ru
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
vacuum full такой огромной таблицы даже на нашем очень мощном железе занимает дня два. уже пробовал. даже бэкап/рестор всей базы и то быстрее (часов за 6 делается).
maintenance_work_mem = 512MB
postgresql-8.3.3
а это продакшн-система. так что, неосуществимо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 15:27
    #35567838
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
возможно, медленно работает поиск по индексу table1_field1_key из-за того, что вследствии update и delete накопилось много устаревших версий строк в таблице или индексе. как идет работа с таблицами table1 и table2, строки в них только добавляются или еще и изменяются и удаляются?

покажите пожалуйста статистику:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select oid,*
        pg_stat_get_tuples_inserted(oid),
        pg_stat_get_tuples_updated(oid),
        pg_stat_get_tuples_deleted(oid),
        pg_stat_get_tuples_hot_updated(oid),
        pg_stat_get_live_tuples(oid),
        pg_stat_get_dead_tuples(oid)
from pg_class where relname in ( 'table1', 'table2', 'table3', 'table1_field1_key', 'table2_field1_key' );

select * from pg_statistic where starelid in ( select oid from pg_class
        where relname in ( 'table1', 'table2', 'table3', 'table1_field1_key', 'table2_field1_key' ) );

tier.ruvacuum full такой огромной таблицы даже на нашем очень мощном железе занимает дня два. уже пробовал. даже бэкап/рестор всей базы и то быстрее (часов за 6 делается).пересоздать индекс тоже никак?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2008, 15:36
    #35567858
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите оптимизировать запрос
покажите ещё пожалуйста

explain analyze SELECT field1 FROM table3 WHERE not EXISTS (SELECT 1 FROM table1 WHERE field1 = table3.field1);

explain analyze SELECT field1 FROM table3 WHERE not EXISTS (SELECT 1 FROM table2 WHERE field1 = table3.field1);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите оптимизировать запрос / 25 сообщений из 54, страница 1 из 3
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]