On Wed, Jul 10, 2019 at 12:27 AM Konstantin Malanchev <hombit@xxxxxxxxx> wrote: > I'm executing the query with smaller work_mem, it will take some time. But I still confused why it used all /dev/shm (4GB) and fails with "no space left" error while work_mem = 512MB. I think it could fail that way for two reasons: /dev/shm size limit (mount option, which I think you are saying you have set to 4GB?), or your system ran out of RAM +swap. The directly listing in your first message only shows 1.4GB, not 4GB, so perhaps it's the second problem. > -> Parallel Hash Join (cost=11030236131.39..255829470118.27 rows=18618961759219 width=31) > Hash Cond: (another_mat_view.oid = my_table.oid) > -> Parallel Seq Scan on another_mat_view (cost=0.00..652514.56 rows=31645556 width=8) > -> Parallel Hash (cost=636676233.38..636676233.38 rows=20353804801 width=31) > -> Parallel Seq Scan on my_table (cost=0.00..636676233.38 rows=20353804801 width=31) > Filter: (flag = '0000000000000000'::bit(16)) It's strange that it's hashing the ~20B row table instead of the ~30M row table. -- Thomas Munro https://enterprisedb.com
