LINUX.ORG.RU
решено Форум — Development  

[SQL] Структура БД

 


0

1

Имеем предметную область, в которой у нас есть корабли, маршруты кораблей и датчики кораблей(3 таблицы). Значений датчиков очень много, около 10 000 000, маршрутов до 100. В датчиках надо выполнять сложные функции выборок значений, которые занимают по 20 секунд. Это много. Поля проиндексированы. Мой руководитель предложил сделать следующее. При добавлении маршрута корабля создается отдельная таблица датчиков, название которой формируется по типу shipID||'___'||routeID. И обращение к датчикам происходит не по id маршрута, а по сформированному имени таблицы. Тоесть таблиц с датчиками у нас получается сколько маршрутов. Это бред как я считаю и очень неудобно, но время выборки сокращается до 1 секунды.

Как выйти из этой ситуации? Время критично.

← Велосипедостроение, PyQt
[c++] как использовать fork? →

костыли. но таки иногда приходится такое использовать.

exception13 ★★★★★
()

Сначала надо смотреть планы запросов и искать, что с ними не так. Может быть, нужны не простые индексы, а составные, или какое-то поле забыли проиндексировать, или ещё что-нибудь такое в этом роде. Через /dev/astral сложно понять.

den73 ★★★★★
()

Например, в данной ситуации напрашивается индекс по shipId, routeId. К тому же, в конкретной СУБД возможность использования индекса зависит от формулировки запроса. В Firebird, например, некоторые коррелированные (и даже вроде бы некоррелированные) подзапросы создают неоптимальный план, в итоге, я почти не пользуюсь подзапросами, а если пользуюсь, то с осторожностью. После MS SQL сервера это было для меня ловушкой, в которую я поначалу попадался.

den73 ★★★★★
()

Структуру таблиц давай, индексы и пример 20-секундного запроса

aydar ★★★★★
()
Ответ на: комментарий от den73

СУБД postgres, в таблице sensors 4 поля(sensorID, value, time, routeID), поиск ведется по всем полям, индекс B-tree, в where применяются только <, >, <=, >=. Все типы integer, кроме value(float8), индексировал как по всем, так и по всем кроме value.

YarikRock
() автор топика
Ответ на: комментарий от YarikRock 
CREATE OR REPLACE FUNCTION readLim(in bottom_range float8, in top_range float8, in insensorid integer, in inrouteid integer, out ofirstTime integer, out oendTime integer)
returns SETOF record as
$$
declare
v_rec record;
end_time integer;
counter1 integer;
counter2 integer;
localFirstTime integer;
localEndTime integer;
begin

SELECT MAX(time) FROM sensors WHERE routeid = $4 into end_time;

SELECT MIN(time) FROM sensors WHERE routeid = $4 into counter1;
counter2 := counter1;

while counter1 < end_time
loop

	SELECT MIN(time) FROM sensors WHERE (value > $2 AND time > counter1 AND sensorid = $3 AND routeid = $4) into localFirstTime;
	
	if(localFirstTime is not null) then
		counter1 := localFirstTime;
	else
		counter1 := end_time + 1;
	end if;

	SELECT MIN(time) FROM sensors WHERE (value <= $2 AND time > counter1 AND sensorid = $3 AND routeid = $4) into localEndTime;
	
	if(localEndTime is not null) then
		counter1 := localEndTime;
	else
		counter1 := end_time + 1; 
	end if;	
		
	select localFirstTime as f1, localEndTime as f2 into v_rec;
	ofirstTime := v_rec.f1;
	oendTime := v_rec.f2;
	return next;
	
end loop;

while counter2 < end_time
loop
	
	SELECT MIN(time) FROM sensors WHERE (value < $1 AND time > counter2 AND sensorid = $3 AND routeid = $4) into localFirstTime;
	
	if(localFirstTime is not null) then
		counter2 := localFirstTime;
	else
		counter2 := end_time + 1;
	end if;
	
	SELECT MIN(time) FROM sensors WHERE (value >= $1 AND time > counter2 AND sensorid = $3 AND routeid = $4) into localEndTime;
	
	if(localEndTime is not null) then
		counter2 := localEndTime;
	else
		counter2 := end_time + 1;
	end if;
	
	SELECT localFirstTime as f1, localEndTime as f2 into v_rec;
	ofirstTime := v_rec.f1;
	oendTime := v_rec.f2;
	return next;
end loop;
	
return;
end;
$$
language 'plpgsql';
YarikRock
() автор топика

Тоесть таблиц с датчиками у нас получается сколько маршрутов. Это бред как я считаю и очень неудобно


Это не бред а так и делают во всем мире я считаю

Karapuz ★★★★★
()
Ответ на: комментарий от YarikRock

Что-то я не вижу здесь привязки к кораблю. Как соотносятся в предметной области датчик и корабль? И вообще, вы пишете Большого Брата, что ли?

den73 ★★★★★
()
Ответ на: комментарий от YarikRock 
CREATE TABLE ships(
id SERIAL PRIMARY KEY,
shipName VARCHAR(256) UNIQUE
);

CREATE TABLE routes(
shipID integer references ships(id),
id INTEGER PRIMARY KEY,
creationDate integer NOT NULL,
exchangeDate integer NOT NULL,
alias varchar(256),
configID integer NOT NULL
);

CREATE TABLE sensors(
time INTEGER NOT NULL,
sensorID INTEGER NOT NULL,
value FLOAT8 NOT NULL,
routeID INTEGER REFERENCES routes(id)
);

create index sensorindex on sensors(routeid, sensorid, time);
YarikRock
() автор топика
Ответ на: комментарий от YarikRock

А сколько кораблей и датчиков?

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

Похоже, что запросов тут по сути всего два.

SELECT MAX(time) FROM sensors WHERE routeid = $4 into end_time;

SELECT MIN(time) FROM sensors WHERE routeid = $4 into counter1;

Здесь был бы уместен индекс по (routeid,time). Кроме того, это делатеся одним запросом. Select min(time),max(time) ... .

SELECT MIN(time) FROM sensors WHERE (value > $2 AND time > counter1 AND sensorid = $3 AND routeid = $4) into localFirstTime

А здесь, видимо, либо по sensorId,routeId,time либо по routeId,sensorId,time, хотя это зависит ещё от количества датчиков.

Корабли как-то тут совсем не фигурируют, непонятно, почему именно по кораблям предлагается разбивать.

den73 ★★★★★
()
Ответ на: комментарий от den73

Не по короблям, а по корабль+маршрут. Тоесть Вы предлагаете создать мне несколько индексов с пересекающимися полями? Датчиков до 100.

YarikRock
() автор топика
Ответ на: комментарий от YarikRock

> Тоесть Вы предлагаете создать мне несколько индексов с пересекающимися полями?
Да, это нормально.

den73 ★★★★★
()
Ответ на: комментарий от den73

>> либо по sensorId,routeId,time либо по routeId,sensorId,time

Серьезно есть разница в какой последовательности создавать?

YarikRock
() автор топика
Ответ на: комментарий от YarikRock

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

den73 ★★★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
← Велосипедостроение, PyQt
Development
[c++] как использовать fork? →