ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите оптимизировать запрос
54 сообщений из 54, показаны все 3 страниц
  все  
помогите оптимизировать запрос
    #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:05
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35561007
DAISER
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DAISER
Гость
может OUTER JOIN потом проверка на NULL?
...
Рейтинг: 0 / 0
26.09.2008, 02:31
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:37
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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, 02:58
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:02
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35561824
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
нет там NULL вообще. абсолютно точно.
...
Рейтинг: 0 / 0
26.09.2008, 12:21
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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, 14:34
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35562521
Jelis
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jelis
Гость
А еще, пожалуста,покжите планы (explain analyze select ...) , и вашего запроса, и тех что посоветовали! Интересно посмотреть.
...
Рейтинг: 0 / 0
26.09.2008, 15:40
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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
28.09.2008, 21:30
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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, 04:12
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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, 10:18
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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
29.09.2008, 20:29
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35566875
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
я чё-то не понял, почему вы hash-индексы по полю id создаете?
...
Рейтинг: 0 / 0
30.09.2008, 10:24
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:09
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:46
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:52
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35567188
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
tier.ru, почему не показываете explain analyze?

LeXa NalBat tier.ruВсе тесты запускал для table3 с 20K записей.
Мой вариант выполняется строго ~10 мин.покажите explain analyze этого запроса
...
Рейтинг: 0 / 0
30.09.2008, 11:54
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:00
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35567212
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
tier.ruпоказал же... actual rows=89 ...не то. обещали "20K записей" и "~10 мин". как я писал, "планы для таблиц гораздо меньшего размера не интересны".
...
Рейтинг: 0 / 0
30.09.2008, 12:04
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35567245
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
Я не настолько силен в базах данных, но по-моему, это один х.
Но, все-таки покажу щас. Подождите.
...
Рейтинг: 0 / 0
30.09.2008, 12:16
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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:42
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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, 12:58
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35567477
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
vacuum full такой огромной таблицы даже на нашем очень мощном железе занимает дня два. уже пробовал. даже бэкап/рестор всей базы и то быстрее (часов за 6 делается).
maintenance_work_mem = 512MB
postgresql-8.3.3
а это продакшн-система. так что, неосуществимо.
...
Рейтинг: 0 / 0
30.09.2008, 13: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:27
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #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
30.09.2008, 15:36
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35568319
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Выполняется ну очень долго, когда в 3-й таблице десятки тысяч записей.
Прекрасно понимаю, что запрос очень тяжелый, но, возможно, есть возможность его изменить/оптимизировать...
Подскажите, как.

Реляционное вычитание ( а тут именно оно применяется ) -- это очень сложная операция,
её сложность - O ( N * M ) , где N и M - кол-во записей в "уменьшаемой" и "вычитаемой" таблицах.
СУБД при наличии индексов на поля, по которым происходит вычитание, сделает это за
O ( N * log M ) , у вас их два, тогда будет

O ( N * log M1 * log M2 )

Если это у вас table3 имеет десятки тыщ записей, то вот у вас и будет десятки тыщ записей обрабатываться,
плюс ещё помноженные на два логарифма.

Вот и попробуйте прикинуть, сколько это хотя бы в теории должно выполняться,
чтение одной записи - считайте порядка 10 msec.

Изменить/оптимизировать его нельзя, по одной простой причине - чудес не бывает.
Вам остаётся только убедиться, что индексы есть, что они используются, и что они оптимальные.
Больше тут ничего не сделаешь.

Есть два пути для обдумывания, которые мне приходят сходу на ум:

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


тексты - не самые лучшие поля для индексирования, возможно, можно создать словарь текстов, и вычитание проводить по идентификаторам слов. Оно по идее будет немного (незначительно) быстрее, но если у вас большие реально объёмы данных, этот выигрыш может накапливаться и давать ощутимую пользу. Вроде бы у вас все эти поля уникальные, тогда по идее они 1:1 соотносятся с первичным ключём и можно просто вычитать по нему, только надо чтобы идентификаторы были в одном пространстве ключей.

Больше что-то придумать трудно.
...
Рейтинг: 0 / 0
30.09.2008, 18:27
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35568322
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
OFFTOPIC: DAISER, для ускорения этих запросов надо избавиться от HashJoin по t1 и от MergeJoin по t2 в пользу NestedLoop

