ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Has join
21 сообщений из 21, страница 1 из 1
Has join
    #39164331
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
имеем:
Код: sql
1.
2.
3.
4.
5.
postgres=# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)



Пытаюсь выполнить:
Код: sql
1.
2.
postgres=# set max_parallel_degree=0;
ERROR:  unrecognized configuration parameter "max_parallel_degree"



Где я туплю?
...
Рейтинг: 0 / 0
05.02.2016, 17:45
| Ответить | Цитировать | Написать  
Has join
    #39164345
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Andrey Sribnyak,

Это только в 9.6 будет же.
...
Рейтинг: 0 / 0
05.02.2016, 18:02
| Ответить | Цитировать | Написать  
Has join
    #39166310
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
Ок, поставил 9.6

авторtpcc=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.6devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit
(1 row)


подскажите, как мне добится заветного Partial Seq Scan ?

делаю:

авторtpcc=# set max_parallel_degree = 1;
SET
Time: 0.395 ms
tpcc=#
tpcc=#
tpcc=# explain SELECT ol_number
,sum(ol_quantity) AS sum_qty
,sum(ol_amount) AS sum_amount
,avg(ol_quantity) AS avg_qty
,avg(ol_amount) AS avg_amount
,count(*) AS count_order
FROM dbo.order_line
WHERE ol_delivery_d > '20151006'
GROUP BY ol_number
ORDER BY ol_number;
QUERY PLAN
--------------------------------------------------------------------------------------------
Sort (cost=5443989.89..5443989.93 rows=15 width=10)
Sort Key: ol_number
-> HashAggregate (cost=5443989.33..5443989.60 rows=15 width=10)
Group Key: ol_number
-> Seq Scan on order_line (cost=0.00..3284336.60 rows=143976849 width=10)
Filter: (ol_delivery_d > '2015-10-06 00:00:00'::timestamp without time zone)
(6 rows)

Time: 1.026 ms



авторtpcc=# SELECT ol_number
,sum(ol_quantity) AS sum_qty
,sum(ol_amount) AS sum_amount
,avg(ol_quantity) AS avg_qty
,avg(ol_amount) AS avg_amount
,count(*) AS count_order
FROM dbo.order_line
WHERE ol_delivery_d > '20151006'
GROUP BY ol_number
ORDER BY ol_number;

...

Time: 62600.480 ms



И то же при возможно параллельном выолнении:


авторtpcc=# set max_parallel_degree = 32;
SET
Time: 0.389 ms
tpcc=#
tpcc=#
tpcc=# explain SELECT ol_number
,sum(ol_quantity) AS sum_qty
,sum(ol_amount) AS sum_amount
,avg(ol_quantity) AS avg_qty
,avg(ol_amount) AS avg_amount
,count(*) AS count_order
FROM dbo.order_line
WHERE ol_delivery_d > '20151006'
GROUP BY ol_number
ORDER BY ol_number;
QUERY PLAN
--------------------------------------------------------------------------------------------
Sort (cost=5443989.89..5443989.93 rows=15 width=10)
Sort Key: ol_number
-> HashAggregate (cost=5443989.33..5443989.60 rows=15 width=10)
Group Key: ol_number
-> Seq Scan on order_line (cost=0.00..3284336.60 rows=143976849 width=10)
Filter: (ol_delivery_d > '2015-10-06 00:00:00'::timestamp without time zone)
(6 rows)



автор
tpcc=# SELECT ol_number
,sum(ol_quantity) AS sum_qty
,sum(ol_amount) AS sum_amount
,avg(ol_quantity) AS avg_qty
,avg(ol_amount) AS avg_amount
,count(*) AS count_order
FROM dbo.order_line
WHERE ol_delivery_d > '20151006'
GROUP BY ol_number
ORDER BY ol_number;
ol_number | sum_qty | sum_amount | avg_qty | avg_amount | count_order
-----------+----------+------------------+--------------------+-----------------------+-------------
1 | 72000000 | 72001066703.2736 | 5.0000000000000000 | 5000.0740766162222222 | 14400000
2 | 72000000 | 71979552805.7618 | 5.0000000000000000 | 4998.5800559556805556 | 14400000
3 | 72000000 | 72016792814.8090 | 5.0000000000000000 | 5001.1661676950694444 | 14400000
4 | 72000000 | 72026174580.4301 | 5.0000000000000000 | 5001.8176791965347222 | 14400000
5 | 72000000 | 71995979084.4438 | 5.0000000000000000 | 4999.7207697530416667 | 14400000
6 | 65453315 | 65445858569.8675 | 5.0000000000000000 | 4999.4304008794283376 | 13090663
7 | 58910155 | 58882197836.0727 | 5.0000000000000000 | 4997.6271354295961367 | 11782031
8 | 52362530 | 52367429545.1200 | 5.0000000000000000 | 5000.4678483946440327 | 10472506
9 | 45822420 | 45821006829.0195 | 5.0000000000000000 | 4999.8457991764184432 | 9164484
10 | 39274160 | 39261904262.5124 | 5.0000000000000000 | 4998.4397199726741450 | 7854832
11 | 32723110 | 32713101576.5575 | 5.0000000000000000 | 4998.4707407941207300 | 6544622
12 | 26165965 | 26175605528.6040 | 5.0000000000000000 | 5001.8421886225101960 | 5233193
13 | 19619965 | 19618861642.7164 | 5.0000000000000000 | 4999.7188177237828916 | 3923993
14 | 13080865 | 13086910283.7150 | 5.0000000000000000 | 5002.3107354578615405 | 2616173
15 | 6543780 | 6544684119.6085 | 5.0000000000000000 | 5000.6908236588791188 | 1308756
(15 rows)

Time: 62657.160 ms




ПРи этом точно процессоры во время выполнения не задействованы:

автор# mpstat -P ALL
Linux 3.19.0-49-generic (DBBestUBP) 02/09/2016 _x86_64_ (32 CPU)

