Postgresql – Postgres PLPGSQL ‘un-stored’ procedures

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;                                       
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.

Assuming you checked off usual suspects in the wiki page as commented by @a_horse.

Also see the spin-off addressing the discussion of bitmap index scans and the size of work_mem.

• "Recheck Cond:" line in query plans with a bitmap index scan

Query

This rewritten query should be substantially faster:

SELECT id || ':' || group_number AS uniq_id
    -- id::text || ':' || group_number AS uniq_id  -- with integer
FROM   table_one t1
WHERE  NOT EXISTS ( 
   SELECT 1
   FROM   table_two t2
   WHERE  t2.id = t1.id
   AND    t2.group_number = t1.group_number
   ) 
AND NOT EXISTS (
   SELECT 1
   FROM   table_three t3
   WHERE  t3.timestamp > NOW() - interval '30 days' 
   AND    t3.client_id > 0
   AND    t3.id = t1.id
   );

• The most important problem is comparing a concatenated string between table_one and table_two, which is generally more expensive than necessary and specifically not sargable.

• Storing integer numbers as strings is expensive nonsense. You seem to be aware of that. Convert to integer if at all possible. If you only have valid integer numbers in the varchar column id, all you need to do is:

  ALTER TABLE table_one ALTER COLUMN id TYPE integer USING id::int;
  

  And possibly the same for table_two.

• NOT IN carries a trap for NULL values on either side. That's why NOT EXISTS is almost always better. (Typically performs better on top of that.)

  • Select rows which are not present in other table

Indexes

Either way, key to performance are matching indexes.

Be sure to have multicolumn index on table_one and table_two:

CREATE INDEX t1_foo_idx ON table_one (id, group_number)
CREATE INDEX t2_foo_idx ON table_two (id, group_number)

Possibly allows index-only scans.
With integer instead of varchar, these would be smaller and more efficient, yet:

• Is a composite index also good for queries on the first field?

And I suggest a partial multicolumn index on table_three:

CREATE INDEX t3_foo_idx ON table_three (timestamp, id)
WHERE  client_id > 0
AND    timestamp > '2015-06-07 0:0';

Usefulness deteriorates over time. Recreate the index with increased lower bound at opportune moments - which takes an exclusive lock on the table, so consider CREATE INDEX CONCURRENTLY. Detailed explanation:

• Index optimization with dates

You need to match the (updated) index condition in your queries. Add the condition even if that seems redundant. Like:

...
AND NOT EXISTS (
   SELECT 1
   FROM   table_three t3
   WHERE  t3.timestamp > NOW() - interval '30 days' 
   AND    t3 timestamp > '2015-06-07 0:0'  -- match index condition
   AND    t3.client_id > 0
   AND    t3.id = t1.id
   );

You can use a function as pseudo-constant in partial index and query and automate the process. Last chapter in this related answer:

• Get latest child per parent from big table - query is too slow

SET LOCAL

Like you found yourself, increasing work_mem locally for the query helps if the query needs that much RAM. Consider SET LOCAL:

• work_mem in postgresql on Linux
• Speed up creation of Postgres partial index

With all suggested improvements in place you may not need to increase work_mem any more.