I have a query on a hypertable where I get this error
ERROR: could not resize shared memory segment "/PostgreSQL.1808576942" to 16777216 bytes: No space left on device
If I reduce the time span in the query for which I get the error the query plan is as follows:
EXPLAIN ANALYSE SELECT *
FROM
core_fb_domain_expanded
WHERE
time >= '2022-06-09 00:00:00+02:00' AND
time <= '2022-07-31 23:00:00+02:00' AND
iva != 0 AND
branchstatus = 'OK' AND
tso IN ('D7')
"Append (cost=173.64..109205.44 rows=9 width=1412) (actual time=4649.607..75583.659 rows=74 loops=1)"
" -> Bitmap Heap Scan on _hyper_35_346_chunk (cost=173.64..13629.09 rows=1 width=1412) (actual time=4649.606..13047.094 rows=8 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2496188"
" Heap Blocks: exact=26447 lossy=252821"
" -> Bitmap Index Scan on _hyper_35_346_chunk_core_fb_domain_expanded_time_idx (cost=0.00..173.64 rows=12481 width=0) (actual time=3605.965..3605.966 rows=2496196 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_344_chunk (cost=173.20..13573.61 rows=1 width=1412) (actual time=3258.711..10279.786 rows=14 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2487430"
" Heap Blocks: exact=20504 lossy=252801"
" -> Bitmap Index Scan on _hyper_35_344_chunk_core_fb_domain_expanded_time_idx (cost=0.00..173.20 rows=12437 width=0) (actual time=2987.768..2987.769 rows=2487444 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_333_chunk (cost=174.17..13688.51 rows=1 width=1412) (actual time=3336.184..5853.147 rows=4 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Rows Removed by Index Recheck: 1462"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 1400586"
" Heap Blocks: exact=31969 lossy=126465"
" -> Bitmap Index Scan on _hyper_35_333_chunk_core_fb_domain_expanded_time_idx (cost=0.00..174.17 rows=12534 width=0) (actual time=2032.981..2032.982 rows=1400590 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_352_chunk (cost=174.37..13710.94 rows=1 width=1412) (actual time=6598.528..8050.803 rows=2 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2510804"
" Heap Blocks: exact=29681 lossy=252820"
" -> Bitmap Index Scan on _hyper_35_352_chunk_core_fb_domain_expanded_time_idx (cost=0.00..174.37 rows=12554 width=0) (actual time=3626.814..3626.814 rows=2510806 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_338_chunk (cost=2.87..162.62 rows=1 width=1412) (actual time=110.514..110.515 rows=0 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 29566"
" Heap Blocks: exact=3214"
" -> Bitmap Index Scan on _hyper_35_338_chunk_core_fb_domain_expanded_time_idx (cost=0.00..2.87 rows=148 width=0) (actual time=38.467..38.468 rows=29566 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_342_chunk (cost=173.48..13602.20 rows=1 width=1412) (actual time=3601.668..9877.954 rows=28 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2492984"
" Heap Blocks: exact=20294 lossy=252801"
" -> Bitmap Index Scan on _hyper_35_342_chunk_core_fb_domain_expanded_time_idx (cost=0.00..173.48 rows=12465 width=0) (actual time=3443.150..3443.151 rows=2493012 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_348_chunk (cost=173.61..13627.52 rows=1 width=1412) (actual time=10086.098..10086.099 rows=0 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2495548"
" Heap Blocks: exact=27928 lossy=252818"
" -> Bitmap Index Scan on _hyper_35_348_chunk_core_fb_domain_expanded_time_idx (cost=0.00..173.61 rows=12478 width=0) (actual time=3464.365..3464.366 rows=2495548 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_339_chunk (cost=173.14..13560.95 rows=1 width=1412) (actual time=3767.787..10110.002 rows=18 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2486116"
" Heap Blocks: exact=37790 lossy=231757"
" -> Bitmap Index Scan on _hyper_35_339_chunk_core_fb_domain_expanded_time_idx (cost=0.00..173.14 rows=12431 width=0) (actual time=3582.164..3582.165 rows=2486134 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" -> Bitmap Heap Scan on _hyper_35_350_chunk (cost=173.88..13649.96 rows=1 width=1412) (actual time=8168.151..8168.152 rows=0 loops=1)"
" Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
" Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
" Rows Removed by Filter: 2500912"
" Heap Blocks: exact=23675 lossy=252824"
" -> Bitmap Index Scan on _hyper_35_350_chunk_core_fb_domain_expanded_time_idx (cost=0.00..173.88 rows=12505 width=0) (actual time=3661.166..3661.166 rows=2500912 loops=1)"
" Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"Planning Time: 43.946 ms"
"JIT:"
" Functions: 36"
" Options: Inlining false, Optimization false, Expressions true, Deforming true"
" Timing: Generation 6.913 ms, Inlining 0.000 ms, Optimization 5.179 ms, Emission 58.839 ms, Total 70.931 ms"
"Execution Time: 75601.326 ms"
If I understand the accepted answer here (postgresql - /dev/shm size recommendation for postgres database in docker - Database Administrators Stack Exchange
) correctly, the shared memory is mainly used for parallel queries. For me, however, it does not look like the query is parallel.
My questions are the following:
- Did the query run in parallel and is this the probable reason why we need so much shared memory?
- Should we simply increase the shared memory or would it be better to optimize the database or query (especially if it is not a parallel query)?