07:51:29 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
07:51:29 AM all 0.49 0.00 0.09 0.97 0.00 0.00 0.00 0.00 0.00 98.44
07:51:29 AM 0 0.01 0.00 0.21 0.04 0.00 0.01 0.00 0.00 0.00 99.72
07:51:29 AM 1 0.03 0.00 0.07 0.16 0.00 0.00 0.00 0.00 0.00 99.75
07:51:29 AM 2 0.00 0.00 0.05 0.07 0.00 0.00 0.00 0.00 0.00 99.87
07:51:29 AM 3 0.01 0.00 0.07 0.11 0.00 0.00 0.00 0.00 0.00 99.81
07:51:29 AM 4 0.01 0.00 0.05 0.22 0.00 0.00 0.00 0.00 0.00 99.72
07:51:29 AM 5 0.05 0.00 0.15 0.51 0.00 0.00 0.00 0.00 0.00 99.29
07:51:29 AM 6 0.02 0.00 0.02 0.07 0.00 0.00 0.00 0.00 0.00 99.88
07:51:29 AM 7 0.00 0.00 0.01 0.12 0.00 0.00 0.00 0.00 0.00 99.86
07:51:29 AM 8 0.01 0.00 0.07 0.21 0.00 0.00 0.00 0.00 0.00 99.72
07:51:29 AM 9 0.00 0.00 0.01 0.21 0.00 0.00 0.00 0.00 0.00 99.78
07:51:29 AM 10 0.01 0.00 0.01 0.11 0.00 0.00 0.00 0.00 0.00 99.87
07:51:29 AM 11 0.00 0.00 0.02 0.04 0.00 0.00 0.00 0.00 0.00 99.93
07:51:29 AM 12 0.00 0.00 0.02 0.07 0.00 0.00 0.00 0.00 0.00 99.90
07:51:29 AM 13 0.01 0.00 0.11 0.07 0.00 0.00 0.00 0.00 0.00 99.81
07:51:29 AM 14 0.01 0.00 0.02 0.04 0.00 0.00 0.00 0.00 0.00 99.92
07:51:29 AM 15 0.02 0.00 0.06 0.12 0.00 0.00 0.00 0.00 0.00 99.80
07:51:29 AM 16 0.00 0.00 0.06 0.12 0.00 0.00 0.00 0.00 0.00 99.81
07:51:29 AM 17 0.01 0.00 0.07 0.23 0.00 0.00 0.00 0.00 0.00 99.68
07:51:29 AM 18 0.01 0.00 0.04 0.30 0.00 0.00 0.00 0.00 0.00 99.65
07:51:29 AM 19 0.01 0.00 0.54 0.07 0.00 0.00 0.00 0.00 0.00 99.38
07:51:29 AM 20 0.01 0.00 0.08 8.63 0.00 0.00 0.00 0.00 0.00 91.27
07:51:29 AM 21 0.03 0.00 0.07 5.34 0.00 0.00 0.00 0.00 0.00 94.56
07:51:29 AM 22 0.01 0.00 0.09 0.41 0.00 0.00 0.00 0.00 0.00 99.49
07:51:29 AM 23 0.01 0.00 0.05 0.19 0.00 0.00 0.00 0.00 0.00 99.75
07:51:29 AM 24 15.27 0.00 0.62 12.46 0.00 0.02 0.00 0.00 0.00 71.62
07:51:29 AM 25 0.02 0.00 0.12 0.29 0.00 0.00 0.00 0.00 0.00 99.57
07:51:29 AM 26 0.03 0.00 0.03 0.14 0.00 0.00 0.00 0.00 0.00 99.79
07:51:29 AM 27 0.00 0.00 0.01 0.06 0.00 0.00 0.00 0.00 0.00 99.93
07:51:29 AM 28 0.01 0.00 0.01 0.18 0.00 0.00 0.00 0.00 0.00 99.79
07:51:29 AM 29 0.00 0.00 0.08 0.13 0.00 0.00 0.00 0.00 0.00 99.78
07:51:29 AM 30 0.01 0.00 0.01 0.31 0.00 0.00 0.00 0.00 0.00 99.67
07:51:29 AM 31 0.08 0.00 0.03 0.04 0.00 0.00 0.00 0.00 0.00 99.84
...
Рейтинг: 0 / 0
09.02.2016, 10:57
| Ответить | Цитировать | Написать  
Has join
    #39166389
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Andrey Sribnyak,

Надеюсь вы понимаете, что 9.6 ещё далёк от стабилизации и даже от завершения. Будет ещё 1 commitfest в марте, после которого заморозка кода. Бета может появиться только в июне. Оно сырое.

И да, параллельные агрегаты ещё не были закомичены — в работе, один из последних патчей для 9.6.
...
Рейтинг: 0 / 0
09.02.2016, 11:42
| Ответить | Цитировать | Написать  
Has join
    #39166428
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
vyegorov,

Это лишь тестовая среда для оценки производительности.

Стандартный тест tpcc

На очень больших данных. ПРи этом сравнение на одном и том же железе и одних и тех же данных но в разных серверах баз данных.
Была очень большая надежда на этот функционал. Т.к иначе результаты у Postgresql не очень хорошие. Я бы сказал плохие.

например запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT  su_name,
        su_address
FROM    dbo.supplier,
        dbo.nation
WHERE   su_suppkey IN
            (SELECT ((s_i_id * s_w_id) % 10000)
            FROM    dbo.stock
                    INNER JOIN dbo.item ON i_id = s_i_id
                    INNER JOIN dbo.order_line ON ol_i_id = s_i_id
            WHERE   ol_delivery_d > '20151008'
                    AND  i_data LIKE '%%4%%'
            GROUP   BY s_i_id,
                    s_w_id,
                    s_quantity 
            HAVING  650*s_quantity > sum(ol_quantity))
        AND  su_nationkey = n_nationkey
        AND n_name LIKE '%Germany%'
ORDER   BY su_name;


Не выполняется в принципе за разумное время (отбил через несколько часов.)

план при этом такой:

автор QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Sort (cost=690614487.95..690614488.15 rows=80 width=57)
Sort Key: supplier.su_name
-> Hash Join (cost=690614482.64..690614485.42 rows=80 width=57)
Hash Cond: ((((stock.s_i_id * stock.s_w_id) % 10000)) = supplier.su_suppkey)
-> HashAggregate (cost=690614060.73..690614062.73 rows=200 width=4)
Group Key: ((stock.s_i_id * stock.s_w_id) % 10000)
-> HashAggregate (cost=690458060.73..690554060.73 rows=4800000 width=12)
Group Key: stock.s_i_id, stock.s_w_id, stock.s_quantity
Filter: ((650 * stock.s_quantity) > sum(order_line.ol_quantity))
-> Hash Join (cost=3312648.69..391900548.77 rows=29855751196 width=12)
Hash Cond: (order_line.ol_i_id = item.i_id)
-> Seq Scan on order_line (cost=0.00..3284336.60 rows=143976849 width=6)
Filter: (ol_delivery_d > '2015-10-08 00:00:00'::timestamp without time zone)
-> Hash (cost=3052926.69..3052926.69 rows=20777760 width=14)
-> Hash Join (cost=3330.09..3052926.69 rows=20777760 width=14)
Hash Cond: (stock.s_i_id = item.i_id)
-> Seq Scan on stock (cost=0.00..2661819.00 rows=48000000 width=10)
-> Hash (cost=2789.00..2789.00 rows=43287 width=4)
-> Seq Scan on item (cost=0.00..2789.00 rows=43287 width=4)
Filter: (i_data ~~ '%%4%%'::text)
-> Hash (cost=419.90..419.90 rows=161 width=61)
-> Hash Join (cost=2.79..419.90 rows=161 width=61)
Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
-> Seq Scan on supplier (cost=0.00..378.00 rows=10000 width=65)
-> Hash (cost=2.77..2.77 rows=1 width=4)
-> Seq Scan on nation (cost=0.00..2.77 rows=1 width=4)
Filter: (n_name ~~ '%Germany%'::text)


Я конечно понимаю, что я не умею готовить Postgresql.
Но, сильно не понимаю, что я делаю не так...
...
Рейтинг: 0 / 0
09.02.2016, 12:01
| Ответить | Цитировать | Написать  
Has join
    #39166435
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
при этом в на других серверах, к примеру, HANA, MS SQL, DB2 этот же запрос выполняется пару секунд, на этих же данных и на этом железе
...
Рейтинг: 0 / 0
09.02.2016, 12:07
| Ответить | Цитировать | Написать  
Has join
    #39166529
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Andrey Sribnyak,

1. Используйте `EXPLAIN (analyze, buffers)`
2. Оберните вывод тагом `[src]` — читать неудобно.
...
Рейтинг: 0 / 0
09.02.2016, 13:09
| Ответить | Цитировать | Написать  
Has join
    #39166572
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Andrey Sribnyakvyegorov,

Это лишь тестовая среда для оценки производительности.

Стандартный тест tpcc

На очень больших данных. ПРи этом сравнение на одном и том же железе и одних и тех же данных но в разных серверах баз данных.
Была очень большая надежда на этот функционал. Т.к иначе результаты у Postgresql не очень хорошие. Я бы сказал плохие.
...
Я конечно понимаю, что я не умею готовить Postgresql.
Но, сильно не понимаю, что я делаю не так...

