Кирилл

Как-то так:

select row_to_json(j.*) from (

	select  'string' as parentTableFirstProp, to_json(

		(
		select to_json(array_agg(r.*)) from (
			select s as childTableFirstProp from (select unnest('{another string,yet another string}'::text[]) as s) _
		)as r
		)
	) as parentToChildReference

) as j

У всех разное понимание "правильно". По сути все сводится к "либо все, либо ничего".
Если есть понимание как работает СУБД и её возможностей, вы скорее всего будете все выносить в нее: приложение будет туда только "ходить за данными" (дергать одну "хранимку" всесто кучи запросов), и записывать данные, опять же через "хранимку" и одним запросом вместо кучи. У нас даже "шедулеры" в самой БД (если они должны менять данные или что-то с ними делать).

Либо вариант когда СУБД выступает как хранилище строк с продвинутыми возможностями типа вторичных ключей и индексов + более развесистый синтаксис на "селект", такое обычно с MySql в виду его "особенностей"

Да, для постгреса используйте ltree: удобно, производительно

Вообще вся эта штука сильно зависит от селективности, если данных по value много постгрес может решить что "посортировать так" это эффективнее, в общем же случае индекс будет использоваться

т.к. "настройки по-умолчанию" сами сделайте analyze для entities, т.к. скорее всего постгрес не в курсе как у вас там данные по таблице распределены и "полюет на все фулсканом"

ps:

create table entities (id serial, value int, date timestamp);

create index idx_order on entities (value, date asc nulls last);


insert into entities (value, date)
select 
generate_series(1, 20),
time
from generate_series(date_trunc('day', current_timestamp - '10 day'::interval), date_trunc('day', current_timestamp ), '1 second') as time;


analyze entities;


explain SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST


Sort  (cost=239331.74..241545.03 rows=885318 width=16)
  Sort Key: date
  ->  Bitmap Heap Scan on entities  (cost=32277.78..136750.25 rows=885318 width=16)
        Recheck Cond: (value = 10)
        ->  Bitmap Index Scan on idx_order  (cost=0.00..32056.45 rows=885318 width=0)
              Index Cond: (value = 10)


truncate entities;

insert into entities (value, date)
select 
generate_series(1, 200),
time
from generate_series(date_trunc('day', current_timestamp - '1 day'::interval), date_trunc('day', current_timestamp ), '1 second') as time;

analyze entities;


explain SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST;

Sort  (cost=106726.86..106942.86 rows=86401 width=16)
  Sort Key: date
  ->  Bitmap Heap Scan on entities  (cost=3202.17..99642.51 rows=86401 width=16)
        Recheck Cond: (value = 10)
        ->  Bitmap Index Scan on idx_order  (cost=0.00..3180.57 rows=86401 width=0)
              Index Cond: (value = 10)



explain analyze SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST;

Sort  (cost=106726.86..106942.86 rows=86401 width=16) (actual time=18774.148..18783.923 rows=86401 loops=1)
  Sort Key: date
  Sort Method: external sort  Disk: 2200kB
  ->  Bitmap Heap Scan on entities  (cost=3202.17..99642.51 rows=86401 width=16) (actual time=2618.908..18681.170 rows=86401 loops=1)
        Recheck Cond: (value = 10)
        Rows Removed by Index Recheck: 9708392
        Heap Blocks: exact=33638 lossy=52763
        ->  Bitmap Index Scan on idx_order  (cost=0.00..3180.57 rows=86401 width=0) (actual time=2611.946..2611.946 rows=86401 loops=1)
              Index Cond: (value = 10)
Planning time: 0.093 ms
Execution time: 18788.452 ms


set work_mem='1GB';

explain analyze SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST;

Sort  (cost=106726.86..106942.86 rows=86401 width=16) (actual time=614.335..619.024 rows=86401 loops=1)
  Sort Key: date
  Sort Method: quicksort  Memory: 7123kB
  ->  Bitmap Heap Scan on entities  (cost=3202.17..99642.51 rows=86401 width=16) (actual time=198.700..595.232 rows=86401 loops=1)
        Recheck Cond: (value = 10)
        Heap Blocks: exact=86401
        ->  Bitmap Index Scan on idx_order  (cost=0.00..3180.57 rows=86401 width=0) (actual time=177.392..177.392 rows=86401 loops=1)
              Index Cond: (value = 10)
Planning time: 0.145 ms
Execution time: 623.327 ms