BUG #4418: Memory leak in query planner

The following bug has been logged online:

Bug reference:      4418
Logged by:          michael McMaster
Email address:      Michael.McMaster@gmail.com
PostgreSQL version: 8.3.3
Operating system:   Ubuntu Hardy 8.04.1
Description:        Memory leak in query planner
Details:

I intend on creating a view over a large number of tables (possibly > 1000).
 Each table in the view has a CHECK constraint that partitions the data by a
time range.

I've created some test data with 2 views, with each view a UNION ALL over
1000 tables, with each table containing 100 rows.  An INT8 type is used as a
primary key in both tables, with one table additionally containing the INT8
key of the other as a foreign key.

eg. Table A is created with
CREATE TABLE A (
key INT8 primary key,
dtstart INT8, -- top 32 bits seconds, lower 32 bits microseconds
CHECK(dtstart >= <some value> AND dtstart < <some other value>)
); -- Create 1000 of these tables
CREATE VIEW A_View as SELECT * from A_1 UNION ALL select * from A_2 ...
UNION ALL select * from A_N;

(Table B is created in a similar manner)

I have set constraint_exclusion on to limit the number of tables scanned in
each query.  I have confirmed that constraint_exclusion has the effect of
limiting a query to a single table in the view based on the dtstart column.

The problem is the query planner seems to use a large amount of memory, and
this memory is not released until the client connection goes away.  I've
tested this with both a perl dbi client and psql - in both cases the
postgres process can consume > 1Gb of ram.

I can trigger the bug by starting up a psql session, and executing an
"explain" command.  The postgres process will then use large amounts of
memory until psql is terminated.

eg.
explain SELECT e.*, a.annotation FROM History.EventView e,
History.AnnotationView a WHERE e.dtstart between 429496729600000 and
444958611865600 AND a.dtstart between 429496729600000 and 444958611865600
AND a.dtstart = e.dtstart AND a.entryId = e.eventId;
                                                              QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------
 Hash Join  (cost=5.00..10.26 rows=1 width=253)
   Hash Cond: ((event_100000.dtstart = event_annotation_100000.dtstart) AND
(event_100000.eventid = event_annotation_100000.entryid))
   ->  Append  (cost=0.00..4.50 rows=100 width=201)
         ->  Seq Scan on event_100000  (cost=0.00..4.50 rows=100 width=201)
               Filter: ((dtstart >= 429496729600000::bigint) AND (dtstart <=
444958611865600::bigint))
   ->  Hash  (cost=3.50..3.50 rows=100 width=68)
         ->  Append  (cost=0.00..3.50 rows=100 width=68)
               ->  Seq Scan on event_annotation_100000  (cost=0.00..3.50
rows=100 width=68)
                     Filter: ((dtstart >= 429496729600000::bigint) AND
(dtstart <= 444958611865600::bigint))
(9 rows)

It's not necessary to actually perform the query in order to waste
resources.  Actually, when I perform the query in a psql session, the memory
usage is still high, but released as soon as the query results are
available.  If I then perform the explain (ie. explain after the select in
the same psql session), the memory is also released correctly (but I still
think the 1Gb of memory to return 100 small rows with a single join is
excessive).


Executing the explain query in the same psql session twice doesn't use any
more memory than executing it once.