Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
--https://stackoverflow.com/q/74118023/5298879
--https://stackoverflow.com/a/74323168/5298879
create table store_database(id serial primary key);
insert into store_database select generate_series(1,1e2,1);--100 test entries
insert into store_database select 101;--101st id not referenced by store_record.database_id
create table store_record(
  id serial primary key,
  updated_at timestamptz default now(),
  database_id int references store_database(id)
  );

version

PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE TABLE

INSERT 0 100

INSERT 0 1

CREATE TABLE

insert into store_record (updated_at,database_id) 
select 
  now()+(random()*100)*('1 day'::interval),
  floor((random()*100))+1 --referencing 100 store_database.id's, leaving 101 unreferenced
from generate_series(1,9e5,1); --dbfiddle might time out above 900000 here.
create index store_record_idx on store_record (database_id, updated_at desc nulls last);

INSERT 0 900000

CREATE INDEX

EXPLAIN (analyze, buffers, verbose)
SELECT
    store_database.id as database_id,
    MAX(store_record.updated_at) AS latest_record_updated_at,
    COUNT(store_record.id) AS record_count
FROM store_database
    LEFT JOIN store_record ON (store_database.id = store_record.database_id)
GROUP BY store_database.id;

prepare show_worker_settings as select 
  name,
  setting,
  short_desc 
from pg_settings 
  where name in 
  ('max_worker_processes',
  'max_parallel_workers',
  'max_parallel_workers_per_gather');

QUERY PLAN

HashAggregate  (cost=23916.80..23942.30 rows=2550 width=20) (actual time=703.173..703.216 rows=101 loops=1)

  Output: store_database.id, max(store_record.updated_at), count(store_record.id)

  Group Key: store_database.id

  Buffers: shared hit=864 read=4870 dirtied=5733 written=4838

  ->  Hash Right Join  (cost=67.38..17166.80 rows=900000 width=16) (actual time=0.072..478.256 rows=900001 loops=1)

        Output: store_database.id, store_record.updated_at, store_record.id

        Inner Unique: true

        Hash Cond: (store_record.database_id = store_database.id)

        Buffers: shared hit=864 read=4870 dirtied=5733 written=4838

        ->  Seq Scan on public.store_record  (cost=0.00..14733.00 rows=900000 width=16) (actual time=0.021..219.387 rows=900000 loops=1)

              Output: store_record.id, store_record.updated_at, store_record.database_id

              Buffers: shared hit=863 read=4870 dirtied=5733 written=4838

        ->  Hash  (cost=35.50..35.50 rows=2550 width=4) (actual time=0.039..0.039 rows=101 loops=1)

              Output: store_database.id

              Buckets: 4096  Batches: 1  Memory Usage: 36kB

              Buffers: shared hit=1

              ->  Seq Scan on public.store_database  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.013..0.021 rows=101 loops=1)

                    Output: store_database.id

                    Buffers: shared hit=1

Planning Time: 0.513 ms

Execution Time: 703.304 ms

EXPLAIN

PREPARE

execute show_worker_settings;
set max_parallel_workers=1;
set max_parallel_workers_per_gather=1;
execute show_worker_settings;

name
setting
short_desc

max_parallel_workers
8
Sets the maximum number of parallel workers that can be active at one time.

max_parallel_workers_per_gather
4
Sets the maximum number of parallel processes per executor node.

max_worker_processes
8
Maximum number of concurrent worker processes.

SELECT 3

SET

SET

name
setting
short_desc

max_parallel_workers
1
Sets the maximum number of parallel workers that can be active at one time.

max_parallel_workers_per_gather
1
Sets the maximum number of parallel processes per executor node.

max_worker_processes
8
Maximum number of concurrent worker processes.

SELECT 3

EXPLAIN (analyze, buffers, verbose)
SELECT
    store_database.id          as database_id,
    s.latest_record_updated_at as latest_record_updated_at,
    coalesce(s.record_count,0) as record_count
FROM store_database
    LEFT JOIN 
    (   SELECT
            store_record.database_id     as database_id,
            MAX(store_record.updated_at) as latest_record_updated_at,
            COUNT(store_record.id)       as record_count
        FROM store_record
        GROUP BY store_record.database_id)
    AS s ON (store_database.id = s.database_id);

QUERY PLAN

