Как я могу ускорить выполнение этого запроса в postgres

У меня есть этот запрос, выполнение которого занимает 86 секунд.

select cust_id customer_id,
       cust_first_name customer_first_name,
       cust_last_name customer_last_name,
       cust_prf customer_prf,
       cust_birth_country customer_birth_country,
       cust_login customer_login,
       cust_email_address customer_email_address,
       date_year ddyear,
       sum(((stock_ls_price-stock_ws_price-stock_ds_price)+stock_es_price)/2) total_yr,
       's' stock_type
 from customer, stock, date
 where customer_k = stock_customer_k
   and stock_soldate_k = date_k
 group by cust_id, cust_first_name, cust_last_name, cust_prf, cust_birth_country, cust_login, cust_email_address, date_year;

ОБЪЯСНИТЕ РЕЗУЛЬТАТ АНАЛИЗА:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=639753.55..764040.06 rows=2616558 width=213) (actual time=81192.575..86536.398 rows=190581 loops=1)
   Group Key: customer.cust_id, customer.cust_first_name, customer.cust_last_name, customer.cust_prf, customer.cust_birth_country, customer.cust_login, customer.cust_email_address, date.date_year
   ->  Sort  (cost=639753.55..646294.95 rows=2616558 width=213) (actual time=81192.468..83977.960 rows=2685453 loops=1)
         Sort Key: customer.cust_id, customer.cust_first_name, customer.cust_last_name, customer.cust_prf, customer.cust_birth_country, customer.cust_login, customer.cust_email_address, date.date_year
         Sort Method: external merge  Disk: 460920kB
         ->  Hash Join  (cost=6527.66..203691.58 rows=2616558 width=213) (actual time=60.500..2306.082 rows=2685453 loops=1)
               Hash Cond: (stock.stock_customer_k = customer.customer_k)
               ->  Merge Join  (cost=1423.66..144975.59 rows=2744641 width=30) (actual time=8.820..1412.109 rows=2750311 loops=1)
                     Merge Cond: (date.date_k = stock.stock_soldate_k)
                     ->  Index Scan using date_key_idx on date (cost=0.29..2723.33 rows=73049 width=8) (actual time=0.013..7.164 rows=37622 loops=1)
                     ->  Index Scan using stock_soldate_k_index on stock  (cost=0.43..108829.12 rows=2880404 width=30) (actual time=0.004..735.043 rows=2750312 loops=1)
               ->  Hash  (cost=3854.00..3854.00 rows=100000 width=191) (actual time=51.650..51.650rows=100000 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 16139kB
                     ->  Seq Scan on customer  (cost=0.00..3854.00 rows=100000 width=191) (actual time=0.004..30.341 rows=100000 loops=1)
 Planning time: 1.761 ms
 Execution time: 86621.807 ms

У меня 3_. У меня есть индексы, созданные на cust_id, customer_k, stock_customer_k, stock_soldate_k и date_k.

Есть около 100 000 строк в customer, 3 000 000 строк в stock и 80 000 строк в date.

Как я могу ускорить выполнение этого запроса? Буду признателен за любую помощь!

ОПРЕДЕЛЕНИЯ ТАБЛИЦЫ

Дата

 Column              |     Type      | Modifiers
---------------------+---------------+-----------
 date_k              | integer       | not null
 date_id             | character(16) | not null
 date_date           | date          |
 date_year           | integer       |

снабжать

Column                 |     Type     | Modifiers
-----------------------+--------------+-----------
 stock_soldate_k       | integer      |
 stock_soltime_k       | integer      |
 stock_customer_k      | integer      |
 stock_ds_price        | numeric(7,2) |
 stock_es_price        | numeric(7,2) |
 stock_ls_price        | numeric(7,2) |
 stock_ws_price        | numeric(7,2) |

покупатель:

Column                     |         Type          | Modifiers
---------------------------+-----------------------+-----------
 customer_k                | integer               | not null
 customer_id               | character(16)         | not null
 cust_first_name           | character(20)         |
 cust_last_name            | character(30)         |
 cust_prf                  | character(1)          |
 cust_birth_country        | character varying(20) |
 cust_login                | character(13)         |
 cust_email_address        | character(50)         |

TABLE "stock" CONSTRAINT "st1" FOREIGN KEY (stock_soldate_k) REFERENCES date(date_k)

"st2" FOREIGN KEY (stock_customer_k) REFERENCES customer(customer_k)

postgresql sql-execution-plan explain
person Scra    schedule 20.07.2015    source источник
comment
Было бы полезно увидеть определение таблиц, индексов и ограничений.   -  person JimiLoe    schedule 21.07.2015

Ответы (2)


arrow_upward
1
arrow_downward

Попробуй это:

with stock_grouped as
     (select stock_customer_k, date_year, sum(((stock_ls_price-stock_ws_price-stock_ds_price)+stock_es_price)/2) total_yr
      from stock, date
      where stock_soldate_k = date_k
      group by stock_customer_k, date_year)
select cust_id customer_id,
       cust_first_name customer_first_name,
       cust_last_name customer_last_name,
       cust_prf customer_prf,
       cust_birth_country customer_birth_country,
       cust_login customer_login,
       cust_email_address customer_email_address,
       date_year ddyear,
       total_yr,
       's' stock_type
from customer, stock_grouped
where customer_k = stock_customer_k

Этот запрос предполагает группировку по объединению.

person Renzo    schedule 21.07.2015

arrow_upward
1
arrow_downward

Вы получаете большое снижение производительности, потому что около 450 МБ промежуточных данных хранятся извне: Sort Method: external merge Disk: 460920kB. Это происходит потому, что планировщику сначала необходимо выполнить условия соединения между тремя таблицами, включая, возможно, неэффективную таблицу customer, прежде чем может начаться агрегирование sum(), даже если агрегирование может быть отлично выполнено только для таблицы stock.

Запрос

Поскольку ваши таблицы довольно большие, вам лучше уменьшить количество подходящих строк как можно скорее и желательно до любого соединения. В данном случае это означает выполнение агрегирования таблицы stock в подзапросе и присоединение этого результата к двум другим таблицам:

SELECT c.cust_id AS customer_id,
       c.cust_first_name AS customer_first_name,
       c.cust_last_name AS customer_last_name,
       c.cust_prf AS customer_prf,
       c.cust_birth_country AS customer_birth_country,
       c.cust_login AS customer_login,
       c.cust_email_address AS customer_email_address,
       d.date_year AS ddyear,
       ss.total_yr,
       's' stock_type
FROM (
    SELECT 
      stock_customer_k AS ck,
      stock_soldate_k AS sdk,
      sum((stock_ls_price-stock_ws_price-stock_ds_price+stock_es_price)*0.5) AS total_yr
    FROM stock
    GROUP BY 1, 2) ss
JOIN customer c ON c.customer_k = ss.ck
JOIN date d ON d.date_k = ss.sdk;

Подзапрос на stock приведет к гораздо меньшему количеству строк, в зависимости от среднего количества заказов на одного клиента на дату. Кроме того, в функции sum() умножение на 0,5 намного дешевле, чем деление на 2 (хотя по большому счету это будет относительно незначительным).

Модель данных

Вам также следует серьезно взглянуть на свою модель данных.

В таблице customer вы используете такие типы данных, как char(30), которые всегда будут занимать 30 байт в вашей строке, даже если вы храните только «X». Использование типа данных varchar(30) намного эффективнее, когда многие строки короче заявленной максимальной ширины, поскольку он занимает меньше места и, следовательно, требует меньшего количества операций чтения страниц (и записи промежуточных данных).

В таблице stock для цен используется numeric(7,2). Использование типа данных numeric может дать точные результаты при выполнении множества повторяющихся операций с данными, но они также очень медленные. Тип данных double precision будет намного быстрее и одинаково точен в вашем сценарии. В целях презентации вы можете округлить значение до желаемой точности.

В качестве предложения создайте таблицу stock_f с типами данных double precision вместо numeric, скопируйте все данные из stock в stock_f и выполните запрос в этой таблице.

person Patrick    schedule 21.07.2015