Здравейте. Наложи ми се да реша следната задача:

Имам две таблици в база данни PostreSQL с колони name (text) и value (int). Идентифицирането на редовете става по полето "име". Всяка таблица може да съдържа само един ред с определено "име". Всяка таблица може да съдържа ред, с име, еднакво на ред от дргата таблица. Задачата е, двете таблици да се съберат така, че събирайки първата с втората (условно приемам едната за първа, а другата за втора), във втората таблица да получим всички редове с неповтарящо се име, а редовете с повтарящо се име да бъдат събрани (да се събере полето value).

Пример:

Първа таблица:

name: value:
Pesho 3
Kiko 4
Ivan 8

Втора таблица:

name: value:
Stefan 1
Ivan 2
Kiko 3

След събирането във втората таблица трябва да се получи:

name: value:
Pesho 3
Kiko 7
Ivan 10
Stefan 1

Задачата е малко странна. Едната таблица периодично се създава от специфичен софтуер (не мога да променя нищо в структурата и), а другата таблица служи за да "събира" първата. Въпросното "събиране" се прави периодично.

Аз написах функция на PHP която изпълнява задачата, но огромен проблем е скоростта. Тъй като нямам опит нито в писането на PHP, нито в бази данни, аз написах функция със следния алгоритъм, предаден накратко: запитваме за всички елементи от първата таблица; цикъл по всички редове на първата таблица: запитваме за ред със същото име във втората таблица , ако има - събираме редовете (полетата value) и ги ъпдейтваме във втората таблица, ако няма само добавяме реда към втората таблица.

Това работи, но бавно - за всеки ред от първата таблица (може да е доста голяма) се правят по две запитвания към базата данни, което, стува ми се, е причината за бавната работа.

Ще съм много благодарен, ако някой с опит в PostgreSQL ми даде напътствия за оптимизиране и още по-благодарен, ако ми предложи решение.

Благодаря предварително.

SELECT name, SUM(value) FROM t1 GROUP BY name;

това трябва да замине във втарата таблица така ли?

Съдържаниет е скрито
Влезте за да го видите

Ами не знам... ти ще кажеш. Виж примера - там съм показал какво трябва да се получи. Резултатът трябва да се запише във втората таблица, да. Целта е втората таблица да остава една и съща за продължително време, а първата таблица да се подменя периодично (след това подменяне се прави "сумирането", като резултатът остава във втората таблица). По този начин във втората таблица се натрупват суми на редовете появили се в първата таблица.

Благодаря за отделеното време.

В това, което си написал не виждам да се споменава за съществуването на втора таблица. Какво ще сумира то?

Бинго!

Та за какво ти е всъщност втората таблица?

Съдържаниет е скрито
Влезте за да го видите

Защото софтуерът, който създава първата таблица, не добавя редове, а създава таблицата наново. Изтрива всички редове и създава нови, без оглед на това, дали имената се повтарят, или не. Ако това, което си написал ще събере value на редовете с еднакво name в една таблица, тогава би ли могло предварително да направя:

CREATE TEMP TABLE teble3 AS SELECT * FROM table1, table2;

?

Виж какво е предложил phpGuru от PHP форума:

http://clubs.dir.bg/showflat.php?Cat=8&Board=php&Number=1939001212&page=0&view=collapsed&sb=5&vc=1

Кое е по-добрият вариант?

Добре. Това беше полезно уточнение.


Само това не използвай:
CREATE TEMP TABLE teble3 AS SELECT * FROM table1, table2;

Това е т.нар. Декартово Произведение т.е. Всеки ред от t1 комбиниран със всеки от t2 ....

Значи имам следното предложение. Не знам какъв е точния синтаксис в PostgreSQL - ще ти кажа как бих го направил в MySQL пък някой ще ти помогне с "превода"


CREATE TEMPORARY TABLE temp SELECT * FROM t1 UNION ALL SELECT * FROM t2;

DELETE FROM t2;

INSERT INTO t2 SELECT name, SUM(value) FROM TEMP GROUP BY name;

DROP TABLE TEMP;

т.е. Правиш Нова Временна таблица в която вмъкваш всички редове от t1 и всички от t2 (последователно - UNION ALL прави точно това) след това сумираш по име (GROUP BY name) и пращаш в t2

Ако в t2 имаш PRIMARY KEY(name) и ако това беше MySQL можеш да спестиш малко писане със:


CREATE TEMPORARY TABLE temp SELECT * FROM t1 UNION ALL SELECT * FROM t2;

REPLACE INTO t2 SELECT name, SUM(value) FROM TEMP GROUP BY name;

DROP TABLE TEMP;

Съдържаниет е скрито
Влезте за да го видите

Редактирано от salle на 19.02.03 00:07.

... просто прави:

INSERT INTO t2 SELECT * FROM t1; # предполагайки, че в t2 не е дефиниран Primary key

И когато ти затрябва сумата ...

SELECT name, SUM(value) FROM t2 GROUP BY name;

Съдържаниет е скрито
Влезте за да го видите

Ще пробвам с различните вариант и което работи най-добре то ще остане. Пак ти благодаря.