Hash Left Join  (cost=16038.36..16080.57 rows=2550 width=20) (actual time=345.679..345.791 rows=101 loops=1)

  Output: store_database.id, s.latest_record_updated_at, COALESCE(s.record_count, '0'::bigint)

  Inner Unique: true

  Hash Cond: (store_database.id = s.database_id)

  Buffers: shared hit=814 read=4929 written=53

  ->  Seq Scan on public.store_database  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.012..0.023 rows=101 loops=1)

        Output: store_database.id

        Buffers: shared hit=1

  ->  Hash  (cost=16035.86..16035.86 rows=200 width=20) (actual time=345.658..345.731 rows=100 loops=1)

        Output: s.latest_record_updated_at, s.record_count, s.database_id

        Buckets: 1024  Batches: 1  Memory Usage: 14kB

        Buffers: shared hit=813 read=4929 written=53

        ->  Subquery Scan on s  (cost=16007.36..16035.86 rows=200 width=20) (actual time=345.481..345.703 rows=100 loops=1)

              Output: s.latest_record_updated_at, s.record_count, s.database_id

              Buffers: shared hit=813 read=4929 written=53

              ->  Finalize GroupAggregate  (cost=16007.36..16033.86 rows=200 width=20) (actual time=345.480..345.659 rows=100 loops=1)

                    Output: store_record.database_id, max(store_record.updated_at), count(store_record.id)

                    Group Key: store_record.database_id

                    Buffers: shared hit=813 read=4929 written=53

                    ->  Gather Merge  (cost=16007.36..16030.36 rows=200 width=20) (actual time=345.473..345.596 rows=200 loops=1)

                          Output: store_record.database_id, (PARTIAL max(store_record.updated_at)), (PARTIAL count(store_record.id))

                          Workers Planned: 1

                          Workers Launched: 1

                          Buffers: shared hit=813 read=4929 written=53

                          ->  Sort  (cost=15007.35..15007.85 rows=200 width=20) (actual time=325.397..325.406 rows=100 loops=2)

                                Output: store_record.database_id, (PARTIAL max(store_record.updated_at)), (PARTIAL count(store_record.id))

                                Sort Key: store_record.database_id

                                Sort Method: quicksort  Memory: 32kB

                                Worker 0:  Sort Method: quicksort  Memory: 32kB

                                Buffers: shared hit=813 read=4929 written=53

                                Worker 0: actual time=305.556..305.564 rows=100 loops=1

                                  Buffers: shared hit=369 read=2258 written=32

                                ->  Partial HashAggregate  (cost=14997.71..14999.71 rows=200 width=20) (actual time=325.299..325.318 rows=100 loops=2)

                                      Output: store_record.database_id, PARTIAL max(store_record.updated_at), PARTIAL count(store_record.id)

                                      Group Key: store_record.database_id

                                      Buffers: shared hit=804 read=4929 written=53

                                      Worker 0: actual time=305.408..305.429 rows=100 loops=1

                                        Buffers: shared hit=360 read=2258 written=32

                                      ->  Parallel Seq Scan on public.store_record  (cost=0.00..11027.12 rows=529412 width=16) (actual time=0.029..98.281 rows=450000 loops=2)

                                            Output: store_record.id, store_record.updated_at, store_record.database_id

                                            Buffers: shared hit=804 read=4929 written=53

                                            Worker 0: actual time=0.033..87.572 rows=410945 loops=1

                                              Buffers: shared hit=360 read=2258 written=32

Planning Time: 0.210 ms

Execution Time: 345.851 ms

EXPLAIN

execute show_worker_settings;
set max_parallel_workers=8;
set max_parallel_workers_per_gather=8;
execute show_worker_settings;

name
setting
short_desc

max_parallel_workers
1
Sets the maximum number of parallel workers that can be active at one time.

max_parallel_workers_per_gather
1
Sets the maximum number of parallel processes per executor node.

max_worker_processes
8
Maximum number of concurrent worker processes.

SELECT 3

SET

SET

name
setting
short_desc

max_parallel_workers
8
Sets the maximum number of parallel workers that can be active at one time.

max_parallel_workers_per_gather
8
Sets the maximum number of parallel processes per executor node.

max_worker_processes
8
Maximum number of concurrent worker processes.

SELECT 3

EXPLAIN (analyze, buffers, verbose)
SELECT
    store_database.id          as database_id,
    s.latest_record_updated_at as latest_record_updated_at,
    coalesce(s.record_count,0) as record_count
FROM store_database
    LEFT JOIN 
    (   SELECT
            store_record.database_id     as database_id,
            MAX(store_record.updated_at) as latest_record_updated_at,
            COUNT(store_record.id)       as record_count
        FROM store_record
        GROUP BY store_record.database_id)
    AS s ON (store_database.id = s.database_id);

QUERY PLAN

