Memoty leak (over 73.5 % of 24Gb, very slow planning) when Update/delete partitioning table (BUG?, why not ?)

[mak...@gmail.com]

Hello.
My db has 3kkk record in ~3-4k tables.
Select's queries fast, but delete/update - slow, or sometime server crash.....(swap out)

You can replay it with small test WITHOUT records:
(~5k tables)
simple structure:
CREATE TABLE childtable_1 (CONSTRAINT childtable_1_check CHECK (value>=100 AND value <=199)) INHERITS (public.roottable);
CREATE TABLE childtable_N (CONSTRAINT childtable_2_check CHECK (value>=200*n AND value <=299*n)) INHERITS (public.roottable);

Please, use this (https://www.dropbox.com/s/ghss9h9fkbcrn1s/dump_replay.sql)
sql file for create tables.

My test:
postgresq.conf - default
Or tuned (has similar results):
shared_buffers = 16384MB
sort_mem = 128MB
temp_buffers = 64MB
wal_buffers = 64MB
work_mem = 512MB
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0055
cpu_tuple_cost = 0.001
datestyle = 'iso, mdy'
default_statistics_target = 1000
default_text_search_config = 'pg_catalog.english'
effective_cache_size = 16384MB
effective_io_concurrency = 4
full_page_writes = off

OS: FreeBSD uniqtravel.s1 9.2-RELEASE FreeBSD 9.2-RELEASE #0 r255898: Thu Sep 26 22:50:31 UTC 2013 ro...@bake.isc.freebsd.org:/usr/obj/usr/src/sys/GENERIC amd64
Pg server ver: psql (PostgreSQL) 9.3.4


DELETE: used 73.5 % of 24Gb memory...
sql# explain delete from roottable where value=100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Delete on roottable (cost=0.00..17.49 rows=12 width=6) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on roottable (cost=0.00..0.00 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (value = 100)
-> Seq Scan on childtable_1 (cost=0.00..17.49 rows=11 width=6) (actual time=0.000..0.000 rows=0 loops=1)
Filter: (value = 100)
Total runtime: 0.118 ms
(6 lines)

time:18 sec;


pg_activity:
CPU% MEM% READ/s WRITE/s TIME+ W IOW Query
98.4 73.5 0.00B 0.00B 00:30.87 N N explain delete from roottable where value=100;


But SELECT: work fast and fine!
sql# explain select * from roottable where value=100;
QUERY PLAN
--------------------------------------------------------------------
Append (cost=0.00..17.49 rows=12 width=8)
-> Seq Scan on roottable (cost=0.00..0.00 rows=1 width=8)
Filter: (value = 100)
-> Seq Scan on childtable_1 (cost=0.00..17.49 rows=11 width=8)
Filter: (value = 100)
(5 lines)

time:0.04 sec;

Similar results: pg 9.2, 9.1 - os Ubuntu 13.10, windows...

for drop tables, use:
DROP table roottable cascade;
DROP SCHEMA childs0;
DROP SCHEMA childs1;
DROP SCHEMA childs2;
DROP SCHEMA childs3;
DROP SCHEMA childs4;

Thank you!