Melkij

CREATE UNIQUE INDEX test_good_default_goods ON test_good (good_id)
WHERE by_default = true

Это ограничение целостности, чтобы не отстрелить себе ногу.

Обновление:
Вместо

update test_good set by_default = true where id = :id

и какой-то фоновой магии, обновлять вот так:

update test_good set by_default = (id = :new_default_id) where good_id = :good_id

Если надо, uuid можно подзапросом по тому же самому :new_default_id вытащить.
Впрочем, возможно этот апдейт споткнётся на уникальном индексе, не помню точно, когда уникальность проверяется - для всего выражения или для каждой строки. Ну, можно будет переписать запрос. В конце концов, у нас ещё есть CTE.

Обычно делают counter_id in (6001, 15998) а не толпу or.

explain analyze select max(time) from params where counter_id in (6001, 15998);

Aggregate (cost=18.52..18.53 rows=1 width=8) (actual time=1.586..1.586 rows=1 loops=1)
-> Index Only Scan using params_counter_id_time_idx on params (cost=0.56..18.51 rows=4 width=8) (actual time=0.769..1.566 rows=12 loops=1)
Index Cond: (counter_id = ANY ('{6001,15998}'::bigint[]))
Heap Fetches: 2
Planning time: 50.191 ms
Execution time: 1.933 ms

Тут на порядок поменьше данных, это просто виртуалка и pg ещё 9.4

or две минуты исполнения тоже не даёт:

Aggregate (cost=25.20..25.21 rows=1 width=8) (actual time=38.797..38.797 rows=1 loops=1)
-> Bitmap Heap Scan on params (cost=9.16..25.19 rows=4 width=8) (actual time=0.362..38.749 rows=12 loops=1)
Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))
Heap Blocks: exact=12
-> BitmapOr (cost=9.16..9.16 rows=4 width=0) (actual time=0.042..0.042 rows=0 loops=1)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.031..0.031 rows=6 loops=1)
Index Cond: (counter_id = 6001)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.009..0.009 rows=6 loops=1)
Index Cond: (counter_id = 15998)
Planning time: 0.247 ms
Execution time: 39.408 ms

Попробуйте сделать analyze params;
Может, у планировщика статистика старая.

Есть View. Планировщику придёт что-то похожее на изначальный запрос и дальше он будет переписывать запрос. Т.е. что-то подобное пункту 1.

Есть Materialized Views. Это пункт 2. www.postgresql.org/docs/9.4/static/rules-materiali...

По порядку:

Повторно запрос выполняется много быстрее.

Значит, вы с головой упёрлись в диск.
Вариант есть только один - ставить быстрый диск.
Вариант похуже, т.к. диск всё равно у вас останется медленный и если не на чтении, то на старте и на записи вы туда упрётесь очень хорошо - использовать больше памяти. Чтобы как можно данных было в памяти. Но при работе эти данные всё равно надо поднимать с медленных дисков, потому базу сначала придётся разогревать. Есть даже штатная утилитка pg_prewarm

В конфиге postgresql менял настройки только авторизации.

По дефолту там что-то несмешное вместо shared_buffers. 32 что ли мегабайта? И это на ключевую характеристику базы.
Увеличивать хотя бы до 20% от общего объёма памяти на машине.

В третьих, актуальный Postgresql довольно плохо умеет развесистый IN. И тому есть не совсем очевидное решение - переписать в join:

select count(*) from test JOIN (VALUES (1),...,(10000)) AS v(val) USING (val);

Такой вот hash join работает быстрее IN. На 10000 элементах получали 10мс против 380мс у IN.

Например,

CREATE OR REPLACE FUNCTION process_data(
    s_input text
)
RETURNS void
AS $function$
DECLARE
    a_data text[];
BEGIN

    a_data := regexp_matches(s_input, '^Login: ([^;]+); IP ([\d\.]+); Time: ([\d\:\.\s]+)$');

    raise notice 'Login %', a_data[1];
    raise notice 'IP %', a_data[2];
    raise notice 'Time %', to_timestamp(a_data[3], 'DD.MM.YYYY HH24:MI:SS');

END;
$function$
LANGUAGE plpgsql STABLE SECURITY DEFINER;

Туннель, может лучше будет сделать?
Поверх SSH или какой-нибудь VPN.

Как ни странно, но, может быть, таких строк действительно нет?