Hash Left Join  (cost=13361.34..13403.54 rows=2550 width=20) (actual time=378.839..378.950 rows=101 loops=1)

  Output: store_database.id, s.latest_record_updated_at, COALESCE(s.record_count, '0'::bigint)

  Inner Unique: true

  Hash Cond: (store_database.id = s.database_id)

  Buffers: shared hit=807 read=4945 written=18

  ->  Seq Scan on public.store_database  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.014..0.025 rows=101 loops=1)

        Output: store_database.id

        Buffers: shared hit=1

  ->  Hash  (cost=13358.84..13358.84 rows=200 width=20) (actual time=378.816..378.889 rows=100 loops=1)

        Output: s.latest_record_updated_at, s.record_count, s.database_id

        Buckets: 1024  Batches: 1  Memory Usage: 14kB

        Buffers: shared hit=806 read=4945 written=18

        ->  Subquery Scan on s  (cost=13305.17..13358.84 rows=200 width=20) (actual time=378.628..378.862 rows=100 loops=1)

              Output: s.latest_record_updated_at, s.record_count, s.database_id

              Buffers: shared hit=806 read=4945 written=18

              ->  Finalize GroupAggregate  (cost=13305.17..13356.84 rows=200 width=20) (actual time=378.626..378.847 rows=100 loops=1)

                    Output: store_record.database_id, max(store_record.updated_at), count(store_record.id)

                    Group Key: store_record.database_id

                    Buffers: shared hit=806 read=4945 written=18

                    ->  Gather Merge  (cost=13305.17..13351.84 rows=400 width=20) (actual time=378.618..378.769 rows=300 loops=1)

                          Output: store_record.database_id, (PARTIAL max(store_record.updated_at)), (PARTIAL count(store_record.id))

                          Workers Planned: 2

                          Workers Launched: 2

                          Buffers: shared hit=806 read=4945 written=18

                          ->  Sort  (cost=12305.14..12305.64 rows=200 width=20) (actual time=351.951..351.963 rows=100 loops=3)

                                Output: store_record.database_id, (PARTIAL max(store_record.updated_at)), (PARTIAL count(store_record.id))

                                Sort Key: store_record.database_id

                                Sort Method: quicksort  Memory: 32kB

                                Worker 0:  Sort Method: quicksort  Memory: 32kB

                                Worker 1:  Sort Method: quicksort  Memory: 32kB

                                Buffers: shared hit=806 read=4945 written=18

                                Worker 0: actual time=345.426..345.434 rows=100 loops=1

                                  Buffers: shared hit=270 read=1481

                                Worker 1: actual time=332.046..332.064 rows=100 loops=1

                                  Buffers: shared hit=262 read=1552

                                ->  Partial HashAggregate  (cost=12295.50..12297.50 rows=200 width=20) (actual time=351.861..351.880 rows=100 loops=3)

                                      Output: store_record.database_id, PARTIAL max(store_record.updated_at), PARTIAL count(store_record.id)

                                      Group Key: store_record.database_id

                                      Buffers: shared hit=788 read=4945 written=18

                                      Worker 0: actual time=345.320..345.340 rows=100 loops=1

                                        Buffers: shared hit=261 read=1481

                                      Worker 1: actual time=331.931..331.950 rows=100 loops=1

                                        Buffers: shared hit=253 read=1552

                                      ->  Parallel Seq Scan on public.store_record  (cost=0.00..9483.00 rows=375000 width=16) (actual time=0.043..138.804 rows=300000 loops=3)

                                            Output: store_record.id, store_record.updated_at, store_record.database_id

                                            Buffers: shared hit=788 read=4945 written=18

                                            Worker 0: actual time=0.045..136.644 rows=273494 loops=1

                                              Buffers: shared hit=261 read=1481

                                            Worker 1: actual time=0.041..115.826 rows=283304 loops=1

                                              Buffers: shared hit=253 read=1552

Planning Time: 0.201 ms

Execution Time: 379.025 ms

EXPLAIN

select 
  md5(
    string_agg(
      concat(
        database_id::text,
        latest_record_updated_at::text,
        record_count::text)
      ,',')
  ) 
from (
SELECT
    store_database.id as database_id,
    MAX(store_record.updated_at) AS latest_record_updated_at,
    COUNT(store_record.id) AS record_count
FROM store_database
    LEFT JOIN store_record ON (store_database.id = store_record.database_id)
GROUP BY store_database.id
ORDER BY 1,2,3) as a;

md5

2b10a34df0c15705e12e04fd622c0937

SELECT 1

select 
  md5(
    string_agg(
      concat(
        database_id::text,
        latest_record_updated_at::text,
        record_count::text)
      ,',')
  ) 
from (
SELECT
    store_database.id          as database_id,
    s.latest_record_updated_at as latest_record_updated_at,
    coalesce(s.record_count,0) as record_count
FROM store_database
    LEFT JOIN 
    (   SELECT
            store_record.database_id     as database_id,
            MAX(store_record.updated_at) as latest_record_updated_at,
            COUNT(store_record.id)       as record_count
        FROM store_record
        GROUP BY store_record.database_id)
    AS s ON (store_database.id = s.database_id)
ORDER BY 1,2,3) as a;

md5

2b10a34df0c15705e12e04fd622c0937

SELECT 1
name	setting	short_desc
max_parallel_workers	8	Sets the maximum number of parallel workers that can be active at one time.
max_parallel_workers_per_gather	4	Sets the maximum number of parallel processes per executor node.
max_worker_processes	8	Maximum number of concurrent worker processes.