"HashAggregate (cost=1807.60..1807.62 rows=1 width=31)"
" -> Nested Loop (cost=157.96..1806.29 rows=131 width=31)"
" -> Nested Loop (cost=145.60..252.16 rows=1 width=23)"
" -> Nested Loop Left Join (cost=145.60..243.52 rows=1 width=15)"
" -> Bitmap Heap Scan on accanl (cost=145.60..235.24 rows=1 width=16)"
" Recheck Cond: ((accanl_fund = 56537) AND ((accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[])) OR (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))))"
" Filter: (((dateclose >= '2007-06-18'::date) AND (dateclose <= '2007-06-24'::date) AND (dateopen <= '2007-06-24'::date)) OR (dateclose IS NULL))"
" -> BitmapAnd (cost=145.60..145.60 rows=23 width=0)"
" -> Bitmap Index Scan on idx32 (cost=0.00..11.22 rows=395 width=0)"
" Index Cond: (accanl_fund = 56537)"
" -> BitmapOr (cost=134.12..134.12 rows=4675 width=0)"
" -> Bitmap Index Scan on idx30 (cost=0.00..78.19 rows=2721 width=0)"
" Index Cond: (accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[]))"
" -> Bitmap Index Scan on idx31 (cost=0.00..55.93 rows=1954 width=0)"
" Index Cond: (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))"
" -> Index Scan using pkidx20 on locbranch (cost=0.00..8.27 rows=1 width=15)"
" Index Cond: (accanl.idlocbranch = locbranch.id)"
" -> Index Scan using idx_dia_1 on fvaluedia (cost=0.00..8.64 rows=1 width=8)"
" Index Cond: (fvaluedia.idrow = accanl.id)"
" Filter: ((idfeature = 749630) AND ((fvalue)::text <> ALL (('{Накопительный,"Счет КБК"}'::character varying[])::text[])) AND (idfeature IS NOT NULL))"
" -> Bitmap Heap Scan on restanl (cost=12.37..1549.12 rows=400 width=24)"
" Recheck Cond: (restanl.idaccanl = accanl.id)"
" Filter: ((restanl_date >= '2006-12-24'::date) AND (restanl_date <= '2007-06-24'::date))"
" -> Bitmap Index Scan on idx12 (cost=0.00..12.34 rows=400 width=0)"
" Index Cond: (restanl.idaccanl = accanl.id)"

Уважаемые знатоки - кто видит без бинокля, где тормоза?

А это сам запрос

select charcod,sum(abs(turndeb)) as td,sum(abs(turncre)) as tc
from
restanl inner join
accanl on (restanl.idaccanl=accanl.id)
left join fvaluedia on (fvaluedia.idrow=accanl.id and fvaluedia.idfeature=749630 and fvaluedia.fvalue not in ('Накопительный','Счет КБК') )
left join locbranch on (accanl.idlocbranch=locbranch.id)
where
(restanl_date >='2006-12-24' and restanl_date <='2007-06-24') and
accanl.accanl_fund=56537 and
((accanl.dateclose>='2007-06-18' and accanl.dateclose<='2007-06-24' and accanl.dateopen<='2007-06-24') or accanl.dateclose is null ) and
((accanl.accsin1 in ('401','402','403','404','405','406','407')) or (accanl.accsin2 in ('40802','40804','40805','40806','40807'))) and
idfeature is not null
group by
accanl.id,charcod

Explain analyse делается до сих пор.

Может что с настройками сервера не то? Посмотрите пожалуйста!

ОЗУ 2ГБ, Intel Pentium Xeon 5110 1.6 Ghz

"PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"

listen_addresses = '*'
max_connections = 50
shared_buffers = 256MB
max_fsm_pages = 204800
wal_buffers = 256kB
checkpoint_segments = 6
effective_cache_size = 512MB
datestyle = 'iso, dmy'
client_encoding = win1251
lc_messages = 'Russian_Russia.20866'
lc_monetary = 'Russian_Russia.20866'
lc_numeric = 'Russian_Russia.20866'
lc_time = 'Russian_Russia.20866'

остальное по дефаулту

pg_config

