|
Процессор P IV - 2,4
vacuum full analyze transact;
explain analyze select bal, acc, anal sum(sum_dt), sum(sum_kt)
from v_entries
group by bal, acc, anal; -- расчет текущего баланса
GroupAggregate (cost=21148.37..22685.69 rows=76866 width=74) (actual
time=9152.022..9910.720 rows=633 loops=1)
-> Sort (cost=21148.37..21340.53 rows=76866 width=74) (actual time=9151.880..9360.199
rows=156825 loops=1)
Sort Key: bal, acc, anal
-> Subquery Scan v_entries (cost=43.51..14910.67 rows=76866 width=74) (actual
time=322.709..6617.657 rows=156825 loops=1)
-> Append (cost=43.51..13373.35 rows=76866 width=52) (actual
time=322.698..5801.243 rows=156825 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=43.51..4177.87 rows=25622
width=41) (actual time=322.695..1631.239 rows=58564 loops=1)
-> Seq Scan on transact t (cost=43.51..3665.43 rows=25622
width=41) (actual time=322.676..1251.751 rows=58564 loops=1)
Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed
subplan) AND (hashed subplan)))
SubPlan
-> Seq Scan on analytics a (cost=0.00..15.30 rows=111
width=4) (never executed)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on accounts a (cost=0.00..6.08 rows=41
width=4) (never executed)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on analytics a (cost=0.00..15.30 rows=111
width=4) (actual time=10.242..95.830 rows=332 loops=1)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on accounts a (cost=0.00..6.08 rows=41
width=4) (actual time=16.043..46.776 rows=123 loops=1)
Filter: is_sec_allow((bits)::integer)
-> Subquery Scan "*SELECT* 2" (cost=43.51..4177.87 rows=25622
width=41) (actual time=133.921..1596.213 rows=58564 loops=1)
-> Seq Scan on transact t (cost=43.51..3665.43 rows=25622
width=41) (actual time=133.904..1179.919 rows=58564 loops=1)
Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed
subplan) AND (hashed subplan)))
SubPlan
-> Seq Scan on analytics a (cost=0.00..15.30 rows=111
width=4) (never executed)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on accounts a (cost=0.00..6.08 rows=41
width=4) (never executed)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on analytics a (cost=0.00..15.30 rows=111
width=4) (actual time=1.315..83.876 rows=332 loops=1)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on accounts a (cost=0.00..6.08 rows=41
width=4) (actual time=1.305..32.463 rows=123 loops=1)
Filter: is_sec_allow((bits)::integer)
-> Subquery Scan "*SELECT* 3" (cost=50.51..5017.61 rows=25622
width=52) (actual time=138.864..2295.415 rows=39697 loops=1)
-> Hash Join (cost=50.51..4505.17 rows=25622 width=52) (actual
time=138.853..1989.687 rows=39697 loops=1)
Hash Cond: ("outer".delta = "inner".id)
Join Filter: ((("outer".sdebet)::numeric <>
("outer".skredit)::numeric) OR ((("inner".accid)::text)::numeric <> 0::numeric))
-> Seq Scan on transact t (cost=43.51..3665.43 rows=25622
width=52) (actual time=137.483..1228.159 rows=58564 loops=1)
Filter: (((hashed subplan) AND (hashed subplan)) OR
((hashed subplan) AND (hashed subplan)))
SubPlan
-> Seq Scan on analytics a (cost=0.00..15.30
rows=111 width=4) (never executed)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on accounts a (cost=0.00..6.08 rows=41
width=4) (never executed)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on analytics a (cost=0.00..15.30
rows=111 width=4) (actual time=1.294..87.990 rows=332 loops=1)
Filter: is_sec_allow((bits)::integer)
-> Seq Scan on accounts a (cost=0.00..6.08 rows=41
width=4) (actual time=1.361..31.973 rows=123 loops=1)
Filter: is_sec_allow((bits)::integer)
-> Hash (cost=6.69..6.69 rows=123 width=13) (actual
time=1.247..1.247 rows=0 loops=1)
-> Seq Scan on accounts a (cost=0.00..6.69 rows=123
width=13) (actual time=0.063..1.014 rows=123 loops=1)
Filter: ((((accid)::text)::numeric = 0::numeric)
OR (((accid)::text)::numeric <> 0::numeric))
Total runtime: 9934.922 ms
На MS SQL 2000 это же раз в 6-8 быстрее !!!
Вырезка из базы данных:
CREATE TABLE transact
(
id serial NOT NULL,
dt timestamptz NOT NULL,
debet int4 NOT NULL,
ddebet int4 NOT NULL,
kredit int4 NOT NULL,
dkredit int4 NOT NULL,
delta int4 NOT NULL,
ddelta int4 NOT NULL,
drate float4 NOT NULL DEFAULT 1,
krate float4 NOT NULL DEFAULT 1,
crate float4 NOT NULL DEFAULT 1,
respite int2 NOT NULL DEFAULT 0,
bal int2 NOT NULL DEFAULT 1,
buh d_buh,
mat d_mat,
sdebet d_money,
skredit d_money,
scurr d_money,
num int4 DEFAULT 0,
currop char(3) DEFAULT 'USD'::bpchar,
CONSTRAINT pk_transact PRIMARY KEY (id),
CONSTRAINT fk_transact_balances FOREIGN KEY (bal) REFERENCES balances (id) ON UPDATE
CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_transact_ddebet FOREIGN KEY (ddebet) REFERENCES analytics (id) ON UPDATE
CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_transact_ddelta FOREIGN KEY (ddelta) REFERENCES analytics (id) ON UPDATE
CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_transact_debet FOREIGN KEY (debet) REFERENCES accounts (id) ON UPDATE
CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_transact_delta FOREIGN KEY (delta) REFERENCES accounts (id) ON UPDATE
CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_transact_dkredit FOREIGN KEY (dkredit) REFERENCES analytics (id) ON UPDATE
CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_transact_kredit FOREIGN KEY (kredit) REFERENCES accounts (id) ON UPDATE
CASCADE ON DELETE RESTRICT
)
WITHOUT OIDS;
CREATE OR REPLACE VIEW _analytics AS
SELECT a.id, a.name, a.city, a.bits, a.ant1, a.ant2, a.ant3, a.descr
FROM analytics a
WHERE is_sec_allow(a.bits::integer);
CREATE OR REPLACE VIEW _accounts AS
SELECT a.id, a.chart, a.accid, a.accname, a.acctype, a.isg, a.bits, a.ant1, a.ant2, a.ant3
FROM accounts a
WHERE is_sec_allow(a.bits::integer);
CREATE OR REPLACE VIEW _transact AS
SELECT t.id, t.dt, t.bal, t.buh, t.mat, t.num, t.debet, t.ddebet, t.sdebet, t.kredit,
t.dkredit, t.skredit, t.delta, t.ddelta, t.scurr, t.drate, t.krate, t.crate, t.respite,
t.currop
FROM transact t
WHERE (t.debet IN ( SELECT adt.id
FROM _accounts adt)) AND (t.ddebet IN ( SELECT ddt.id
FROM _analytics ddt)) OR (t.kredit IN ( SELECT akt.id
FROM _accounts akt)) AND (t.dkredit IN ( SELECT dkt.id
FROM _analytics dkt));
CREATE OR REPLACE VIEW v_entries AS
( SELECT t1.id, t1.dt, t1.num, t1.bal, t1.buh, t1.mat, t1.debet AS acc, t1.ddebet AS anal,
t1.sdebet AS sum_dt, 0 AS sum_kt
FROM _transact t1
UNION ALL
SELECT t2.id, t2.dt, t2.num, t2.bal, t2.buh, t2.mat, t2.kredit AS acc, t2.dkredit AS anal,
0 AS sum_dt, t2.skredit AS sum_kt
FROM _transact t2)
UNION ALL
SELECT t3.id, t3.dt, t3.num, t3.bal, t3.buh, t3.mat, t3.delta AS acc, t3.ddelta AS anal,
t3.skredit AS sum_dt, t3.sdebet AS sum_kt
FROM _transact t3
JOIN accounts a ON t3.delta = a.id
WHERE a.accid::text::numeric <> 0::numeric OR a.accid::text::numeric = 0::numeric AND
t3.sdebet::numeric <> t3.skredit::numeric;
|
