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-0o7CwsU0UCnAshOtpDR8cSpSjy0g@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