BINDIR = D:/POSTGR~1/bin
DOCDIR = D:/POSTGR~1/doc
INCLUDEDIR = D:/PostgreSQL/include
PKGINCLUDEDIR = D:/PostgreSQL/include
INCLUDEDIR-SERVER = D:/PostgreSQL/include/server
LIBDIR = D:/POSTGR~1/lib
PKGLIBDIR = D:/POSTGR~1/lib
LOCALEDIR = D:/PostgreSQL/share/locale
MANDIR = D:/PostgreSQL/man
SHAREDIR = D:/POSTGR~1/share
SYSCONFDIR = D:/PostgreSQL/etc
PGXS = D:/PostgreSQL/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-openssl' '--with-perl' '--with-tcl' '--with-python' '--with-ldap' '--enable-nls' '--enable-thread-sa
fety' '--with-krb5' '--with-includes=/mingw/include/krb5'
CC = gcc
CPPFLAGS = -I./src/include/port/win32 -DEXEC_BACKEND -I/mingw/include/krb5 -I../../../src/include/port/win32
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-stric
t-aliasing
CFLAGS_SL =
LDFLAGS = -Wl,--allow-multiple-definition
LDFLAGS_SL =
LIBS = -lpgport -lintl -lssleay32 -leay32 -lcomerr32 -lkrb5_32 -lz -lm -lws2_32 -lshfolder
VERSION = PostgreSQL 8.2.4

VACUUM делал непосредственно перед запросом, со всеми опциями и по всемм таблицам по отдельности. А чем плоха 8? В пятницу таже версия работала вполне прилично, но после переинициализации кластера и перезаливки данных началась эта вот беда.

Решил индексы перестроить по самой большой таблице - ~9млн. записей. Что можно еще попробовать?

Как ни странно - вроде бы помогло. И explain analyse быстро сделался ...
В чем дело было?

"HashAggregate (cost=1807.51..1807.53 rows=1 width=31) (actual time=63507.914..63510.484 rows=2559 loops=1)"
" -> Nested Loop (cost=157.87..1806.20 rows=131 width=31) (actual time=1363.869..62555.970 rows=335930 loops=1)"
" -> Nested Loop (cost=145.60..252.16 rows=1 width=23) (actual time=1273.143..22599.875 rows=2559 loops=1)"
" -> Nested Loop Left Join (cost=145.60..243.52 rows=1 width=15) (actual time=1242.048..18038.653 rows=2588 loops=1)"
" -> Bitmap Heap Scan on accanl (cost=145.60..235.24 rows=1 width=16) (actual time=1242.036..17961.645 rows=2588 loops=1)"
" Recheck Cond: ((accanl_fund = 56537) AND ((accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[])) OR (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))))"
" Filter: (((dateclose >= '2007-06-18'::date) AND (dateclose <= '2007-06-24'::date) AND (dateopen <= '2007-06-24'::date)) OR (dateclose IS NULL))"
" -> BitmapAnd (cost=145.60..145.60 rows=23 width=0) (actual time=1201.788..1201.788 rows=0 loops=1)"
" -> Bitmap Index Scan on idx32 (cost=0.00..11.22 rows=395 width=0) (actual time=674.870..674.870 rows=60088 loops=1)"
" Index Cond: (accanl_fund = 56537)"
" -> BitmapOr (cost=134.12..134.12 rows=4675 width=0) (actual time=523.612..523.612 rows=0 loops=1)"
" -> Bitmap Index Scan on idx30 (cost=0.00..78.19 rows=2721 width=0) (actual time=288.570..288.570 rows=3964 loops=1)"
" Index Cond: (accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[]))"
" -> Bitmap Index Scan on idx31 (cost=0.00..55.93 rows=1954 width=0) (actual time=235.035..235.035 rows=1382 loops=1)"
" Index Cond: (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))"
" -> Index Scan using pkidx20 on locbranch (cost=0.00..8.27 rows=1 width=15) (actual time=0.022..0.023 rows=1 loops=2588)"
" Index Cond: (accanl.idlocbranch = locbranch.id)"
" -> Index Scan using idx_dia_1 on fvaluedia (cost=0.00..8.64 rows=1 width=8) (actual time=1.422..1.759 rows=1 loops=2588)"
" Index Cond: (fvaluedia.idrow = accanl.id)"
" Filter: ((idfeature = 749630) AND ((fvalue)::text <> ALL (('{Накопительный,"Счет КБК"}'::character varying[])::text[])) AND (idfeature IS NOT NULL))"
" -> Bitmap Heap Scan on restanl (cost=12.28..1549.03 rows=400 width=24) (actual time=8.325..15.473 rows=131 loops=2559)"
" Recheck Cond: (restanl.idaccanl = accanl.id)"
" Filter: ((restanl_date >= '2006-12-24'::date) AND (restanl_date <= '2007-06-24'::date))"
" -> Bitmap Index Scan on idx12 (cost=0.00..12.25 rows=400 width=0) (actual time=6.814..6.814 rows=454 loops=2559)"
" Index Cond: (restanl.idaccanl = accanl.id)"
"Total runtime: 63511.947 ms"