+1. NLJ тут рулит фореве, поскольку ему надо ТОЛЬКО С ПЕРВОЙ ЗАПИСЬЮ сджойниться или не сджойниться.
...
Рейтинг: 0 / 0
30.09.2008, 18:30
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35568324
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
LeXa NalBatвозможно, медленно работает поиск по индексу table1_field1_key из-за того, что вследствии update и delete накопилось много устаревших версий строк в таблице или индексе.

что значить "медленно" ? Всё равно log N. ну сколько там этих устаревших записей? ну 10 процентов.
Это - капля в море. Так что совет плохой.
...
Рейтинг: 0 / 0
30.09.2008, 18:32
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35568450
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
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);

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table3 (cost=0.00..1764082.80 rows=10000 width=70) (actual time=16672.434..299867.119 rows=61 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using table1_field1_key on table1 (cost=0.00..88.18 rows=1 width=0) (actual time=14.987..14.987 rows=1 loops=20000)
Index Cond: (field1 = $0)
Total runtime: 299867.281 ms
(6 rows)

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table3 (cost=0.00..168146.80 rows=10000 width=70) (actual time=138.135..4727.714 rows=19999 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using table2_field1_key on table2 (cost=0.00..8.38 rows=1 width=0) (actual time=0.234..0.234 rows=0 loops=20000)
Index Cond: (field1 = $0)
Total runtime: 4732.318 ms
(6 rows)
...
Рейтинг: 0 / 0
30.09.2008, 20:08
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35569057
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
tier.ruSeq Scan on table3 (actual rows=61)
-> Index Scan using table1_field1_key on table1

Seq Scan on table3 (actual rows=19999)
-> Index Scan using table2_field1_key on table2видно, что почти все строки из table3 отсутствуют в table2, и лишь 61 строка отсутствует в table1. поэтому надо попытаться заставить постгрес строки из table3 сначала проверять по таблице table1, и лишь затем немногие прошедшие эту проверку проверять по table2. так как времена сканов по индексам table1 и table2 примерно равны соответственно 23 и 1.6 миллисекунды, то это ускорит запрос совсем немного, примерно на 6 процентов. (эх, если бы оказалось наоборот, что много строк не удовлетворяет проверке по table2, которая быстрая.)

1) как изменяются данные в table1 и table2? 2) статистику покажете? тынц

MasterZivСУБД при наличии индексов на поля, по которым происходит вычитание, сделает это за O ( N * log M ) , у вас их два, тогда будет O ( N * log M1 * log M2 )O ( N * log M1 ) + O ( N * log M2 ) ?

MasterZiv+1. NLJ тут рулит фореве, поскольку ему надо ТОЛЬКО С ПЕРВОЙ ЗАПИСЬЮ сджойниться или не сджойниться.нет, NestedLoop будет быстрее потому, что, в отличие от HashJoin и MergeJoin, не надо будет целиком читать большие таблицы t1 и t2. джоин идет по уникальным полям, поэтому соединение "только с первой записью" будет для любого типа.

MasterZivВсё равно log N.о-большое о-большому - рознь. начинается борьба за константы.

MasterZivну сколько там этих устаревших записей? ну 10 процентов.однажды на боевой базе заметили замедление. оказалось, что не выполнялся vacuum, а данные при этом обновлялись целиком ежедневно. за месяц три тысячи процентов лишних неберется, а заметили кажется через несколько месяцев.
...
Рейтинг: 0 / 0
01.10.2008, 10:41
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35569328
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
фантастика: если закэшировать в памяти стомиллионную таблицу с её индексом, то запрос ускорится на несколько порядков.

реальность: надо ускорять чтение с диска таблицы table1 и её индекса. разнесение по разным дискам таблицы и индекса, быстрые диски, ускоряющий рэйд.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
create table table3 as select generate_series( 1 , 1000000000 , 15625 ) as id; create index index3 on table3 ( id );
create table table2 as select generate_series( 1 , 1000000000 , 243   ) as id; create index index2 on table2 ( id );
create table table1 as select generate_series( 1 , 1000000000 , 2     ) as id; create index index1 on table1 ( id );

