1

I recently read about the ltree column & how it could, supposedly, be GIST indexed to provide relatively good support; this seemed like a good match for a problem I'm currently playing with, so I created a table like so:

CREATE TABLE foo (
  id varchar(12) NOT NULL, -- ID is a hash, not a serial
  parent varchar(12) NULL references foo(id), -- some nodes may be orphaned
  pos integer NOT NULL, -- orphaned nodes do not have a position
  primary key (id)
);

This is a slight simplification, but is essentially the structure of the existing table. (Additional columns omitted)

If I extend the table thus:

ALTER TABLE foo ADD COLUMN path ltree NULL;
CREATE foo_path_gist ON foo (path) WHERE path IS NOT NULL;

And begin serially inserting records in a balanced fashion, after only some hundreds, or maybe a couple thousand, updating the index takes so much memory (the machine I'm using to test has 2GB available) that the kernel OOM killer steps in and terminates Pg.

The machine has not yet been tuned - Pg is running with all default settings - but I'm not sure what, if anything, would cause that kind of memory consumption to begin with. I could understand it if the combined size of the path nodes was large, but at this point we're talking only a few megabytes of data, at the most.

Update:

  1. Records are inserted by selecting a key at random, and then walking down the tree until an opening is found.
  2. All insertion is done by a single process, one at a time
  3. The problem (excessive memory consumption) does not appear if I remove the GIST index from the column.
  4. If I try to create the index after inserting more than a few hundred rows, I get the error:

stack depth limit exceeded

  • The workload you describe should not be a problem at all - even with default settings which are very conservative. How do you serially insert records in a balanced fashion exactly? And how do you know the GiST index is slowing you down, and not something else? – Erwin Brandstetter May 2 '18 at 2:21
  • @ErwinBrandstetter I've answered in an edit to the question. – Vector Gorgoth May 2 '18 at 15:01
  • Your description for item 1 is too vague to yield a meaningful answer, or to be turned into a test case. Are you describing what you do, or what you think GiST does? – jjanes May 3 '18 at 15:08
  • @jjanes What I'm doing to insert records that are then indexed by GiST. I have no idea how GiST operates internally, although I have some dark suspicions given the amount of memory I watched it burn through. – Vector Gorgoth May 4 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.