Slow query / Indexes creation (PostgreSQL 9.2)
Perhaps this will help.

If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it:

CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$
SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int
$body$ LANGUAGE SQL IMMUTABLE SECURITY DEFINER RETURNS NULL ON NULL INPUT;

CREATE INDEX ON gorfs.inode_segments (gorfs.f_get_account_from_full_path(full_path));


Ensure gorfs.inodes has an index (or key much better if applicable) on st_ino!

You run the function split_part several times for each row, this is likely taking a significant toll. I've replaced it with string_to_array, and then fetch the individual pieces as needed. I also didn't understand what you intended to obtain for the field field_name using reverse? The query below returns the last element for it.

Your query returns many million rows. Even if PostgreSQL processes the query reasonably quickly, your client application (especially if you use PgAdminIII) will struggle allocating enough memory and receive and format the results, and probably be what takes the most time. So you may want to create a temporary table with the results, and then query against the temporary table:

CREATE TEMP TABLE myresults AS
WITH
  accounts AS (
    SELECT id
    FROM public.ja_clients
    WHERE NOT (
               (last_sub_pay &gt; EXTRACT('epoch' FROM now() - '4 Months'::INTERVAL) AND price_model &gt; 0) OR
               regdate &gt; EXTRACT('epoch' FROM now() - '3 Month'::INTERVAL) OR
               (price_model = 0 AND jobcredits &gt; 0 AND last_login &gt; EXTRACT('epoch' FROM now() - '4 Month'::INTERVAL))
              )
    ORDER BY 1 LIMIT 100 -- first 100 accounts for testing purposes; comment out this line once the query is proven performant enough
    ) 
SELECT r.parts[4]::INT AS account_id, r.parts[6]::INT AS note_id, r.parts[9] AS variation,
       st_size, segment_index, r.parts[array_upper(r.parts, 1)] AS file_name, st_ino, full_path, size_mb
FROM (
  SELECT string_to_array(full_path, '/') AS parts, st_size, segment_index, i.st_ino, full_path,
         (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
  FROM gorfs.inode_segments s
  JOIN gorfs.inodes i ON (i.st_ino = s.st_ino_target)
  WHERE gorfs.f_get_account_from_full_path(s.full_path) IN (SELECT * FROM accounts)
    AND i.checksum_md5 IS NOT NULL
    AND i.st_size &gt; 0
  ) r;

SELECT *
FROM myresults
LIMIT 100;