Ничего не понимаю - опять на том же запросе тормоза! Что делать? Где копать?

INFO: vacuuming "public.restanl"
INFO: "restanl": found 0 removable, 9084306 nonremovable row versions in 120132 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 92 to 144 bytes long.
There were 3625 unused item pointers.
Total free space (including removable row versions) is 5976812 bytes.
0 pages are or will become empty, including 0 at the end of the table.
14504 pages containing 1433892 free bytes are potential move destinations.
CPU 2.09s/0.96u sec elapsed 151.65 sec.
INFO: index "idx12" now contains 9084306 row versions in 24911 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.43s/2.32u sec elapsed 17.98 sec.
INFO: index "idx13" now contains 9084306 row versions in 24911 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.59s/2.57u sec elapsed 20.65 sec.
INFO: "restanl": moved 0 row versions, truncated 120132 to 120132 pages
DETAIL: CPU 0.06s/0.17u sec elapsed 4.65 sec.
INFO: analyzing "public.restanl"
INFO: "restanl": scanned 3000 of 120132 pages, containing 226845 live rows and 0 dead rows; 3000 rows in sample, 9083781 estimated total rows

Total query runtime: 195294 ms.

Сделал VACUUM FULL вроде опять нормально работает. Как часто надо вакуумить?

Действительно - после VACUUM ANALYSE на все таблицы, время выполнения запросов нормализовалось. Увеличил, как советовал Lexa NailBat default_statistics_target=100 (было 10) и
тепер EXPLAIN ANALYSE выглядит так:

"HashAggregate (cost=220558.29..220757.49 rows=9960 width=31) (actual time=5546.835..5549.301 rows=2559 loops=1)"
" -> Hash Join (cost=48503.37..219759.84 rows=79845 width=31) (actual time=876.900..4610.365 rows=335930 loops=1)"
" Hash Cond: (restanl.idaccanl = accanl.id)"
" -> Bitmap Heap Scan on restanl (cost=39497.59..197495.42 rows=2507789 width=24) (actual time=673.133..2830.067 rows=2454083 loops=1)"
" Recheck Cond: ((restanl_date >= '2006-12-24'::date) AND (restanl_date <= '2007-06-24'::date))"
" -> Bitmap Index Scan on idx13 (cost=0.00..38870.64 rows=2507789 width=0) (actual time=659.848..659.848 rows=2454083 loops=1)"
" Index Cond: ((restanl_date >= '2006-12-24'::date) AND (restanl_date <= '2007-06-24'::date))"
" -> Hash (cost=8985.11..8985.11 rows=1654 width=23) (actual time=203.737..203.737 rows=2559 loops=1)"
" -> Hash Left Join (cost=6518.51..8985.11 rows=1654 width=23) (actual time=88.428..201.204 rows=2559 loops=1)"
" Hash Cond: (accanl.idlocbranch = locbranch.id)"
" -> Hash Join (cost=6512.10..8972.02 rows=1654 width=24) (actual time=88.261..197.781 rows=2559 loops=1)"
" Hash Cond: (fvaluedia.idrow = accanl.id)"
" -> Bitmap Heap Scan on fvaluedia (cost=766.75..2923.08 rows=57410 width=8) (actual time=14.131..91.067 rows=56636 loops=1)"
" Recheck Cond: (idfeature = 749630)"
" Filter: ((fvalue)::text <> ALL (('{Накопительный,"Счет КБК"}'::character varying[])::text[]))"
" -> Bitmap Index Scan on idx_dia_3 (cost=0.00..752.40 rows=57619 width=0) (actual time=13.754..13.754 rows=56981 loops=1)"
" Index Cond: (idfeature = 749630)"
" -> Hash (cost=5724.59..5724.59 rows=1660 width=16) (actual time=74.103..74.103 rows=2588 loops=1)"
" -> Bitmap Heap Scan on accanl (cost=121.26..5724.59 rows=1660 width=16) (actual time=3.530..71.210 rows=2588 loops=1)"
" Recheck Cond: ((accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[])) OR (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[])))"
" Filter: ((accanl_fund = 56537) AND (((dateclose >= '2007-06-18'::date) AND (dateclose <= '2007-06-24'::date) AND (dateopen <= '2007-06-24'::date)) OR (dateclose IS NULL)))"
" -> BitmapOr (cost=121.26..121.26 rows=5788 width=0) (actual time=2.609..2.609 rows=0 loops=1)"
" -> Bitmap Index Scan on idx30 (cost=0.00..91.52 rows=4768 width=0) (actual time=1.806..1.806 rows=3313 loops=1)"
" Index Cond: (accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[]))"
" -> Bitmap Index Scan on idx31 (cost=0.00..28.91 rows=1021 width=0) (actual time=0.800..0.800 rows=1110 loops=1)"
" Index Cond: (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))"
" -> Hash (cost=6.34..6.34 rows=6 width=15) (actual time=0.137..0.137 rows=6 loops=1)"
" -> Index Scan using pkidx20 on locbranch (cost=0.00..6.34 rows=6 width=15) (actual time=0.121..0.127 rows=6 loops=1)"
"Total runtime: 5551.442 ms"