Я бы предположил несколько вариантов:
1)work_men не под tpcc (там гигабай work_mem под такие запросы самое то)
2)мало shared_buffers
3)низкий default_statistics_target и в итоге план неудачный
4)непрогретый кеш базы перед началом теста а база не на ssd а на механике
5)какие то индексы есть на других базах и почему то нет в tpcc реализации на postgresql (как минимум сравнить наличие индекса по ol_delivery_d)
6)не сделан analyze после загрузки данных в базу

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
09.02.2016, 13:31
| Ответить | Цитировать | Написать  
Has join
    #39166583
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
Andrey Sribnyak,

а сделать индекс на dbo.stock типа
Код: sql
1.
2.
CREATE INDEX idx_stock_partiton ON dbo.stock
  USING btree (((s_i_id * s_w_id) % 10000));

и попробовать можно?
...
Рейтинг: 0 / 0
09.02.2016, 13:39
| Ответить | Цитировать | Написать  
Has join
    #39166621
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
LonepsychoAndrey Sribnyak,

а сделать индекс на dbo.stock типа
Код: sql
1.
2.
CREATE INDEX idx_stock_partiton ON dbo.stock
  USING btree (((s_i_id * s_w_id) % 10000));

и попробовать можно?

индексм создал:

но в плане я не вижу, что он должен участвовать... попробую запустить сейчас

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
tpcc=# CREATE INDEX idx_stock_partiton ON dbo.stock
tpcc-#   USING btree (((s_i_id * s_w_id) % 10000));
CREATE INDEX
Time: 116053.115 ms
tpcc=# EXPLAIN SELECT  su_name,
tpcc-#         su_address
tpcc-# FROM    dbo.supplier,
tpcc-#         dbo.nation
tpcc-# WHERE   su_suppkey IN
tpcc-#             (SELECT ((s_i_id * s_w_id) % 10000)
tpcc(#             FROM    dbo.stock
tpcc(#                     INNER JOIN dbo.item ON i_id = s_i_id
tpcc(#                     INNER JOIN dbo.order_line ON ol_i_id = s_i_id
tpcc(#             WHERE   ol_delivery_d > '20151213'
tpcc(#                     AND  i_data LIKE '%%4%%'
tpcc(#             GROUP   BY s_i_id,
tpcc(#                     s_w_id,
tpcc(#                     s_quantity
tpcc(#             HAVING  650*s_quantity > sum(ol_quantity))
tpcc-#         AND  su_nationkey = n_nationkey
tpcc-#         AND UPPER(n_name) LIKE '%GERMANY%'
tpcc-# ORDER   BY su_name;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=351169774.40..351169774.60 rows=80 width=57)
   Sort Key: supplier.su_name
   ->  Hash Join  (cost=351169769.09..351169771.87 rows=80 width=57)
         Hash Cond: ((((stock.s_i_id * stock.s_w_id) % 10000)) = supplier.su_suppkey)
         ->  HashAggregate  (cost=351169346.87..351169348.87 rows=200 width=4)
               Group Key: ((stock.s_i_id * stock.s_w_id) % 10000)
               ->  HashAggregate  (cost=351013346.87..351109346.87 rows=4800000 width=12)
                     Group Key: stock.s_i_id, stock.s_w_id, stock.s_quantity
                     Filter: ((650 * stock.s_quantity) > sum(order_line.ol_quantity))
                     ->  Hash Join  (cost=3312648.69..200649339.20 rows=15036400767 width=12)
                           Hash Cond: (order_line.ol_i_id = item.i_id)
                           ->  Seq Scan on order_line  (cost=0.00..3284336.60 rows=72511778 width=6)
                                 Filter: (ol_delivery_d > '2015-12-13 00:00:00'::timestamp without time zone)
                           ->  Hash  (cost=3052926.69..3052926.69 rows=20777760 width=14)
                                 ->  Hash Join  (cost=3330.09..3052926.69 rows=20777760 width=14)
                                       Hash Cond: (stock.s_i_id = item.i_id)
                                       ->  Seq Scan on stock  (cost=0.00..2661819.00 rows=48000000 width=10)
                                       ->  Hash  (cost=2789.00..2789.00 rows=43287 width=4)
                                             ->  Seq Scan on item  (cost=0.00..2789.00 rows=43287 width=4)
                                                   Filter: (i_data ~~ '%%4%%'::text)
         ->  Hash  (cost=420.21..420.21 rows=161 width=61)
               ->  Hash Join  (cost=3.10..420.21 rows=161 width=61)
                     Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
                     ->  Seq Scan on supplier  (cost=0.00..378.00 rows=10000 width=65)
                     ->  Hash  (cost=3.08..3.08 rows=1 width=4)
                           ->  Seq Scan on nation  (cost=0.00..3.08 rows=1 width=4)
                                 Filter: (upper((n_name)::text) ~~ '%GERMANY%'::text)
...
Рейтинг: 0 / 0
09.02.2016, 14:05
| Ответить | Цитировать | Написать  
Has join
    #39166643
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
Maxim Boguk
Я бы предположил несколько вариантов:
1)work_men не под tpcc (там гигабай work_mem под такие запросы самое то)


Код: sql
1.
2.
3.
4.
cat postgresql.conf | grep work_mem
work_mem = 14GB                         # min 64kB
maintenance_work_mem = 2GB              # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem


2)мало shared_buffers

Код: sql
1.
2.
3.
 cat postgresql.conf | grep shared_buffers
shared_buffers = 228GB                  # min 128kB
#wal_buffers = -1                       # min 32kB, -1 sets based on shared_buffers



3)низкий default_statistics_target и в итоге план неудачный


Код: sql
1.
2.
cat postgresql.conf | grep default_statistics_target
default_statistics_target = 500 # range 1-10000


4)непрогретый кеш базы перед началом теста а база не на ssd а на механике


Сам Postgress и linux на ssd

База на 10 рейде из 4 дисков
Журнал транзакций на зеркале из 2 дисков

5)какие то индексы есть на других базах и почему то нет в tpcc реализации на postgresql (как минимум сравнить наличие индекса по ol_delivery_d)

Ну например SQL server, тот же запрос... и план выполнения... кроме кластерных индексов - нет никаких

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
SET SHOWPLAN_ALL ON
go
SET statistics time  ON
go
SELECT  su_name,
        su_address
FROM    supplier,
        nation
WHERE   su_suppkey IN
            (SELECT ((s_i_id * s_w_id) % 10000)
            FROM    stock
                    INNER JOIN item ON i_id = s_i_id
                    INNER JOIN order_line ON ol_i_id = s_i_id
            WHERE   ol_delivery_d > '2015-12-13'
                    AND  i_data LIKE '%%4%%'
            GROUP   BY s_i_id,
                    s_w_id,
                    s_quantity 
            HAVING  200*s_quantity > sum(ol_quantity))
        AND  su_nationkey = n_nationkey
        AND UPPER(n_name) LIKE '%GERMANY%'
ORDER   BY su_name
option (recompile)
;



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
(1 row(s) affected)
StmtText               StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument DefinedValues EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList Warnings Type                                                             Parallel EstimateExecutions
---------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------
SET SHOWPLAN_ALL ON    1           1           0           NULL                           NULL                           1        NULL          NULL          NULL          NULL          NULL        NULL             NULL       NULL     SET ON/OFF                                                       0        NULL

(1 row(s) affected)

StmtText                   StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument DefinedValues EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList Warnings Type                                                             Parallel EstimateExecutions
-------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------
SET statistics time  ON    1           1           0           NULL                           NULL                           1        NULL          NULL          NULL          NULL          NULL        NULL             NULL       NULL     SET STATS                                                        0        NULL

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                               DefinedValues                                                                                                                                                                                                                                               EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                                                                                                                  Warnings Type                                                             Parallel EstimateExecutions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT  su_name,
        su_address
FROM    supplier,
        nation
WHERE   su_suppkey IN
            (SELECT ((s_i_id * s_w_id) % 10000)
            FROM    stock
                    INNER JOIN item ON i_id = s_i_id
                    INNER JOIN 1           1           0           NULL                           NULL                           1                                                                                                                                                                                                      NULL                                                                                                                                                                                                                                                        899.91        NULL          NULL          NULL        988.4825         NULL                                                                                                                                                                                                                                                        NULL     SELECT                                                           0        NULL
  |--Parallelism(Gather Streams, ORDER BY:([tpcch].[dbo].[supplier].[su_name] ASC))                                                                                                                                                                              1           2           1           Parallelism                    Gather Streams                 ORDER BY:([tpcch].[dbo].[supplier].[su_name] ASC)                                                                                                                                                      NULL                                                                                                                                                                                                                                                        899.91        0             0.03105695    56          988.4825         [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address]                                                                                                                                                                                   NULL     PLAN_ROW                                                         1        1
       |--Sort(ORDER BY:([tpcch].[dbo].[supplier].[su_name] ASC))                                                                                                                                                                                                1           3           2           Sort                           Sort                           ORDER BY:([tpcch].[dbo].[supplier].[su_name] ASC)                                                                                                                                                      NULL                                                                                                                                                                                                                                                        899.91        0.0002346096  9.235956E-05  56          988.4515         [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address]                                                                                                                                                                                   NULL     PLAN_ROW                                                         1        1
            |--Hash Match(Left Semi Join, HASH:([tpcch].[dbo].[supplier].[su_suppkey])=([Expr1012]), RESIDUAL:([tpcch].[dbo].[supplier].[su_suppkey]=[Expr1012])DEFINE:([Opt_Bitmap1153]))                                                                       1           4           3           Hash Match                     Left Semi Join                 HASH:([tpcch].[dbo].[supplier].[su_suppkey])=([Expr1012]), RESIDUAL:([tpcch].[dbo].[supplier].[su_suppkey]=[Expr1012])                                                                                 [Opt_Bitmap1153]                                                                                                                                                                                                                                            899.91        0             0.04320068    56          988.4512         [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address]                                                                                                                                                                                   NULL     PLAN_ROW                                                         1        1
                 |--Hash Match(Inner Join, HASH:([tpcch].[dbo].[nation].[n_nationkey])=([tpcch].[dbo].[supplier].[su_nationkey])DEFINE:([Opt_Bitmap1122]))                                                                                                       1           5           4           Hash Match                     Inner Join                     HASH:([tpcch].[dbo].[nation].[n_nationkey])=([tpcch].[dbo].[supplier].[su_nationkey])                                                                                                                  [Opt_Bitmap1122]                                                                                                                                                                                                                                            900.0001      0             0.001872874   60          0.2334089        [tpcch].[dbo].[supplier].[su_suppkey], [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address]                                                                                                                                            NULL     PLAN_ROW                                                         1        1
                 |    |--Filter(WHERE:([Expr1014] like '%GERMANY%'))                                                                                                                                                                                             1           6           5           Filter                         Filter                         WHERE:([Expr1014] like '%GERMANY%')                                                                                                                                                                    NULL                                                                                                                                                                                                                                                        5.58          0             3.41E-07      11          0.03284592       [tpcch].[dbo].[nation].[n_nationkey]                                                                                                                                                                                                                        NULL     PLAN_ROW                                                         1        1
                 |    |    |--Compute Scalar(DEFINE:([Expr1014]=upper([tpcch].[dbo].[nation].[n_name])))                                                                                                                                                         1           7           6           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1014]=upper([tpcch].[dbo].[nation].[n_name]))                                                                                                                                             [Expr1014]=upper([tpcch].[dbo].[nation].[n_name])                                                                                                                                                                                                           62            0             3.875E-08     27          0.03284558       [tpcch].[dbo].[nation].[n_nationkey], [Expr1014]                                                                                                                                                                                                            NULL     PLAN_ROW                                                         1        1
                 |    |         |--Parallelism(Distribute Streams, RoundRobin Partitioning)                                                                                                                                                                      1           8           7           Parallelism                    Distribute Streams             NULL                                                                                                                                                                                                   NULL                                                                                                                                                                                                                                                        62            0             0.02875305    36          0.03284399       [tpcch].[dbo].[nation].[n_nationkey], [tpcch].[dbo].[nation].[n_name]                                                                                                                                                                                       NULL     PLAN_ROW                                                         1        1
                 |    |              |--Clustered Index Scan(OBJECT:([tpcch].[dbo].[nation].[nation_c1]))                                                                                                                                                        1           9           8           Clustered Index Scan           Clustered Index Scan           OBJECT:([tpcch].[dbo].[nation].[nation_c1])                                                                                                                                                            [tpcch].[dbo].[nation].[n_nationkey], [tpcch].[dbo].[nation].[n_name]                                                                                                                                                                                       62            0.003865741   0.0002252     36          0.004090941      [tpcch].[dbo].[nation].[n_nationkey], [tpcch].[dbo].[nation].[n_name]                                                                                                                                                                                       NULL     PLAN_ROW                                                         0        1
                 |    |--Filter(WHERE:(PROBE([Opt_Bitmap1122],[tpcch].[dbo].[supplier].[su_nationkey])))                                                                                                                                                         1           14          5           Filter                         Filter                         WHERE:(PROBE([Opt_Bitmap1122],[tpcch].[dbo].[supplier].[su_nationkey]))                                                                                                                                NULL                                                                                                                                                                                                                                                        1000          0.1979398     0.0006973125  64          0.1986371        [tpcch].[dbo].[supplier].[su_suppkey], [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address], [tpcch].[dbo].[supplier].[su_nationkey]                                                                                                   NULL     PLAN_ROW                                                         1        1
                 |         |--Clustered Index Scan(OBJECT:([tpcch].[dbo].[supplier].[supplier_c1]))                                                                                                                                                              1           15          14          Clustered Index Scan           Clustered Index Scan           OBJECT:([tpcch].[dbo].[supplier].[supplier_c1])                                                                                                                                                        [tpcch].[dbo].[supplier].[su_suppkey], [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address], [tpcch].[dbo].[supplier].[su_nationkey]                                                                                                   1000          0.1979398     0.0006973125  64          0.1986371        [tpcch].[dbo].[supplier].[su_suppkey], [tpcch].[dbo].[supplier].[su_name], [tpcch].[dbo].[supplier].[su_address], [tpcch].[dbo].[supplier].[su_nationkey]                                                                                                   NULL     PLAN_ROW                                                         1        1
                 |--Filter(WHERE:([Expr1013]>[Expr1010] AND PROBE([Opt_Bitmap1153],[Expr1012])))                                                                                                                                                                 1           18          4           Filter                         Filter                         WHERE:([Expr1013]>[Expr1010] AND PROBE([Opt_Bitmap1153],[Expr1012]))                                                                                                                                   NULL                                                                                                                                                                                                                                                        1440000       0             0.0144        11          988.1746         [Expr1012]                                                                                                                                                                                                                                                  NULL     PLAN_ROW                                                         1        1
                      |--Compute Scalar(DEFINE:([Expr1012]=[tpcch].[dbo].[stock].[s_i_id]*[tpcch].[dbo].[stock].[s_w_id]%(10000), [Expr1013]=(200)*CONVERT_IMPLICIT(int,[tpcch].[dbo].[stock].[s_quantity],0)))                                                  1           19          18          Compute Scalar                 Compute Scalar                 DEFINE:([Expr1012]=[tpcch].[dbo].[stock].[s_i_id]*[tpcch].[dbo].[stock].[s_w_id]%(10000), [Expr1013]=(200)*CONVERT_IMPLICIT(int,[tpcch].[dbo].[stock].[s_quantity],0))                                 [Expr1012]=[tpcch].[dbo].[stock].[s_i_id]*[tpcch].[dbo].[stock].[s_w_id]%(10000), [Expr1013]=(200)*CONVERT_IMPLICIT(int,[tpcch].[dbo].[stock].[s_quantity],0)                                                                                               4800000       0             0.003         19          988.1602         [Expr1010], [Expr1012], [Expr1013]                                                                                                                                                                                                                          NULL     PLAN_ROW                                                         1        1
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([tpcch].[dbo].[order_line].[ol_i_id], [Expr1166]) OPTIMIZED WITH UNORDERED PREFETCH)                                                                                                    1           21          19          Nested Loops                   Inner Join                     OUTER REFERENCES:([tpcch].[dbo].[order_line].[ol_i_id], [Expr1166]) OPTIMIZED WITH UNORDERED PREFETCH                                                                                                  NULL                                                                                                                                                                                                                                                        2527894       0             6.690451      21          988.0308         [tpcch].[dbo].[stock].[s_i_id], [tpcch].[dbo].[stock].[s_w_id], [tpcch].[dbo].[stock].[s_quantity], [Expr1010]                                                                                                                                              NULL     PLAN_ROW                                                         1        1
                                |--Hash Match(Inner Join, HASH:([tpcch].[dbo].[item].[i_id])=([tpcch].[dbo].[order_line].[ol_i_id])DEFINE:([Opt_Bitmap1097]))                                                                                                    1           24          21          Hash Match                     Inner Join                     HASH:([tpcch].[dbo].[item].[i_id])=([tpcch].[dbo].[order_line].[ol_i_id])                                                                                                                              [Opt_Bitmap1097]                                                                                                                                                                                                                                            52664.45      0             0.01187771    15          51.44816         [tpcch].[dbo].[order_line].[ol_i_id], [Expr1010]                                                                                                                                                                                                            NULL     PLAN_ROW                                                         1        1
                                |    |--Clustered Index Scan(OBJECT:([tpcch].[dbo].[item].[item_c1]), WHERE:([tpcch].[dbo].[item].[i_data] like '%%4%%'))                                                                                                        1           25          24          Clustered Index Scan           Clustered Index Scan           OBJECT:([tpcch].[dbo].[item].[item_c1]), WHERE:([tpcch].[dbo].[item].[i_data] like '%%4%%')                                                                                                            [tpcch].[dbo].[item].[i_id]                                                                                                                                                                                                                                 52664.45      0.8742361     0.006884812   61          0.8811209        [tpcch].[dbo].[item].[i_id]                                                                                                                                                                                                                                 NULL     PLAN_ROW                                                         1        1
                                |    |--Hash Match(Aggregate, HASH:([tpcch].[dbo].[order_line].[ol_i_id]) DEFINE:([Expr1010]=SUM([tpcch].[dbo].[order_line].[ol_quantity])))                                                                                     1           26          24          Hash Match                     Aggregate                      HASH:([tpcch].[dbo].[order_line].[ol_i_id])                                                                                                                                                            [Expr1010]=SUM([tpcch].[dbo].[order_line].[ol_quantity])                                                                                                                                                                                                    100000        0             2.825588      15          50.55211         [tpcch].[dbo].[order_line].[ol_i_id], [Expr1010]                                                                                                                                                                                                            NULL     PLAN_ROW                                                         1        1
                                |         |--Index Scan(OBJECT:([tpcch].[dbo].[order_line].[ncci_order_line]),  WHERE:([tpcch].[dbo].[order_line].[ol_delivery_d]>'2015-12-13 00:00:00.000' AND PROBE([Opt_Bitmap1097],[tpcch].[dbo].[order_line].[ol_i_id])))   1           30          26          Index Scan                     Index Scan                     OBJECT:([tpcch].[dbo].[order_line].[ncci_order_line]),  WHERE:([tpcch].[dbo].[order_line].[ol_delivery_d]>'2015-12-13 00:00:00.000' AND PROBE([Opt_Bitmap1097],[tpcch].[dbo].[order_line].[ol_i_id]))  [tpcch].[dbo].[order_line].[ol_o_id], [tpcch].[dbo].[order_line].[ol_d_id], [tpcch].[dbo].[order_line].[ol_w_id], [tpcch].[dbo].[order_line].[ol_number], [tpcch].[dbo].[order_line].[ol_i_id], [tpcch].[dbo].[order_line].[ol_quantity], [Generation1163]  3.834564E+07  160.6646      9.899408      39          170.564          [tpcch].[dbo].[order_line].[ol_o_id], [tpcch].[dbo].[order_line].[ol_d_id], [tpcch].[dbo].[order_line].[ol_w_id], [tpcch].[dbo].[order_line].[ol_number], [tpcch].[dbo].[order_line].[ol_i_id], [tpcch].[dbo].[order_line].[ol_quantity], [Generation1163]  NULL     PLAN_ROW                                                         1        1
                                |--Clustered Index Seek(OBJECT:([tpcch].[dbo].[stock].[stock_c1]), SEEK:([tpcch].[dbo].[stock].[s_i_id]=[tpcch].[dbo].[order_line].[ol_i_id]) ORDERED FORWARD)                                                                   1           37          21          Clustered Index Seek           Clustered Index Seek           OBJECT:([tpcch].[dbo].[stock].[stock_c1]), SEEK:([tpcch].[dbo].[stock].[s_i_id]=[tpcch].[dbo].[order_line].[ol_i_id]) ORDERED FORWARD                                                                  [tpcch].[dbo].[stock].[s_i_id], [tpcch].[dbo].[stock].[s_w_id], [tpcch].[dbo].[stock].[s_quantity]                                                                                                                                                          486.2745      0.01719907    0.000691902   17          929.8922         [tpcch].[dbo].[stock].[s_i_id], [tpcch].[dbo].[stock].[s_w_id], [tpcch].[dbo].[stock].[s_quantity]                                                                                                                                                          NULL     PLAN_ROW                                                         1        52664.45

(19 row(s) affected)


(1 row(s) affected)



Выполняется - за секунду

6)не сделан analyze после загрузки данных в базу

НУ число записей в планах актуальное


--
Maxim Boguk
www.postgresql-consulting.ru [/quot]
...
Рейтинг: 0 / 0
09.02.2016, 14:18
| Ответить | Цитировать | Написать  
Has join
    #39166647
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Andrey Sribnyak
Код: sql
1.
tpcc=# EXPLAIN SELECT  su_name, ...


1. Пожалуйста `EXPLAIN (analyze, buffers) SELECT ...`.

2. Параллельной аггрегации пока нет, так что приблизится к MS SQL не выйдет.
...
Рейтинг: 0 / 0
09.02.2016, 14:22
| Ответить | Цитировать | Написать  
Has join
    #39166663
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
vyegorovAndrey Sribnyak
Код: sql
1.
tpcc=# EXPLAIN SELECT  su_name, ...


1. Пожалуйста `EXPLAIN (analyze, buffers) SELECT ...`.


Не выполняется, висит столко же , сколько и обычное выполнение
я пытался - отбил через час...
...
Рейтинг: 0 / 0
09.02.2016, 14:31
| Ответить | Цитировать | Написать  
Has join
    #39166669
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
vyegorovAndrey Sribnyak
Код: sql
1.
tpcc=# EXPLAIN SELECT  su_name, ...



2. Параллельной аггрегации пока нет, так что приблизится к MS SQL не выйдет.

Я уже согласен, если он выполнится за вменяемое время вообще...
(меньше часа желательно)
...
Рейтинг: 0 / 0
09.02.2016, 14:34
| Ответить | Цитировать | Написать  
Has join
    #39166676
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Andrey SribnyakLonepsychoAndrey Sribnyak,

а сделать индекс на dbo.stock типа
Код: sql
1.
2.
CREATE INDEX idx_stock_partiton ON dbo.stock
  USING btree (((s_i_id * s_w_id) % 10000));

и попробовать можно?

индексм создал:

но в плане я не вижу, что он должен участвовать... попробую запустить сейчас

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
tpcc=# CREATE INDEX idx_stock_partiton ON dbo.stock
tpcc-#   USING btree (((s_i_id * s_w_id) % 10000));
CREATE INDEX
Time: 116053.115 ms
tpcc=# EXPLAIN SELECT  su_name,
tpcc-#         su_address
tpcc-# FROM    dbo.supplier,
tpcc-#         dbo.nation
tpcc-# WHERE   su_suppkey IN
tpcc-#             (SELECT ((s_i_id * s_w_id) % 10000)
tpcc(#             FROM    dbo.stock
tpcc(#                     INNER JOIN dbo.item ON i_id = s_i_id
tpcc(#                     INNER JOIN dbo.order_line ON ol_i_id = s_i_id
tpcc(#             WHERE   ol_delivery_d > '20151213'
tpcc(#                     AND  i_data LIKE '%%4%%'
tpcc(#             GROUP   BY s_i_id,
tpcc(#                     s_w_id,
tpcc(#                     s_quantity
tpcc(#             HAVING  650*s_quantity > sum(ol_quantity))
tpcc-#         AND  su_nationkey = n_nationkey
tpcc-#         AND UPPER(n_name) LIKE '%GERMANY%'
tpcc-# ORDER   BY su_name;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=351169774.40..351169774.60 rows=80 width=57)
   Sort Key: supplier.su_name
   ->  Hash Join  (cost=351169769.09..351169771.87 rows=80 width=57)
         Hash Cond: ((((stock.s_i_id * stock.s_w_id) % 10000)) = supplier.su_suppkey)
         ->  HashAggregate  (cost=351169346.87..351169348.87 rows=200 width=4)
               Group Key: ((stock.s_i_id * stock.s_w_id) % 10000)
               ->  HashAggregate  (cost=351013346.87..351109346.87 rows=4800000 width=12)
                     Group Key: stock.s_i_id, stock.s_w_id, stock.s_quantity
                     Filter: ((650 * stock.s_quantity) > sum(order_line.ol_quantity))
                     ->  Hash Join  (cost=3312648.69..200649339.20 rows=15036400767 width=12)
                           Hash Cond: (order_line.ol_i_id = item.i_id)
                           ->  Seq Scan on order_line  (cost=0.00..3284336.60 rows=72511778 width=6)
                                 Filter: (ol_delivery_d > '2015-12-13 00:00:00'::timestamp without time zone)
                           ->  Hash  (cost=3052926.69..3052926.69 rows=20777760 width=14)
                                 ->  Hash Join  (cost=3330.09..3052926.69 rows=20777760 width=14)
                                       Hash Cond: (stock.s_i_id = item.i_id)
                                       ->  Seq Scan on stock  (cost=0.00..2661819.00 rows=48000000 width=10)
                                       ->  Hash  (cost=2789.00..2789.00 rows=43287 width=4)
                                             ->  Seq Scan on item  (cost=0.00..2789.00 rows=43287 width=4)
                                                   Filter: (i_data ~~ '%%4%%'::text)
         ->  Hash  (cost=420.21..420.21 rows=161 width=61)
               ->  Hash Join  (cost=3.10..420.21 rows=161 width=61)
                     Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
                     ->  Seq Scan on supplier  (cost=0.00..378.00 rows=10000 width=65)
                     ->  Hash  (cost=3.08..3.08 rows=1 width=4)
                           ->  Seq Scan on nation  (cost=0.00..3.08 rows=1 width=4)
                                 Filter: (upper((n_name)::text) ~~ '%GERMANY%'::text)



Можно ли выполнить
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
explain analyze
SELECT ((s_i_id * s_w_id) % 10000)
            FROM    dbo.stock
                    INNER JOIN dbo.item ON i_id = s_i_id
                    INNER JOIN dbo.order_line ON ol_i_id = s_i_id
            WHERE   ol_delivery_d > '20151008'
                    AND  i_data LIKE '%%4%%'
            GROUP   BY s_i_id,
                    s_w_id,
                    s_quantity


или он тоже зависает надолго?

Если зависает то что говорит
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
explain analyze
SELECT ((s_i_id * s_w_id) % 10000)
            FROM    dbo.stock
                    INNER JOIN dbo.order_line ON ol_i_id = s_i_id
            WHERE   ol_delivery_d > '20151008'
            GROUP   BY s_i_id,
                    s_w_id,
                    s_quantity


и что говорит
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
explain analyze
SELECT ((s_i_id * s_w_id) % 10000)
            FROM    dbo.stock
                    INNER JOIN dbo.item ON i_id = s_i_id
            WHERE   i_data LIKE '%%4%%'
            GROUP   BY s_i_id,
                    s_w_id,
                    s_quantity


?

PS: у меня есть подозрение что другие базы выполняют этот подзапрос следующим методом (ну или похожим)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
explain analyze WITH 
ol AS (
  SELECT sum(ol_quantity) as sum_ol_quantity,  ol_i_id FROM dbo.order_line WHERE ol_delivery_d > '20151008' GROUP BY ol_i_id
),
s AS (
  SELECT s_i_id, s_w_id, s_quantity
  FROM dbo.stock
  WHERE EXISTS (SELECT * FROM dbo.item WHERE i_id = s_i_id AND i_data LIKE '%%4%%')
)
SELECT ((s_i_id * s_w_id) % 10000) FROM s
WHERE EXISTS (SELECT * FROM ol WHERE ol_i_id = s_i_id AND 650*s_quantity > sum_ol_quantity);



таким образом избегая безумного по обьему результата обьединения таблиц dbo.order_line и dbo.item по ol_i_id = s_i_id

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
09.02.2016, 14:43
| Ответить | Цитировать | Написать  
Has join
    #39166716
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Andrey Sribnyak,

Ясно как оно делает... это я не угадал слегка... mssql делает вот что:

explain analyze WITH
ol AS (
SELECT sum(ol_quantity) as sum_ol_quantity, ol_i_id FROM dbo.order_line
WHERE ol_delivery_d > '20151008'
AND EXISTS (SELECT * FROM dbo.item WHERE i_id = ol_i_id AND i_data LIKE '%%4%%')
GROUP BY ol_i_id
)
SELECT ((s_i_id * s_w_id) % 10000) FROM dbo.stock
WHERE EXISTS (SELECT * FROM ol WHERE ol_i_id = s_i_id AND 650*s_quantity > sum_ol_quantity);

В принципе это корректная замена но предпросчет частичных аггрегатов не поддерживается postgresql оптимизатором.
Тут запрос надо переписывать под pg.

Ключевое тут - сначала подсчитать суммы итемов в dbo.order_line а потом уже начинать соединять результат с dbo.stock.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
09.02.2016, 15:04
| Ответить | Цитировать | Написать  
Has join
    #39166739
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
Maxim Boguk,

Последний - смог выполнить:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
tpcc=# explain analyze WITH
tpcc-# ol AS (
tpcc(#   SELECT sum(ol_quantity) as sum_ol_quantity,  ol_i_id FROM dbo.order_line WHERE ol_delivery_d > '20151008' GROUP BY ol_i_id
tpcc(# ),
tpcc-# s AS (
tpcc(#   SELECT s_i_id, s_w_id, s_quantity
tpcc(#   FROM dbo.stock
tpcc(#   WHERE EXISTS (SELECT * FROM dbo.item WHERE i_id = s_i_id AND i_data LIKE '%%4%%')
tpcc(# )
tpcc-# SELECT ((s_i_id * s_w_id) % 10000) FROM s
tpcc-# WHERE EXISTS (SELECT * FROM ol WHERE ol_i_id = s_i_id AND 650*s_quantity > sum_ol_quantity);
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=7061398.89..7909634.12 rows=4696112 width=8) (actual time=159265.878..252366.418 rows=21061679 loops=1)
   Hash Cond: (s.s_i_id = ol.ol_i_id)
   Join Filter: ((650 * s.s_quantity) > ol.sum_ol_quantity)
   Rows Removed by Join Filter: 379921
   CTE ol
     ->  HashAggregate  (cost=4004220.85..4005221.17 rows=100032 width=6) (actual time=159017.837..159070.055 rows=100000 loops=1)
           Group Key: order_line.ol_i_id
           ->  Seq Scan on order_line  (cost=0.00..3284336.60 rows=143976849 width=6) (actual time=0.005..68685.872 rows=143991253 loops=1)
                 Filter: (ol_delivery_d > '2015-10-08 00:00:00'::timestamp without time zone)
   CTE s
     ->  Hash Join  (cost=3330.09..3052926.69 rows=28176672 width=10) (actual time=60.673..53143.037 rows=21441600 loops=1)
           Hash Cond: (stock.s_i_id = item.i_id)
           ->  Seq Scan on stock  (cost=0.00..2661819.00 rows=48000000 width=10) (actual time=0.007..23136.738 rows=48000000 loops=1)
           ->  Hash  (cost=2789.00..2789.00 rows=43287 width=4) (actual time=60.022..60.022 rows=44670 loops=1)
                 Buckets: 65536  Batches: 1  Memory Usage: 5922kB
                 ->  Seq Scan on item  (cost=0.00..2789.00 rows=43287 width=4) (actual time=0.007..38.686 rows=44670 loops=1)
                       Filter: (i_data ~~ '%%4%%'::text)
                       Rows Removed by Filter: 55330
   ->  CTE Scan on s  (cost=0.00..563533.44 rows=28176672 width=10) (actual time=60.676..71887.417 rows=21441600 loops=1)
   ->  Hash  (cost=2000.64..2000.64 rows=100032 width=12) (actual time=159204.759..159204.759 rows=100000 loops=1)
         Buckets: 131072  Batches: 1  Memory Usage: 5712kB
         ->  CTE Scan on ol  (cost=0.00..2000.64 rows=100032 width=12) (actual time=159017.846..159158.584 rows=100000 loops=1)
 Planning time: 1.256 ms
 Execution time: 260223.348 ms
(24 rows)



Первых два, все еще висят
...
Рейтинг: 0 / 0
09.02.2016, 15:19
| Ответить | Цитировать | Написать  
Has join
    #39166753
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
Maxim BogukAndrey Sribnyak,

Ясно как оно делает... это я не угадал слегка... mssql делает вот что:

explain analyze WITH
ol AS (
SELECT sum(ol_quantity) as sum_ol_quantity, ol_i_id FROM dbo.order_line
WHERE ol_delivery_d > '20151008'
AND EXISTS (SELECT * FROM dbo.item WHERE i_id = ol_i_id AND i_data LIKE '%%4%%')
GROUP BY ol_i_id
)
SELECT ((s_i_id * s_w_id) % 10000) FROM dbo.stock
WHERE EXISTS (SELECT * FROM ol WHERE ol_i_id = s_i_id AND 650*s_quantity > sum_ol_quantity);

В принципе это корректная замена но предпросчет частичных аггрегатов не поддерживается postgresql оптимизатором.
Тут запрос надо переписывать под pg.

Ключевое тут - сначала подсчитать суммы итемов в dbo.order_line а потом уже начинать соединять результат с dbo.stock.

--
Maxim Boguk
www.postgresql-consulting.ru

Да, так получше, спасибо:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
tpcc=# explain analyze WITH
tpcc-# ol AS (
tpcc(# SELECT sum(ol_quantity) as sum_ol_quantity, ol_i_id FROM dbo.order_line
tpcc(# WHERE ol_delivery_d > '20151213'
tpcc(# AND EXISTS (SELECT * FROM dbo.item WHERE i_id = ol_i_id AND i_data LIKE '%%4%%')
tpcc(# GROUP BY ol_i_id
tpcc(# )
tpcc-# SELECT ((s_i_id * s_w_id) % 10000) FROM dbo.stock
tpcc-# WHERE EXISTS (SELECT * FROM ol WHERE ol_i_id = s_i_id AND 650*s_quantity > sum_ol_quantity);
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=3990501.11..8235707.55 rows=8000000 width=8) (actual time=109939.010..170311.961 rows=21441600 loops=1)
   Merge Cond: (stock.s_i_id = ol.ol_i_id)
   Join Filter: ((650 * stock.s_quantity) > ol.sum_ol_quantity)
   CTE ol
     ->  HashAggregate  (cost=3985044.98..3985549.59 rows=50461 width=6) (actual time=109851.937..109873.532 rows=44670 loops=1)
           Group Key: order_line.ol_i_id
           ->  Hash Semi Join  (cost=3330.09..3827919.22 rows=31425153 width=6) (actual time=57.587..90472.749 rows=32518788 loops=1)
                 Hash Cond: (order_line.ol_i_id = item.i_id)
                 ->  Seq Scan on order_line  (cost=0.00..3284336.60 rows=72627697 width=6) (actual time=0.008..43669.315 rows=72793790 loops=1)
                       Filter: (ol_delivery_d > '2015-12-13 00:00:00'::timestamp without time zone)
                       Rows Removed by Filter: 71197463
                 ->  Hash  (cost=2789.00..2789.00 rows=43287 width=4) (actual time=57.512..57.512 rows=44670 loops=1)
                       Buckets: 65536  Batches: 1  Memory Usage: 2083kB
                       ->  Seq Scan on item  (cost=0.00..2789.00 rows=43287 width=4) (actual time=0.006..36.531 rows=44670 loops=1)
                             Filter: (i_data ~~ '%%4%%'::text)
                             Rows Removed by Filter: 55330
   ->  Index Scan using ix_siid_stock on stock  (cost=0.56..3428278.56 rows=48000000 width=10) (actual time=0.012..30507.424 rows=47997121 loops=1)
   ->  Sort  (cost=4950.95..5077.10 rows=50461 width=12) (actual time=109938.533..109994.569 rows=44670 loops=1)
         Sort Key: ol.ol_i_id
         Sort Method: quicksort  Memory: 3630kB
         ->  CTE Scan on ol  (cost=0.00..1009.22 rows=50461 width=12) (actual time=109851.943..109912.903 rows=44670 loops=1)
 Planning time: 0.576 ms
 Execution time: 177796.197 ms
(23 rows)
...
Рейтинг: 0 / 0
09.02.2016, 15:26
| Ответить | Цитировать | Написать  
Has join
    #39166768
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Andrey Sribnyak,

А так лучше не станет?
Код: sql
1.
2.
CREATE INDEX i_order_line_delivery_n_id ON dbo.order_line(ol_delivery_d, ol_i_id);
ANALYZE dbo.order_line;
...
Рейтинг: 0 / 0
09.02.2016, 15:39
| Ответить | Цитировать | Написать  
Has join
    #39166797
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
vyegorovAndrey Sribnyak,

А так лучше не станет?
Код: sql
1.
2.
CREATE INDEX i_order_line_delivery_n_id ON dbo.order_line(ol_delivery_d, ol_i_id);
ANALYZE dbo.order_line;



Там, как минимум у таблицу Order_line есть используемое поле ol_quantity

я даже пытался "читить таким образом"
создал фильтрованный индекс по дате, и посмотрел, что выйдет:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
tpcc=# CREATE INDEX i_order_line_delivery_n_id ON dbo.order_line(ol_i_id,ol_quantity) where ol_delivery_d> '20151213';
CREATE INDEX
Time: 207803.158 ms
tpcc=#
tpcc=#
tpcc=#
tpcc=# explain SELECT  su_name,
tpcc-#         su_address
tpcc-# FROM    dbo.supplier,
tpcc-#         dbo.nation
tpcc-# WHERE   su_suppkey IN
tpcc-#             (SELECT MOD((s_i_id * s_w_id), 10000)
tpcc(#             FROM    dbo.stock
tpcc(#                     INNER JOIN dbo.item ON i_id = s_i_id
tpcc(#                     INNER JOIN dbo.order_line ON ol_i_id = s_i_id
tpcc(#             WHERE   ol_delivery_d > '20151213'
tpcc(#                     AND  i_data LIKE '%%4%%'
tpcc(#             GROUP   BY s_i_id,
tpcc(#                     s_w_id,
tpcc(#                     s_quantity
tpcc(#             HAVING  650*s_quantity > sum(ol_quantity))
tpcc-#         AND  su_nationkey = n_nationkey
tpcc-#         AND n_name LIKE '%Germany%'
tpcc-# ORDER   BY su_name;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=352193065.25..352193065.45 rows=80 width=57)
   Sort Key: supplier.su_name
   ->  Hash Join  (cost=352193059.94..352193062.72 rows=80 width=57)
         Hash Cond: ((mod((stock.s_i_id * stock.s_w_id), 10000)) = supplier.su_suppkey)
         ->  HashAggregate  (cost=352192638.03..352192640.03 rows=200 width=4)
               Group Key: mod((stock.s_i_id * stock.s_w_id), 10000)
               ->  HashAggregate  (cost=352036638.03..352132638.03 rows=4800000 width=12)
                     Group Key: stock.s_i_id, stock.s_w_id, stock.s_quantity
                     Filter: ((650 * stock.s_quantity) > sum(order_line.ol_quantity))
                     ->  Hash Join  (cost=3312648.69..201195905.38 rows=15084073265 width=12)
                           Hash Cond: (order_line.ol_i_id = item.i_id)
                           ->  Seq Scan on order_line  (cost=0.00..3284336.60 rows=72627697 width=6)
                                 Filter: (ol_delivery_d > '2015-12-13 00:00:00'::timestamp without time zone)
                           ->  Hash  (cost=3052926.69..3052926.69 rows=20777760 width=14)
                                 ->  Hash Join  (cost=3330.09..3052926.69 rows=20777760 width=14)
                                       Hash Cond: (stock.s_i_id = item.i_id)
                                       ->  Seq Scan on stock  (cost=0.00..2661819.00 rows=48000000 width=10)
                                       ->  Hash  (cost=2789.00..2789.00 rows=43287 width=4)
                                             ->  Seq Scan on item  (cost=0.00..2789.00 rows=43287 width=4)
                                                   Filter: (i_data ~~ '%%4%%'::text)
         ->  Hash  (cost=419.90..419.90 rows=161 width=61)
               ->  Hash Join  (cost=2.79..419.90 rows=161 width=61)
                     Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
                     ->  Seq Scan on supplier  (cost=0.00..378.00 rows=10000 width=65)
                     ->  Hash  (cost=2.77..2.77 rows=1 width=4)
                           ->  Seq Scan on nation  (cost=0.00..2.77 rows=1 width=4)
                                 Filter: (n_name ~~ '%Germany%'::text)
(27 rows)

Time: 53.805 ms




И только, если явным образом запретить:

Код: sql
1.
tpcc=# SET enable_seqscan TO off;




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


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
tpcc=# explain SELECT  su_name,
        su_address
FROM    dbo.supplier,
        dbo.nation
WHERE   su_suppkey IN
            (SELECT MOD((s_i_id * s_w_id), 10000)
            FROM    dbo.stock
                    INNER JOIN dbo.item ON i_id = s_i_id
                    INNER JOIN dbo.order_line ON ol_i_id = s_i_id
            WHERE   ol_delivery_d > '20151213'
                    AND  i_data LIKE '%%4%%'
            GROUP   BY s_i_id,
                    s_w_id,
                    s_quantity
            HAVING  650*s_quantity > sum(ol_quantity))
        AND  su_nationkey = n_nationkey
        AND n_name LIKE '%Germany%'
ORDER   BY su_name;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=353500870.94..353500871.14 rows=80 width=57)
   Sort Key: supplier.su_name
   ->  Hash Join  (cost=353500865.64..353500868.42 rows=80 width=57)
         Hash Cond: ((mod((stock.s_i_id * stock.s_w_id), 10000)) = supplier.su_suppkey)
         ->  HashAggregate  (cost=353500258.99..353500260.99 rows=200 width=4)
               Group Key: mod((stock.s_i_id * stock.s_w_id), 10000)
               ->  HashAggregate  (cost=353344258.99..353440258.99 rows=4800000 width=12)
                     Group Key: stock.s_i_id, stock.s_w_id, stock.s_quantity
                     Filter: ((650 * stock.s_quantity) > sum(order_line.ol_quantity))
                     ->  Hash Join  (cost=4554513.94..202503526.34 rows=15084073265 width=12)
                           Hash Cond: (stock.s_i_id = item.i_id)
                           ->  Index Scan using ix_siid_stock on stock  (cost=0.56..3428278.56 rows=48000000 width=10)
                           ->  Hash  (cost=4161698.96..4161698.96 rows=31425153 width=10)
                                 ->  Hash Join  (cost=1182801.36..4161698.96 rows=31425153 width=10)
                                       Hash Cond: (order_line.ol_i_id = item.i_id)
                                       ->  Bitmap Heap Scan on order_line  (cost=1177863.98..3570156.19 rows=72627697 width=6)
                                             Recheck Cond: (ol_delivery_d > '2015-12-13 00:00:00'::timestamp without time zone)
                                             ->  Bitmap Index Scan on i_order_line_delivery_n_id  (cost=0.00..1159707.05 rows=72627697 width=0)
                                       ->  Hash  (cost=4396.29..4396.29 rows=43287 width=4)
                                             ->  Index Scan using item_c1 on item  (cost=0.29..4396.29 rows=43287 width=4)
                                                   Filter: (i_data ~~ '%%4%%'::text)
         ->  Hash  (cost=604.63..604.63 rows=161 width=61)
               ->  Hash Join  (cost=14.52..604.63 rows=161 width=61)
                     Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
                     ->  Index Scan using supplier_c1 on supplier  (cost=0.29..551.28 rows=10000 width=65)
                     ->  Hash  (cost=14.23..14.23 rows=1 width=4)
                           ->  Index Scan using nation_c1 on nation  (cost=0.14..14.23 rows=1 width=4)
                                 Filter: (n_name ~~ '%Germany%'::text)
...
Рейтинг: 0 / 0
09.02.2016, 16:07
| Ответить | Цитировать | Написать  
Has join
    #39166805
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Sribnyak
Участник
В общем проблему я закрыл, спасибо Мaxim Boguk
...
Рейтинг: 0 / 0
09.02.2016, 16:11
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Has join
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39166435&tid=1997451]:
 0ms
get settings:
 8ms
get forum list:
 17ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 174ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 207ms
total:  468ms
созд. / загр.: 468ms
x
x
Закрыть


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