Оптимизация запросов postgresql - база данных объемом 2 ГБ в процессе выполнения запроса достигает 50 ГБ.

Question

Оптимизация запросов postgresql - база данных объемом 2 ГБ в процессе выполнения запроса достигает 50 ГБ.

У меня есть таблица базы данных под названием "Цены", которая содержит в себе данные, где есть разные товары с разными ценами и другими свойствами.

Моя цель - сгруппировать эти данные по часам (многие образцы собираются ежечасно). Обобщенные данные должны быть вставлены в таблицу под названием "HourlyData".

Проблема, с которой я сталкиваюсь, заключается в том, что моя база данных, занимающая примерно 2 ГБ пространства примерно в данный момент, в результате выполнения запроса использует более 40 ГБ. Это плохая новость для меня, потому что я использую VPS, а емкость жесткого диска ограничена, и у меня не остается свободного места на устройстве для завершения выполнения запроса.

Я включил SQLfiddle, чтобы обеспечить лучшую читаемость кода здесь: http://sqlfiddle.com/

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

Поскольку я новичок в postgresql/SQL - для поддержки этой тонны кода, я разбил его на временные таблицы вместо одного большого запроса со многими подзапросами внутри. Я не уверен, является ли это плохой практикой использования временных таблиц или нет, но я не заметил различий в CACHE с обеих сторон.

У меня вопрос, как я мог бы сделать это текущее выполнение более эффективно? Я использую множество функций AVG и LAG в конце моей второй функции SQL, я предполагаю, что именно они виноваты в создании кэша, но я не нашел решения, как я мог бы сделать этот кусок за куском, который потребовал бы меньше кеша, так как функция LAG не может быть использована с командой UPDATE.

Одним из решений, конечно, является обобщение данных чаще в будущем. Другое решение, которое я придумала, - выгрузить базу данных, загрузить ее с VPS и запустить функции с локального компьютера, где пространство на жестком диске не является проблемой. Тем не менее, я надеюсь, что это более разрешимо с более опытным программированием SQL - если нет, то есть ли что-нибудь, что я мог бы сделать, чтобы оптимизировать текущий код в любом случае с точки зрения размера кэша?

Код таблицы

-- Table: "Prices"

-- DROP TABLE "Prices";

CREATE TABLE "Prices"
(
  data_id integer,
  level smallint,
  sell_price integer,
  buy_price integer,
  sell_count integer,
  buy_count integer, -- 
  name character varying(100),
  date timestamp without time zone DEFAULT ('now'::text)::timestamp(1) without time zone,
  "ID" serial NOT NULL,
  CONSTRAINT "Prices_pkey" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);



-- Index: "Prices_ID_idx"


CREATE INDEX "Prices_ID_idx"
  ON "Prices"
  USING btree
  ("ID");

-- Index: "Prices_data_id_idx"

CREATE INDEX "Prices_data_id_idx"
  ON "Prices"
  USING btree
  (data_id);

-- Table: "HourlyData"


CREATE TABLE "HourlyData"
(
  data_id bigint,
  name character varying(100),
  date_time timestamp without time zone,
  hour integer,
  day integer,
  buy numeric(20,4),
  sell numeric(20,4),
  prev_buy numeric(20,4),
  prev_sell numeric(20,4),
  buy_count integer,
  sell_count integer,
  prev_buy_count integer,
  prev_sell_count integer,
  ab_change numeric(10,2),
  as_change numeric(10,2),
  abc_change numeric(10,2),
  asc_change numeric(10,2),
  "ID" integer NOT NULL DEFAULT nextval('"DailyData_ID_seq"'::regclass),
  CONSTRAINT "DailyData_pkey" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);


-- Index: "DailyData_data_id_idx"


CREATE INDEX "DailyData_data_id_idx"
  ON "HourlyData"
  USING btree
  (data_id);


INSERT INTO Prices
    ("data_id", "level", "sell_price", "buy_price", "sell_count", "buy_count", "name", "date")