И еще вопрос до кучи - стоит ли использовать autovacuum=on или же лучше поставить в ежедневную задачу VACUUM ANALYSE ночью? Особенностью базы является то, что большинство таблиц удаляются, затем создаются вновь, заливаются данными через COPY, затем строятся индексы. Таблицы по 3-12млн. активно обновляются в течение дня, идет удаление 18-50 тысяч записей и затем через COPY заливаются обратно. Какой вариант мне подходит больше?

assa...провести analyze именно этой таблицы... Так я и сделаю. А autovacuum все же отключать или оставить в on? Обновлениями данных в базе занимается только 1 процесс, остальные пользователи только читают (строят отчеты). Может заодно кто-нибудь посоветует какие параметры в postgresql.conf покрутить для оптимизации под тяжелые запросы для множества пользователей?

LeXa NalBat ... 36-100 тысяч строк для таблицы из 3-12 миллионов составляют 1 процент, то есть их изменение не является массированным обновлением таблицы, имхо поэтому vacuum, analyze после каждого такого изменения не обязательны ...
Обратил внимание на странную вещь - раньше, когда я вставлял записи при помощи INSERT, действительно эти обновления никак не отражались на выполнении запросов. Стоило мне поменять стратегию загрузки - сначала в текст, потом COPY в таблицу - даже 5 таких вставок по 18 тысяч записей приводят к тому, что EXPLAIN ANALYSE уходит в себя и не возвращается. После VACUUM ANALYSE все приходит в норму. Это особенность COPY?

Еще вопрос:

есть две таблицы

CREATE TABLE accanl
(
id bigint,
idclient bigint,
acc_char character(1),
accanl_pic character(25),
accsin1 character(3),
accsin2 character(5),
accanl_area bigint,
accanl_branch bigint,
accanl_fund bigint,
dateopen date,
dateclose date,
datechange date,
datefirstpay date,
accanl_name character(160),
idlocbranch bigint,
note character(640),
accsort character(25)
)
WITHOUT OIDS;

57679 записей и

CREATE TABLE linkdia
(
f_parentid bigint,
f_childid bigint,
f_selector bigint,
f_linkid bigint
)
WITHOUT OIDS;

153722 записи

explain analyse
update
accanl
set idlocbranch=(select f_childid from linkdia where f_parentid=accanl.id and f_selector=1 and f_linkid=3)


"Seq Scan on accanl (cost=100000000.00..101861149.41 rows=115019 width=3526) (actual time=0.076..541.220 rows=57679 loops=1)"
" SubPlan"
" -> Index Scan using part_idx_1 on linkdia (cost=0.00..16.03 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=57679)"
" Index Cond: (f_parentid = $0)"
"Total runtime: 216345.747 ms"

Что-то мне подсказывает, что так долго выполняться не должно. Запрос дурной?

запрос переписал, но все равно както долго мне кажется:

explain analyse
update accanl set idlocbranch=(select f_childid from linkdia where f_parentid=accanl.id and f_selector=1 and f_linkid=3) where accanl.id in (select f_parentid from linkdia where (f_selector=1 and f_linkid=3))


"Nested Loop (cost=293.61..1307.01 rows=114 width=987) (actual time=1927.222..29509.066 rows=1584 loops=1)"
" -> HashAggregate (cost=293.61..295.12 rows=151 width=8) (actual time=7.663..10.071 rows=1584 loops=1)"
" -> Index Scan using part_idx_1 on linkdia (cost=0.00..292.85 rows=302 width=8) (actual time=0.068..6.062 rows=1584 loops=1)"
" -> Index Scan using idx1 on accanl (cost=0.00..3.46 rows=1 width=987) (actual time=18.601..18.605 rows=1 loops=1584)"
" Index Cond: (accanl.id = linkdia.f_parentid)"
" SubPlan"
" -> Index Scan using part_idx_1 on linkdia (cost=0.00..4.28 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1584)"
" Index Cond: (f_parentid = $0)"
"Total runtime: 38507.027 ms"

MBG...Еще проверяй, сколько места занимают индексы ... Ну допустим проверил я сколько у меня места индексы занимают - как это мне может помочь?

Вот статистика по самой большой таблице

SELECT relname AS name, relfilenode AS oid, relpages as size_in_pages , reltuples::numeric as count
FROM pg_class
WHERE relname NOT LIKE 'pg%'
ORDER BY relpages DESC;
name oid size_in_pages reltuples
"docblc" 16714 700088 3801970
"didx1" 25917 12075 3801970
"didx2" 25921 12946 3801970
"didx3" 25922 29130 3801970
"didx4" 25923 12737 3801970
"didx5" 25924 12647 3801970

И еще - нашел вот такой совет:

"Иногда, особенно для интенсивно обновляемых таблиц, начинают сыпаться deadlock'и и VACUUM
выполняется нереально долгое время. Часто проблема из-за поврежденного индекса. Решение:
REINDEX TABLE таблица;
REINDEX INDEX индекс;"

я всегда думал, что REINDEX TABLE предполагает, что индексы к этой таблице так же переиндексируются - это не так?

Попробую внести ясность.
База является отражением набора таблиц системы Diasoft Bank 4x4 (если кто знает). Данные в таблицах обновляются 2 раза в день (иногда чаще). После некоторых экспериментов пришел к выводу, что самое быстрое - делать DROP TABLE, затем CREATE TABLE и COPY из файла. Однако не для всех таблиц - таблицу остатков по счетам за 6 лет работы не загрузишь за 10 минут. Поэтому по огромным таблицам идет то самое удаление и вставка. А вот таблица accanl как раз таки пересоздается каждый раз, поэтому в чем тормоза - непонятно. Хотя после включения в код VACUUM FULL ANALYSE accanl (выполняется 10,5 секунд) все вроде бы устаканилось.

С большим интересом прочитал про разделенные таблицы. Если я разобъю свои большие таблицы, то до какой степени детализации это будет эффективно? Т.е. порядок частей таблицы не влияет на быстродействие? Или частить надо, но без фанатизма?

Допустим побил я таблицу по месяцам, а у меня запрос обращается к данным на стыке месяцев или за полугодие - в этом случае партионность также эффективна?

Большое спасибо всем кто поучаствовал в обсуждении моих проблем! Обязательно сообщу после перезаливки данных прирост производительности.

нужен совет - в субботу-воскресение буду перезаливать данные по большим таблицам с разделением по месяцам. Месяцев будет 41 на каждую таблицу. План такой - создаю временную таблицу name_table_ b r к ней 41 со всеми правилами и так для всех больших таблиц. Заливаю данные и после проверки "что_все_работает", переименовываю родительские таблицы в name_table. Следует ли для каждой таблицы пересоздавать\изменять правила или при переименовании родительской таблицы PG сам сделает все что нужно?

И еще вопрос - что быстрее для операции SELECT?
Составной индекс

CREATE INDEX rest_idx_3 ON restdepcrd USING btree (rest_date, accanl_pic);

или 2 индекса

CREATE INDEX rest_idx_3_1 ON restdepcrd USING btree (rest_date);
CREATE INDEX rest_idx_3_2 ON restdepcrd USING btree (accanl_pic);