Производительность Postgres !!! Неужели такой тормозной ???

altair_andy
Дата: 19.10.2004 20:15:04
Процессор 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;
Sad Spirit
Дата: 19.10.2004 23:00:20
altair_andy

На MS SQL 2000 это же раз в 6-8 быстрее !!!

Эээ... А на MS SQL 2000 у тебя тоже ни одного индекса нету?
sibircev
Дата: 20.10.2004 10:50:32
Проблемы с быстродействием действительно есть (особенно без индексов), но существуют мануалы по оптимезации работы.
Niemi
Дата: 20.10.2004 14:08:09
по-моему где-то тут у нас давали хорошую сулку на FAQ, в котором и были опиманы прелести INDEX.
altair_andy
Дата: 21.10.2004 11:25:16
Индексы есть по всем FOREIGN KEYS !!! + по полям "buh" и "dt" !!!
altair_andy
Дата: 21.10.2004 11:32:29
set enable_seqscan to false; -- !!!
explain analyze select bal, acc, anal, sum(sum_dt), sum(sum_kt)
from v_entries
group by bal, acc, anal; -- расчет текущего баланса

И все-равно почти не хочет использовать индексы.
А где использует, там делает MERGE JOIN, который медленнее чем HASH.

"GroupAggregate (cost=1400018906.19..1400020443.51 rows=76866 width=74) (actual time=11592.013..12705.321 rows=633 loops=1)"
" -> Sort (cost=1400018906.19..1400019098.36 rows=76866 width=74) (actual time=11591.852..11810.550 rows=156825 loops=1)"
" Sort Key: bal, acc, anal"
" -> Subquery Scan v_entries (cost=500000035.51..1400012668.49 rows=76866 width=74) (actual time=146.834..8741.184 rows=156825 loops=1)"
" -> Append (cost=500000035.51..1400011131.17 rows=76866 width=52) (actual time=146.824..7785.732 rows=156825 loops=1)"
" -> Subquery Scan "*SELECT* 1" (cost=500000035.51..500003270.87 rows=25622 width=41) (actual time=146.821..1483.162 rows=58564 loops=1)"
" -> Seq Scan on transact t (cost=500000035.51..500002758.43 rows=25622 width=41) (actual time=146.803..1115.558 rows=58564 loops=1)"
" Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed subplan) AND (hashed subplan)))"
" SubPlan"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (actual time=4.219..92.999 rows=332 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (actual time=11.057..44.416 rows=123 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Subquery Scan "*SELECT* 2" (cost=500000035.51..500003270.87 rows=25622 width=41) (actual time=126.697..2113.425 rows=58564 loops=1)"
" -> Seq Scan on transact t (cost=500000035.51..500002758.43 rows=25622 width=41) (actual time=126.677..1544.315 rows=58564 loops=1)"
" Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed subplan) AND (hashed subplan)))"
" SubPlan"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (actual time=1.305..90.115 rows=332 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (actual time=1.165..33.086 rows=123 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Subquery Scan "*SELECT* 3" (cost=400000035.51..400004589.43 rows=25622 width=52) (actual time=211.886..3910.960 rows=39697 loops=1)"
" -> Merge Join (cost=400000035.51..400004076.99 rows=25622 width=52) (actual time=211.873..3555.877 rows=39697 loops=1)"
" Merge Cond: ("outer".id = "inner".delta)"
" Join Filter: ((("inner".sdebet)::numeric <> ("inner".skredit)::numeric) OR ((("outer".accid)::text)::numeric <> 0::numeric))"
" -> Index Scan using pk_acounts on accounts a (cost=0.00..9.49 rows=123 width=13) (actual time=0.884..2.493 rows=120 loops=1)"
" Filter: ((((accid)::text)::numeric = 0::numeric) OR (((accid)::text)::numeric <> 0::numeric))"
" -> Index Scan using fki_transact_delta on transact t (cost=400000035.51..400003298.61 rows=25622 width=52) (actual time=198.620..2471.554 rows=58564 loops=1)"
" Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed subplan) AND (hashed subplan)))"
" SubPlan"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (actual time=1.733..123.978 rows=332 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (actual time=1.728..46.029 rows=123 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
"Total runtime: 12719.589 ms"
altair_andy
Дата: 21.10.2004 11:36:33
set enable_seqscan to false; -- !!!
explain analyze select bal, acc, anal, sum(sum_dt), sum(sum_kt)
from v_entries
group by bal, acc, anal; -- расчет текущего баланса

И все-равно почти не хочет использовать индексы.
А где использует, там делает MERGE JOIN, который медленнее чем HASH.

"GroupAggregate (cost=1400018906.19..1400020443.51 rows=76866 width=74) (actual time=11592.013..12705.321 rows=633 loops=1)"
" -> Sort (cost=1400018906.19..1400019098.36 rows=76866 width=74) (actual time=11591.852..11810.550 rows=156825 loops=1)"
" Sort Key: bal, acc, anal"
" -> Subquery Scan v_entries (cost=500000035.51..1400012668.49 rows=76866 width=74) (actual time=146.834..8741.184 rows=156825 loops=1)"
" -> Append (cost=500000035.51..1400011131.17 rows=76866 width=52) (actual time=146.824..7785.732 rows=156825 loops=1)"
" -> Subquery Scan "*SELECT* 1" (cost=500000035.51..500003270.87 rows=25622 width=41) (actual time=146.821..1483.162 rows=58564 loops=1)"
" -> Seq Scan on transact t (cost=500000035.51..500002758.43 rows=25622 width=41) (actual time=146.803..1115.558 rows=58564 loops=1)"
" Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed subplan) AND (hashed subplan)))"
" SubPlan"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (actual time=4.219..92.999 rows=332 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (actual time=11.057..44.416 rows=123 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Subquery Scan "*SELECT* 2" (cost=500000035.51..500003270.87 rows=25622 width=41) (actual time=126.697..2113.425 rows=58564 loops=1)"
" -> Seq Scan on transact t (cost=500000035.51..500002758.43 rows=25622 width=41) (actual time=126.677..1544.315 rows=58564 loops=1)"
" Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed subplan) AND (hashed subplan)))"
" SubPlan"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (actual time=1.305..90.115 rows=332 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (actual time=1.165..33.086 rows=123 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Subquery Scan "*SELECT* 3" (cost=400000035.51..400004589.43 rows=25622 width=52) (actual time=211.886..3910.960 rows=39697 loops=1)"
" -> Merge Join (cost=400000035.51..400004076.99 rows=25622 width=52) (actual time=211.873..3555.877 rows=39697 loops=1)"
" Merge Cond: ("outer".id = "inner".delta)"
" Join Filter: ((("inner".sdebet)::numeric <> ("inner".skredit)::numeric) OR ((("outer".accid)::text)::numeric <> 0::numeric))"
" -> Index Scan using pk_acounts on accounts a (cost=0.00..9.49 rows=123 width=13) (actual time=0.884..2.493 rows=120 loops=1)"
" Filter: ((((accid)::text)::numeric = 0::numeric) OR (((accid)::text)::numeric <> 0::numeric))"
" -> Index Scan using fki_transact_delta on transact t (cost=400000035.51..400003298.61 rows=25622 width=52) (actual time=198.620..2471.554 rows=58564 loops=1)"
" Filter: (((hashed subplan) AND (hashed subplan)) OR ((hashed subplan) AND (hashed subplan)))"
" SubPlan"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (never executed)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on analytics a (cost=100000000.00..100000012.30 rows=111 width=4) (actual time=1.733..123.978 rows=332 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
" -> Seq Scan on accounts a (cost=100000000.00..100000005.08 rows=41 width=4) (actual time=1.728..46.029 rows=123 loops=1)"
" Filter: is_sec_allow((bits)::integer)"
"Total runtime: 12719.589 ms"
LeXa NalBat
Дата: 21.10.2004 15:30:52
попробуйте заменить
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))
на
where exists ( select 1 from _accounts adt where adt.id=t.debet )
and exists ...

нужны индексы accounts(id), analytics(id) или даже accounts(id,is_sec_allow(a.bits::integer)), analytics(...)

я не разбираюсь в вашей предметной области, но 't3.delta = a.id' - это правильно?

без этих преобразований типов 'a.accid::text::numeric' не обойтись? может быть сделать 'a.accid<>0'?

мелочь: 'a.accid <> 0 or a.accid = 0 and t3.sdebet <> t3.skredit' эквивалентно 'a.accid <> 0 or t3.sdebet <> t3.skredit'

можете привести план выполнения аналогичного запроса в мсскл?
4321
Дата: 22.10.2004 17:03:24
маленкое уточнение:
altair_andy
Индексы есть по всем FOREIGN KEYS !!!

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

_______
Частые >>Filter: is_sec_allow((bits)::integer) - наводят на мысль о необходимости наличия функционального индекса, - если, конечно, ф-я реализована как IMMUTABLE

IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

Если же нет - от фильтра не отбрешетесь, и скорость его будет зависеть от того, STABLE ф-я, или VOLATILE.
Shweik
Дата: 22.10.2004 18:43:04
Напомните плз что это за типы данных:
d_money и d_buh?
И конечно план віполнения данного запроса в M$ SQL думаю прояснил бі ситуацию.
А вот форсированное использование индексов практически никогда
не давало положительных результатов - думаю эта опция осталась как ававизм..