select count(*) from table1 union all select count(*) from table2 union all select count(*) from table3;

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);
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;
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;

drop table table1;
drop table table2;
drop table table3;

Код: 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.
26.
27.
28.
29.
30.
nalbat=> 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 .. 4597690 . 72  rows= 16000  width= 4 ) (actual time= 105 . 554 .. 917860 . 169  rows= 31868  loops= 1 )
   Filter: ((NOT (subplan)) AND (NOT (subplan)))
   SubPlan
     ->  Index Scan using index1 on table1 t1  (cost= 0 . 00 .. 63 . 11  rows= 1  width= 0 ) (actual time= 14 . 312 .. 14 . 312  rows= 0  loops= 63736 )
           Index Cond: (id = $ 0 )
     ->  Index Scan using index2 on table2 t2  (cost= 0 . 00 .. 8 . 72  rows= 1  width= 0 ) (actual time= 0 . 076 .. 0 . 076  rows= 0  loops= 64000 )
           Index Cond: (id = $ 0 )
 Total runtime:  917947 . 083  ms
( 8  rows)

Time:  917947 . 655  ms

nalbat=> 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 .. 4597690 . 72  rows= 16000  width= 4 ) (actual time= 0 . 147 .. 2098 . 945  rows= 31868  loops= 1 )
   Filter: ((NOT (subplan)) AND (NOT (subplan)))
   SubPlan
     ->  Index Scan using index1 on table1 t1  (cost= 0 . 00 .. 63 . 11  rows= 1  width= 0 ) (actual time= 0 . 020 .. 0 . 020  rows= 0  loops= 63736 )
           Index Cond: (id = $ 0 )
     ->  Index Scan using index2 on table2 t2  (cost= 0 . 00 .. 8 . 72  rows= 1  width= 0 ) (actual time= 0 . 007 .. 0 . 007  rows= 0  loops= 64000 )
           Index Cond: (id = $ 0 )
 Total runtime:  2163 . 381  ms
( 8  rows)