VALUES
    (28262, 80, 18899, 15000, 53, 66, 'random_item', '2013-12-16 01:38:07'),
    (28262, 80, 18899, 15000, 53, 66, 'random_item', '2013-12-16 01:44:31'),
    (28262, 80, 18987, 15000, 46, 65, 'random_item', '2013-12-16 01:30:22'),
    (28262, 80, 18987, 16000, 49, 65, 'random_item', '2013-12-16 01:00:19'),
    (28265, 80, 18987, 16000, 48, 64, 'random_itema', '2013-12-16 01:30:20'),
    (28265, 80, 18987, 16000, 48, 64, 'random_itema', '2013-12-16 01:00:21'),
    (28265, 80, 17087, 16000, 49, 63, 'random_itema', '2013-12-16 01:30:22'),
    (28262, 80, 18980, 5028, 48, 62, 'random_item', '2013-12-16 10:00:28'),
    (28262, 80, 18975, 5528, 50, 60, 'random_item', '2013-12-16 10:30:30'),
    (28262, 80, 18975, 5228, 51, 59, 'random_item', '2013-12-16 10:00:27'),
    (28262, 80, 18975, 5500, 52, 59, 'random_item', '2013-12-16 10:30:21'),
    (28262, 80, 18975, 5600, 53, 59, 'random_item', '2013-12-16 10:00:23'),
    (28262, 80, 18979, 5700, 50, 58, 'random_item', '2013-12-16 10:30:28'),
    (28262, 80, 18977, 5028, 51, 56, 'random_item', '2013-12-16 10:00:23'),
    (28264, 80, 18978, 5028, 51, 54, 'random_itemaw', '2013-12-16 10:30:25'),
    (28264, 80, 18979, 5628, 50, 54, 'random_itemaw', '2013-12-16 10:00:28'),
    (28264, 80, 18979, 5028, 52, 64, 'random_itemaw', '2013-12-16 10:30:26'),
    (28264, 80, 18979, 15028, 52, 64, 'random_item', '2013-12-16 11:00:25'),
    (28264, 80, 17977, 15028, 56, 63, 'random_item', '2013-12-16 11:30:24'),
    (28264, 80, 17977, 15029, 58, 62, 'random_item', '2013-12-16 11:00:30'),
    (28262, 80, 17977, 15027, 58, 62, 'random_item', '2013-12-16 11:30:22'),
    (28262, 80, 16000, 15022, 59, 49, 'random_item', '2013-12-16 11:00:26'),
    (28262, 80, 17979, 15021, 56, 49, 'random_item', '2013-12-16 11:30:26'),
    (28262, 80, 17969, 15023, 58, 44, 'random_item', '2013-12-16 11:00:31'),
    (28262, 80, 18987, 15027, 48, 44, 'random_item', '2013-12-16 12:30:33'),
    (28262, 80, 20819, 15027, 40, 43, 'random_item', '2013-12-16 12:00:32'),
    (28262, 80, 21810, 15034, 37, 48, 'random_item', '2013-12-16 12:30:24'),
    (28262, 80, 21810, 15037, 39, 49, 'random_item', '2013-12-16 22:00:18'),
    (28262, 80, 21810, 15038, 39, 49, 'random_item', '2013-12-16 22:30:25'),
    (28262, 80, 21810, 15038, 39, 49, 'random_item', '2013-12-16 22:00:25'),
    (28262, 80, 21710, 15039, 40, 49, 'random_item', '2013-12-16 22:30:24'),
    (28262, 80, 21709, 15040, 41, 49, 'random_item', '2013-12-16 22:00:24'),
    (28262, 80, 21709, 15040, 41, 49, 'random_item', '2013-12-16 22:30:22'),
    (28262, 80, 21709, 15040, 41, 49, 'random_item', '2013-12-16 23:00:24'),
    (28262, 80, 21709, 15041, 41, 49, 'random_item', '2013-12-16 23:30:27'),
    (28266, 80, 21708, 15042, 42, 50, 'random_item1', '2013-12-17 05:00:26'),
    (28266, 80, 20000, 15041, 43, 49, 'random_item1', '2013-12-17 05:30:21'),
    (28266, 80, 20000, 15097, 43, 52, 'random_item1', '2013-12-17 05:00:28'),
    (28262, 80, 20000, 15097, 43, 52, 'random_item', '2013-12-17 05:30:28'),
    (28262, 80, 20000, 15097, 43, 52, 'random_item', '2013-12-17 05:00:31'),
    (28262, 80, 20000, 15097, 44, 51, 'random_item', '2013-12-17 05:30:34'),
    (28262, 80, 19997, 15097, 44, 47, 'random_item', '2013-12-17 05:00:20'),
    (28262, 80, 19997, 15098, 44, 50, 'random_item', '2013-12-17 05:30:26'),
    (28262, 80, 19997, 15098, 44, 50, 'random_item', '2013-12-17 05:00:24'),
    (28262, 80, 19997, 15098, 44, 49, 'random_item', '2013-12-17 05:35:44'),
    (28262, 80, 19996, 15098, 45, 48, 'random_item', '2013-12-17 05:00:22'),
    (28262, 80, 19996, 15097, 46, 47, 'random_item', '2013-12-17 05:30:24'),
    (28262, 80, 19996, 15097, 46, 47, 'random_item', '2013-12-17 05:00:29'),
    (28262, 80, 19996, 15097, 46, 47, 'random_item', '2013-12-17 05:30:24'),
    (28262, 80, 19996, 15041, 47, 46, 'random_item', '2013-12-17 05:00:25')