тези варианти са горе-долу това, което и аз ти предложих, но все пак с FULL JOIN най-вероятно ще е по-бързо отколкото с UNION ALL защото по-малко данни се трансферират и после няма да имаш GROUP BY, което за да работи ок, ти трябват индекси на временната таблица (може да си ги създадеш обаче, ако решиш така да го правиш)

за да ползваш REPLACE обаче, ще трябва да си създадеш тригер BEFORE INSERT в, който да изтриваш евентуланият дублиращ запис (искам да кажа в pgsql-а няма REPLACE), което ще е може би по-добре от DELETE FROM t2 и INSERT от нула, както аз го бях написал, ако данните в t2 са адски много

но, кое ще е най бързо - пробвай!

Сбъркал съм. Готовият софтуер не трие таблицата, а само добавя нови редове. Това, че досега триеше изцяло таблицата се дължеше на грешка в конфигурирането. Друго, което съм сбъркал - таблицата има още една колона.

Понеже вероятно за вас стана малко объркано, ето какво е истинското положение:

единствената задължителна таблица е първата - тя има колони name (text), value(int) и time(bigint). Primary key са name, time. Когато готовият софтуер бъде стартиран, той добавя нови редове към тази таблица. Тези нови редове обикновено са с "name", което вече го имаме, и ново value.
Ето така:

name: value: time:
pesho 5 123456789
ivan 6 123456789

след повторното пускане на програмата:

name: vakue: time:
pesho 5 123456789
ivan 6 123456789
dragan 6 234567890
pesho 3 234567890

Моята задача е да показвам в PHP сумата на полетата value на редовете с еднакви имена.

Мисля, че има два генерални варианта, които бяха споменати. Първият вариант е, таблицата да си остане една, а данните да се събират чак тогава, когато потрябват (чак когато поискаме от PHP да ги покаже). Вторият вариант е да създам втора таблица, където тези суми просто да се натрупват, а първата таблица да я трия, както беше досега. Как мислите, кой вариант е по-добър?

Засега съм направил така:
едната таблица (където готовия софтуер добавя редове) е t1;
втората таблица (където се събират сумите) е t2;
след като готовия софтуер запише данните в базата се прави следното:

CREATE TABLE tt AS SELECT COALESCE(t1.name, t2.name), COALESCE(t1.value, 0) + COALESCE(t2.value, 0) FROM t1 FULL JOIN t2 ON t1.name = t2.name;
DELETE FROM t2;
DELETE FROM t1;
INSERT INTO t2 SELECT * FROM tt;
DROP TABLE tt;

Как мислите, така ли да остане?

Извинявам се, че първоначално ви подведох и пак ви благодаря за помощта.

ами трябва да си изясниш дали искаш да пазиш все пак архив на данните (по-детайлно да се помни всеки запис)

иначе така ще стане, но е хубаво да го правиш в транзакция!!
begin;
....
commit;
или във

Съдържаниет е скрито
Влезте за да го видите

Съдържаниет е скрито
Влезте за да го видите

Ако оригиналната ти таблица не се трие а само се добавя тогава с пълна сила важи въпросът ми от

Съдържаниет е скрито
Влезте за да го видите

Съдържаниет е скрито
Влезте за да го видите

незнам дали чак толкова ще се натоварва, обаче

SELECT * FROM t2 ще е значително по-бързо отколкото SELECT name, sum(value) FROM t2 GROUP BY name

а като го прави така в t2 ще му е изнесена директно статистиката

ето примерно решени за директно генериране на t2

CREATE OR REPLACE FUNCTION fake_insert(text, integer) RETURNS bool
AS '
declare
rows integer;
t1_name alias for $1;
t1_value alias for $2;
begin
UPDATE t2 SET value = value + t1_value WHERE name = t1_name;
GET DIAGNOSTICS rows = ROW_COUNT;
if rows = 0 then
INSERT INTO t2 (name, value) VALUES (t1_name, t1_value);
end if;
return true;
end;
'
LANGUAGE 'plpgsql' STRICT;

и сега два варианта

1. данните си остават и в t1

CREATE RULE insert_on_t1 AS ON INSERT TO t1 DO SELECT fake_insert(NEW.name, NEW.value);

2. данните не остават в t1 (всъщност, даже и не влизат там - таблицата става фиктивна)

CREATE RULE insert_on_t1 AS ON INSERT TO t1 DO INSTEAD SELECT fake_insert(NEW.name, NEW.value);