Time:  2163 . 950  ms
...
Рейтинг: 0 / 0
01.10.2008, 12:06
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35570430
DAISER
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DAISER
Гость
LeXa NalBatреальность: надо ускорять чтение с диска таблицы table1 и её индекса. разнесение по разным дискам таблицы и индекса, быстрые диски, ускоряющий рэйд.
А я это ещё на прошлой странице сказал...
...
Рейтинг: 0 / 0
01.10.2008, 17:46
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35570809
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
сервер более чем достойный. много процов, памяти, SCSI райд и т.д. еще дороже возможности взять нет :(
думал, тут виртуозы sql что-то в плане оптимизации запроса помогут сделать...
...
Рейтинг: 0 / 0
01.10.2008, 21:00
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35571016
Konstantin~
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konstantin~
Участник
tier.ru... тут виртуозы sql что-то в плане оптимизации запроса помогут сделать...

ну почему же, вам вроде осмысленные советы дают: или ускорить чтените таблицы1 (путем частичного загона в кеш или диски быстрее) или де-нормализациа в каком-либо виде, т.е. хранить результат наличия t3.field1 в t2.field1 and t2 field.1 (индех по функции, доп.поле в t3, etc)
...
Рейтинг: 0 / 0
02.10.2008, 00:26
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35572578
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
<<путем частичного загона в кеш
это как?
...
Рейтинг: 0 / 0
02.10.2008, 15:08
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35573626
Konstantin~
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konstantin~
Участник
если памяти достаточнo , то повторный запрос выполняется намного быстрее так как таблица оседает в [linux VFS layer] кеше после первого запроса. (см. последний пост LeXa NalBat: планы по кол-ву дисковых операций одинаковы а время исполненния очень сильно отличаестя)

у вас база на чем крутится, размера какого и памяти сколько?
...
Рейтинг: 0 / 0
03.10.2008, 00:16
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35573664
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
CentOS 5 64bit
8 гигов памяти. вся база - 150 гигов.
...
Рейтинг: 0 / 0
03.10.2008, 01:16
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574087
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
Попробую объяснить, зачем вообще понадобилось такие вещи городить, и, возможно, посоветуете, как можно решить те же задачи, но по-другому, с целью повышения производительности.
Итак, у нас есть некая программа, которой пользуются несколько сотен пользователей. Все данные хранятся в базе постгреса. Упрощенно говоря, каждому пользователю принадлежит множество записей с некоей текстовой информацией. Задача - обеспечение уникальности этой текстовой информации. Существующая сейчас структура базы данных уже является результатом денормализации. Первоначально, все записи всех пользователей хранились в одной огромной таблице. Поддерживать уникальность было просто - всего лишь уникальным ключом по этому текстовому полю. Но, т.к. данные пользователями активно добавляются, чуть менее активно модифицируются (но, не это поле, а другие, второстепенные) и иногда удаляются, то имея одну таблицу имели огромное торможение.
Разнесли данные по отдельным таблицам. Каждому пользователю - свою (это те таблицы, которые фигурировали в моих примерах, как table2). Теперь они друг другу не мешают вообще - каждый работает только со своей таблицей. Но, поддерживать уникальность значений названного текстового поля в пределах всей системы по-прежнему надо. Для этого имеем table1, в которой хранятся все те же текстовые данные с уникальным ключом по этому полю. При попытке пользователя добавить новую порцию данных в свою таблицу, сначала убираем из нее (порции) те, которые уже есть в большой table1, а затем - те, которые уже есть в его (пользователя) таблице table2. Что осталось - инсертим по очереди в table1 и table2. Это, как раз тот запрос, который я приводил, и который меня не устраивает по скорости. Сразу скажу, что выполняется он отложенно, и не пользовательскими приложениями, а отдельной одной единственной специальной программой, поэтому concurrency по инсертам нет никакой.
Ну, собственно, вопрос в том, можно ли эту задачу решить как-то иначе, чтобы поиметь существенный выигрыш в производительности?

Спасибо.
...
Рейтинг: 0 / 0
03.10.2008, 10:38
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574443
Konstantin~
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konstantin~
Участник
нда, просто добавить памяти походу не получиться

исходя из дополнительной инфы, я бы сделал примерно следущее:

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

2. при добавлении теск-инфы вычислять ее хеш (sha1 наример) в десятичной форме и по этой функции построить уникальный индекс. Проверка по такому индексу не должна давать задержек. Можно также хранить этот хеш в отдельном поле, возможно будет просще чем связыватся с индексами по функции.

3. деление на партиции по пункту 1 наверно надо организовать так чтоб максимально уменьшить время работы с уникальным индексом для текст-инфы, т..е делить надо будет используя ranges по хешу.

Еще наверно имеет смысл и юзеров раскидать по партициям, чтобы все данные одного юзера вседа были в одной партиции/суб-таблице.
...
Рейтинг: 0 / 0
03.10.2008, 12:17
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574577
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
как бы не сделал разделение - после этого уже не получится одним запросом кучу записей проверять на уникальность/инсертить. придется по-одному, что уже замедление.
...
Рейтинг: 0 / 0
03.10.2008, 12:50
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574643
Konstantin~
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konstantin~
Участник
почему?

если таблица разбита по хешу то легко:

BEFORE UPDATE/INSERT вычисляешь хеш , далее операция производистья только с суб-таблицей где хранятся записи с кешами в определенном диапазоне. остальные таблици при этом не трогаются.

PS: идея с раскидыванием юзер по суб-таблицам не пройдет.
...
Рейтинг: 0 / 0
03.10.2008, 13:08
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574664
Konstantin~
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konstantin~
Участник
кстати если таблица разделена с помощью наследования, то вы работаете с одной таблицей. разделение на суб-таблици происходит автоматически.


ПС: при предложеннной схеме денормализация особо не нужна
...
Рейтинг: 0 / 0
03.10.2008, 13:12
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574687
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
tier.ruПопробую объяснить, зачем вообще понадобилось такие вещи городить, и, возможно, посоветуете, как можно решить те же задачи, но по-другому, с целью повышения производительности.
Итак, у нас есть некая программа, которой пользуются несколько сотен пользователей. Все данные хранятся в базе постгреса. Упрощенно говоря, каждому пользователю принадлежит множество записей с некоей текстовой информацией. Задача - обеспечение уникальности этой текстовой информации. Существующая сейчас структура базы данных уже является результатом денормализации. Первоначально, все записи всех пользователей хранились в одной огромной таблице. Поддерживать уникальность было просто - всего лишь уникальным ключом по этому текстовому полю. Но, т.к. данные пользователями активно добавляются, чуть менее активно модифицируются (но, не это поле, а другие, второстепенные) и иногда удаляются, то имея одну таблицу имели огромное торможение.
Разнесли данные по отдельным таблицам.


Это - странное решение. Не понятно, зачем разбивать одну таблицу на несколько, никакой прибавки скорости от этого быть не должно. В PG версионность, индексы дают логарифмический поиск, и в общем-то никакой проблемы быть не должно. Ну если конечно у вас не используется сканирование таблиц без индексов напропалую.

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

Так просто может быть вам эту уникальность бизнес-логикой поддерживать ?
Хранить какие-то пулы идентификаторов, и выдавать их пользователям. Ну не знаю, чего тут придумать, я в общем-то ничего так толком и не понял.
...
Рейтинг: 0 / 0
03.10.2008, 13:18
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574696
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
1. хранить все в одной таблице, которую поделить на несколько партиций (суб-таблиц) при помощи механизма наследования постгреса,чтоб не было одной гигантской таблицы.

Чем вам большая таблица-то всем мешает ? А ?


2. при добавлении теск-инфы вычислять ее хеш (sha1 наример) в десятичной форме и по этой функции построить уникальный индекс. Проверка по такому индексу не должна давать задержек. Можно также хранить этот хеш в отдельном поле, возможно будет просще чем связыватся с индексами по функции.

Уникальный индекс по хэшу значения не даст уникальности самого значения.
...
Рейтинг: 0 / 0
03.10.2008, 13:20
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574703
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Konstantin~почему?
BEFORE UPDATE/INSERT вычисляешь хеш , далее операция производистья только с суб-таблицей где хранятся записи с кешами в определенном диапазоне. остальные таблици при этом не трогаются.
.

Зачем вам весь этот бред ? Обычный индекс B+ делает в общем-то то же самое. Зачем вам отдельные таблицы ?
Что от этого лучше-то будет ?
...
Рейтинг: 0 / 0
03.10.2008, 13:21
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35574752
Konstantin~
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konstantin~
Участник
MasterZiv
Чем вам большая таблица-то всем мешает ? А ?


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

MasterZiv
Уникальный индекс по хэшу значения не даст уникальности самого значения.

почему? если хеши одинаковы то и тексты одинаковы. если хеши разные то тексты тоже разные.
(имеется ввиду криптографически-стойкий хеш).


вообще подход одна-большая-таблица не работает если она действительно большая. тормозит.
...
Рейтинг: 0 / 0
03.10.2008, 13:38
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35575150
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
tier.ruПервоначально, все записи всех пользователей хранились в одной огромной таблице. Поддерживать уникальность было просто - всего лишь уникальным ключом по этому текстовому полю. Но, т.к. данные пользователями активно добавляются, чуть менее активно модифицируются (но, не это поле, а другие, второстепенные) и иногда удаляются, то имея одну таблицу имели огромное торможение.не понятно. почему возникали торомоза при конкурентном добавлении данных?

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

tier.ruПри попытке пользователя добавить новую порцию данных в свою таблицу, сначала убираем из нее (порции) те, которые уже есть в большой table1, а затем - те, которые уже есть в его (пользователя) таблице table2. Что осталось - инсертим по очереди в table1 и table2.достаточно отфильтровать порцию по таблице table1. последующая фильтрация по table2 ничего не даст, потому что в table2 нет строк, отсутствующих в table1. получается, что таблица table2 вообще не нужна.

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

при выборке по индексу постгрес читает и индекс, и таблицу. интересно, при вставке с проверкой на уникальность он читает так же, или только индекс? может ускорила бы пока не реализованная фича allow COPY to report error lines and continue . хотя она будет наверное реализована через savepoint-ы, которые тоже требуют времени. :-(

можно разнести table1 по нескольким серверам. стоит ли ради десятиминутного запроса?
...
Рейтинг: 0 / 0
03.10.2008, 15:20
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35575579
MySQLCraft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MySQLCraft
Участник
Попробуйте построить не уникальный индекс по первым 4, 8 буквам. Чем меньше индекс тем проще его кэшировать в памяти.
...
Рейтинг: 0 / 0
03.10.2008, 17:30
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35575584
MySQLCraft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MySQLCraft
Участник
А еще можно создать отдельное поле и преобразовать первые байты в int4 или int8.
...
Рейтинг: 0 / 0
03.10.2008, 17:33
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35576306
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
бекапить например не удобно,

объём данных в N таблицах по x/N записей такой же, что и в одной по x записей. Так какая разница ?

индекс долго создаётся, еще много всяких неудобств.

То же самое замечание.
Длина сканирования аддитивна. Вы ничего не теряете и не приобретаете.
Теряется только удобство работы.

с таблицами можно работать независимо.

Можно. Вопрос - зачем.

почему?

если хеши разные то тексты тоже разные.

Да, это правильно.

если хеши одинаковы то и тексты одинаковы.

Нет, вот это - неправильно. Если хэши одинаковы, то
исходные значения могут быть как одинаковыми, так и разными.

(имеется ввиду криптографически-стойкий хеш).

Любой. Не важно.

вообще подход одна-большая-таблица не работает если она действительно большая. тормозит.

Ничего подобного. Ну, еще раз оговорюсь, что ДО ТЕХ ПОР, ПОКА ВАША БД НЕ ОБРАБАТЫВАЕТ ВСЕ ЗАПРОСЫ СКАНИРОВАНИЕМ ТАБЛИЦ, без использования индексов. Но это - случай клинический, и обычно так не бывает, поскольку в сколько-нибудь большой БД это не работает.
...
Рейтинг: 0 / 0
04.10.2008, 17:27
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35576518
tier.ru
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tier.ru
Участник
<<не понятно. почему возникали торомоза при конкурентном добавлении данных?
я так понимаю, из-за блокировок. кроме того, там не только добавление но и огромное количество select и update идет одновременно.
<<и почему сейчас тормозов нет? если большая таблица (в другом виде, как table1) все равно существует, и в нее добаляются <<данные, и по ней происходит проверка на уникальность. в чем отличие?
в том, что большая таблица задействована только при инсерте новых данных. а другой работы куча делается.
<<достаточно отфильтровать порцию по таблице table1. последующая фильтрация по table2 ничего не даст, потому что в table2 нет <<строк, отсутствующих в table1. получается, что таблица table2 вообще не нужна.
да, из моего объяснения можно сделать такой вывод. но, на самом деле, большую я иногда чищу от устаревших данных. а в многочисленных маленьких пользовательских таблицах они могут остаться. и, т.к. данные устаревшие, то вероятность весьма мала, что их кто-то может заинсертить повторно. но, она есть, поэтому и с table2 тоже надо делать проверку на уникальность.

ну так какое общее мнение, partitioning мне поможет?
...
Рейтинг: 0 / 0
04.10.2008, 23:44
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35577069
DAISER
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DAISER
Гость
tier.ru<<не понятно. почему возникали торомоза при конкурентном добавлении данных?
я так понимаю, из-за блокировок. кроме того, там не только добавление но и огромное количество select и update идет одновременно.
Прошу прощения, если чушь скажу, но при чем тут это? Какой у вас "default_transaction_isolation" установлен или какой isolation level в транзации, где select выполняется? update'ы должны в WAL записываться, может с его параметрами поиграться? остаются select. если select частые, то таблица table1 или её часть должны быть в кэше.
...
Рейтинг: 0 / 0
05.10.2008, 23:34
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35577070
DAISER
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DAISER
Гость
может table1.id и table1.field1 вынести в отдельную таблицу? типа, table4(id int, field1 text), которая связана с table1 по id.
...
Рейтинг: 0 / 0
05.10.2008, 23:37
| Ответить | Цитировать | Написать  
помогите оптимизировать запрос
    #35577073
DAISER
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DAISER
Гость
И ещё, в порядке бреда, если поле table1.field1 большого размера, то можно где-то хранить архивированые zip/gzip значения этого поля. Уникальность сохранится, а размер данных уменьшится.
...
Рейтинг: 0 / 0
05.10.2008, 23:42
| Ответить | Цитировать | Написать  
54 сообщений из 54, показаны все 3 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите оптимизировать запрос
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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