;

функции

-- Function: percentageincrease(numeric, numeric)

CREATE OR REPLACE FUNCTION percentageincrease(lugeja numeric, nimetaja numeric)
  RETURNS numeric AS
$BODY$
BEGIN
    IF Nimetaja IS NULL or Nimetaja = 0 
        THEN RETURN 0;
    ELSE
        RETURN ROUND((Lugeja - Nimetaja) / Nimetaja * 100, 2);
    END IF;
END;
$BODY$
  LANGUAGE plpgsql;

-- Function: process_hourly_data()

CREATE OR REPLACE FUNCTION process_hourly_data()
  RETURNS void AS
$BODY$  

    CREATE TEMP TABLE "TEMP_summarize1" AS
    SELECT
                prices.data_id AS data_id,
                prices.name AS name,

                date_part('hour', prices.date) AS hour,
                date_part('day', prices.date) AS day,
                prices.date AS date_var,
                prices.buy_price,
                prices.sell_price,                            
                prices.sell_count,
                prices.buy_count

    FROM "Prices" as prices;

    CREATE TEMP TABLE "TEMP_summarize2"
    (
        item_name character varying(100),
        data_id bigint, 
        hour integer,
        day integer,
        date_var timestamp without time zone,
        avgbuy smallint,
        avgsell smallint,
        avgsellCount smallint,
        avgbuyCount smallint
    );


    PREPARE TEMP2 AS
    INSERT INTO "TEMP_summarize2"
    SELECT
       MAX(whatever.name) as item_name,
       whatever.data_id,
       prices.hour,
       MAX(prices.day) as day,
       MAX(prices.date_var) as date_var,
       AVG(prices.buy_price) AS avgbuy,
       AVG(prices.sell_price) AS avgsell,
       AVG(prices.sell_count) AS avgsellCount,
       AVG(prices.buy_count) AS avgbuyCount 
    FROM "TEMP_summarize1" AS prices, (SELECT data_id, name FROM "TEMP_summarize1" as whatever) AS whatever
        WHERE whatever.data_id = prices.data_id AND whatever.name = prices.name
        GROUP BY hour, whatever.data_id;

    PREPARE TEMP3 AS
    INSERT INTO "HourlyData"
    SELECT 
        data_id,
        item_name,
        date_var,
        hour,
        day,
        avgbuy,
        avgsell,
        LAG(avgbuy, 1, NULL) OVER(PARTITION BY data_id) AS last_avgbuy,
        LAG(avgsell, 1, NULL) OVER(PARTITION BY data_id) AS last_avgsell,
        avgsellCount,
        avgbuyCount,
        LAG(avgsellCount, 1, NULL) OVER (PARTITION BY data_id) AS last_avgsellCount,
        LAG(avgbuyCount, 1, NULL) OVER (PARTITION BY data_id) AS last_avgbuyCount,
        percentageincrease(LAG(avgbuy, 1, NULL) OVER(PARTITION BY data_id), avgbuy),
        percentageincrease(LAG(avgsell, 1, NULL) OVER(PARTITION BY data_id), avgsell),
        percentageincrease(LAG(avgsellCount, 1, NULL) OVER (PARTITION BY data_id), avgsellCount),
        percentageincrease(LAG(avgbuyCount, 1, NULL) OVER (PARTITION BY data_id), avgbuyCount)

    FROM "TEMP_summarize2"
    ORDER BY data_id, hour; 
    EXECUTE TEMP2;
    EXECUTE TEMP3;
$BODY$
  LANGUAGE sql;

0

sql postgresql average lag temporary

Источник

user1590407 02 янв '14 в 05:32

3 ответа

Решение

