IndexJoin memory problem using spgist and boxes

[Anton Dignös]

Hi,

I came across a strange memory problem when doing an IndexJoin using
spgist on boxes.
I also found it mentioned here:
https://www.postgresql.org/message-id/flat/CAPqRbE5vTGWCGrOc91Bmu-0o7CwsU0UCnAshOtpDR8cSpSjy0g%40mail.gmail.com#capqrbe5vtgwcgroc91bmu-0o7cwsu0ucnashotpdr8cspsj...@mail.gmail.com

With the following setting:

CREATE TABLE r AS SELECT 1 i, box(point(generate_series,
generate_series), point(generate_series+10, generate_series+10)) FROM
generate_series(1, 1000000);
CREATE TABLE s AS SELECT 1 i, box(point(generate_series,
generate_series), point(generate_series+10, generate_series+10)) FROM
generate_series(1, 1000000) ORDER BY random(); -- random sort just
speeds up index creation
CREATE INDEX s_idx ON s USING spgist(box);

postgres consumes several GBs of main memory for the following query:

SELECT * FROM r, s WHERE r.box && s.box;

The problem also occurs for polygons which are based on boxes and are
now part of the dev version.

The attached patch should fix this problem by maintaining the
traversal memory per index scan instead of for the entire join.

Best regards,
Anton

diff --git a/src/backend/access/spgist/spgscan.c 
b/src/backend/access/spgist/spgscan.c
index 854032d..7e86cec 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -209,6 +209,9 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int 
nscankeys,
 {
        SpGistScanOpaque so = (SpGistScanOpaque) scan->opaque;
 
+       /* clear temp context before proceeding to the next scan */
+       MemoryContextReset(so->tempCxt);
+
        /* copy scankeys into local storage */
        if (scankey && scan->numberOfKeys > 0)
        {
@@ -463,7 +466,7 @@ redirect:
                        in.scankeys = so->keyData;
                        in.nkeys = so->numberOfKeys;
                        in.reconstructedValue = stackEntry->reconstructedValue;
-                       in.traversalMemoryContext = oldCtx;
+                       in.traversalMemoryContext = so->tempCxt;
                        in.traversalValue = stackEntry->traversalValue;
                        in.level = stackEntry->level;
                        in.returnData = so->want_itup;
@@ -547,8 +550,6 @@ redirect:
 
                /* done with this scan stack entry */
                freeScanStackEntry(so, stackEntry);
-               /* clear temp context before proceeding to the next one */
-               MemoryContextReset(so->tempCxt);
        }
 
        if (buffer != InvalidBuffer)