title: Postgresql insert trigger becomes slow when querying current table
body: <p>When inserting a lot number into a table we are counting the number times the base number exists, and adding a -## to the end of the new number based on that count.</p>
  
  <p>I have stripped out most the logic (we check for other things as well). I also am aware of the logic flaw here that would skip -1.</p>
  
  <pre class="lang-sql prettyprint-override"><code>-- Function: stone._lsuniqueid()
  
  -- DROP FUNCTION stone._lsuniqueid();
  
  CREATE OR REPLACE FUNCTION stone._lsuniqueid()
    RETURNS trigger AS
  $BODY$
  DECLARE
   _count INTEGER;
  
  BEGIN
    -- Obtain the number of occurences of this new ls_number
    SELECT COUNT(ls_number) into _count
    FROM ls
    WHERE ls_number LIKE CAST(NEW.ls_number || '%' AS text);
  
    -- Allow new ls_numbers to be entered as is, otherwise add "-#{count + 1}"
    -- to the end of the ls_number
    if _count &gt; 0 THEN
      NEW.ls_number = NEW.ls_number || '-' || CAST(_count + 1 AS text);
    END IF;      
  
    RETURN NEW;
  END
  $BODY$
  
  INSERT INTO ls VALUES (NEXTVAL('ls_ls_id_seq'),7285,UPPER('20151012'));
  --&gt; Query returned successfully: one row affected, 391 ms execution time.
  </code></pre>
  
  <p>The count query is plenty fast</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(ls_number)
  FROM ls
  WHERE ls_number LIKE CAST('20151012' || '%' AS text);
  --&gt; 19ms
  </code></pre>
  
  <p>For comparison I tried a similar trigger, but ran the count against a different table with same amount of rows, and similar query time.</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(lsdetail_id)
  FROM lsdetail
  WHERE lsdetail_id &gt; 2433308
  --&gt; 20ms
  </code></pre>
  
  <p>Running the same insert with the count running against a different table returns the result 20 times faster.</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO ls VALUES (NEXTVAL('ls_ls_id_seq'),7285,UPPER('20151012'));
   --&gt; Query returned successfully: one row affected, 20 ms execution time.
  </code></pre>
  
  <p>The ls table has about 2.5 million rows</p>
  
  <p>I've tried a couple of different things and the issue seems to be when selecting from the same table I'm inserting into.</p>
  
  <p>I would like to know why this happening, but I would also be open to a better way to create "sub-lot" numbers.</p>
  
  <p>Thanks!</p>