има още едно две неща по функцията могат да се оптимизират, ама ... (мързи ме да търся как стават, а и ти да погледнеж нещо ;-))
(за да я пуснеш таз функция - трябва в базата да си пуснал plpgsql езика, за което виж

Съдържаниет е скрито
Влезте за да го видите

Много ти благодаря за всичко. Ефекта вече е повече от впечатляващ. От тук нататък ще се оправям сам. Благодаря също и на salle.

Засега ще родължа да си пускам статистиката през PHP, пък като прочета за функциите ще се опитам да го донаправя.

Последно остава така, нали (през PHP):

$query='
BEGIN;
CREATE TABLE tt AS SELECT COALESCE(t1.name, t2.name), COALESCE(t1.value, 0) + COALESCE(t2.value, 0) FROM t1 FULL JOIN t2 ON t1.name = t2.name;
DELETE FROM t2;
DELETE FROM t1;
INSERT INTO t2 SELECT * FROM tt;
DROP TABLE tt;
COMMIT;';

pg_exec($query);

Значи ето ти даваш един възможен отговор

Номера е да си зададеш въпроса първо и после да потърсиш възможните отговори, после да прецениш кой от отговорите сасви предимства и недостатъци има и чак тогава да си харесаш някой от тях

phpGuru знам, че ти си наясно със всичко това. Ама аз не питах теб а Petrov нали?

Съдържаниет е скрито
Влезте за да го видите

ако ползваш по-нова версия на php, което е препоръчително - ползвай pg_query, а не pg_exec (това ще изчезне скоро от php-то)

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

а и варианта с рула и функцията ми се струва все по-елегантен май ;-))
особено ако сложиш UNIQUE индекс по полето name на таблицата t2

незнам твоята програма дето пълни t1 кога пълни, ама ти ще трябва да правиш онова нещо всеки път след нея (там begin;create temp table tt .....)

ако ползваш варианта с рула - само го създаваш и спираш да се грижиш за това кога ти идват данни и т.н. - те просто сами се подреждат при това ако имаш тоз уникален индекс върху t1, няма да има почти никакво забавяне при вкарването


(незнам дали обясних добре, но вече писах, че принципно не съм добър с обясненията)

в общи линии (има и други, ама това са важните )

1. предимства с една таблица и варианта програмата само да си инсертва, а като ни требат данни да правим select ... group by ...

- лесно (в смисъл нема писане на нищо допълнително)
- по-бързо вмъкване на данните

недостатъци
- по-бавно извличане на данните

2. предимства на варианта с 2 таблици (по-специално на този с рула и функцията)

- по-бързо при извличане на данните

недостатъци
- по-бавно вмъкване на данните

=> при по-често вмъкване и по-рядко гледане - вариант 1.; при по-често гледане и по-рядко вмъкване - вариант 2; при нещо средно ..... преценявайте

(честно казано варианта с триене на таблицата не си представям как ще се извиква и т.н. щото нема достатъчно инфо)

(дали е по-полезно , а дано)
(еййй, когато няма кво да правиш е много готско )

пак съм аз

вариант 2 се държи по-добре при нарастване на данните

защото при insert функцията ще проверява по уникален индекс, което си е бързичко, а group by-а ще сумира по не уникален индекс

И то много зависи от много неща.

А за едно нещо хич биля не си прав.

INSERT винаги е по-бърз когато няма никакви индекси и се бави пропорционално на броя на индексите а не на типа им.


Та честичко за такива таблици дето съдържат срова информация (log-ове примерно) се оказва най-хитро да погазиш цялата теория и да ги напрариш без нито един индекс
Когато примерно цял ден трупаш по 100-200 реда в секунда и само веднъж накрая на деня правиш една статистика и забърсваш ....

Ако обаче имащ по 1 реда на 5 минути тогава по добре да си пазиш всичко в една таблица.

Та въпросът си стои



А дали GROUP BY може да използва индекси е много тъмна Индия и зависи предимно от реализацията. В повечето случаи няма смисъл от индекс щото тъй или иначе се преравя цалята таблица а обикновено агрегирането се прави по неиндексирани колони.

Съдържаниет е скрито
Влезте за да го видите

за индексите, които имах предвид, че се ползват при вторият вариант са тези, които се ползват във функцията от UPDATE t2 SET value = value + t1_value WHERE name = t1_name; която подържа таблицата с вече обработената информация

та тези индекси се използват и то пълноценно, а иначе самият insert е друго (той, както казах може даже да не се изпълнява, чак ако тоз update не промени нищо ще инсертне нов запис, и то пак в тази таблица t2

(е тва си , моите обяснения - даже и сам трудно ги разбирам)

идеата е, че при нарастване на t2 този update няма да си забави скоростта, а за group by при нарастване на данните ще си забави скоростта

а ето и

CREATE TABLE t1 (
name varchar(64) NOT NULL,
value int4 NOT NULL
);

(вкарваме малко данни)

EXPLAIN SELECT name, sum(value) FROM t1 GROUP BY name;

Aggregate (cost=16.94..19.13 rows=29 width=54)
-> Group (cost=16.94..18.40 rows=293 width=54)
-> Sort (cost=16.94..17.67 rows=293 width=54)
Sort Key: name
-> Seq Scan on t1 (cost=0.00..4.93 rows=293 width=54)

CREATE INDEX t1_name ON t1 USING btree (name);

EXPLAIN SELECT name, sum(value) FROM t1 GROUP BY name;

Aggregate (cost=0.00..14.69 rows=29 width=54)
-> Group (cost=0.00..13.96 rows=293 width=54)
-> Index Scan using t1_name on t1 (cost=0.00..13.22 rows=293 width=54)


така, че в случая group by си ползва индекси, но въпреки това аз казвам, че варианта с t2 и руловете ще се държи по-добре