Я столкнулся с такой же проблемой. Проблема может быть вызвана из-за UPDATE заявления. Ты используешь COMMIT зафиксировать ваши изменения в базе данных? Если вы используете COMMIT для каждого UPDATE тогда размер кеша обязательно увеличится.

0

Источник

user2298700 02 янв '14 в 05:58

Вы не можете изменить внутреннее поведение функций Windows. Есть две возможности:

Тест на уменьшение work_mem (его можно уменьшить для каждого запроса) - это может уменьшить скорость вашего запроса, но может уменьшить потребление памяти
```
SET work_mem = '1MB'
```
Используйте хранимую процедуру с явной итерацией над курсором. Много функций `окна 'можно эмулировать (и это типичное решение в эпоху до-оконной функции). Он должен иметь значительно меньший объем памяти:
```
DO $$
DECLARE 
  t_1 foo;
  t_2 foo;
  t_3 foo;
  r foo;
FOR r IN SELECT * FROM foo ORDER BY some_col
LOOP
  /* move history records */
  t_3 := t_2;
  t_2 := t_1;
  t_1 := r;

  -- now in t_1, t_2, and t_3 are presorted records
  -- do something there
END LOOP;
$$;          
```

0

Источник

user406691 02 янв '14 в 06:07

Другие вопросы по тегам sql postgresql average lag temporary

user812837 02 янв '14 в 07:52 2014-01-02 07:52 · Accepted Answer · 2014-01-02 07:52

Вы можете попробовать что-то вроде следующего:

WITH Date_Range as (SELECT calendarDate, timeOfDay, 
                           calendarDate + timeOfDay as rangeStart,
                           (calendarDate + timeOfDay) + INTERVAL '1 hour' as rangeEnd
                    FROM Calendar
                    CROSS JOIN TimeOfDay
                    WHERE calendarDate >= CAST('2013-12-16' as DATE)
                          AND calendarDate < CAST('2013-12-18' as DATE))

INSERT INTO HourlyData

SELECT data_id,
       randomName,
       latestPriceChangeAt,
       EXTRACT(HOUR FROM timeOfDay) as hourOfDay,
       EXTRACT(DAY FROM calendarDate) as dayOfMonth,
       averageBuyPrice,
       averageSellPrice,
       previousAverageBuyPrice,
       previousAverageSellPrice,
       averageBuyCount,
       averageSellCount,
       previousAverageBuyCount,
       previousAverageSellCount,
       -- put the calls to your function here instead of these operations
       averageBuyPrice - previousAverageBuyPrice,
       averageSellPrice - previousAverageSellPrice,
       averageBuyCount - previousAverageBuyCount,
       averageSellCount - previousAverageSellCount


FROM (SELECT data_id, calendarDate, timeOfDay,
             MAX(date) as latestPriceChangeAt,
             MAX(name) as randomName,
             AVG(sell_price) as averageSellPrice,
             AVG(sell_count) as averageSellCount,
             AVG(buy_price) as averageBuyPrice,
             AVG(buy_count) as averageBuyCount,
             LAG(AVG(buy_price)) OVER(PARTITION BY data_id ORDER BY calendarDate, timeOfDay) as previousAverageBuyPrice,
             LAG(AVG(sell_price)) OVER(PARTITION BY data_id ORDER BY calendarDate, timeOfDay) as previousAverageSellPrice,
             LAG(AVG(buy_count)) OVER(PARTITION BY data_id ORDER BY calendarDate, timeOfDay) as previousAverageBuyCount,
             LAG(AVG(sell_count)) OVER(PARTITION BY data_id ORDER BY calendarDate, timeOfDay) as previousAverageSellCount
      FROM Date_Range
      JOIN Prices
        ON Prices.date >= Date_Range.rangeStart
           AND prices.date < Date_Range.rangeEnd
      GROUP BY data_id, calendarDate, timeOfDay) data;

(Используя вашу исходную SQL Fiddle в качестве основы)

Обратите внимание, что я изменил сводку так, чтобы она была также по дням, а не по часам, так как в противном случае ваши столбцы результатов будут немного странными. Это легко изменить, если вы хотите объединить все дни вместе. Я также предполагаю, что у вас есть таблица календаря / времени дня, которая смехотворно удобна для анализа. Если у вас его нет / вы не можете его создать, их можно легко создать с помощью рекурсивных CTE. Вся работа с запросами предполагает, что у вас также есть соответствующие индексы, иначе ваша система, скорее всего, создаст несколько временных...

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