Re: Odd sudden performance degradation related to temp object churn

>
> > Not so.
> >
> > This system has no defined temp_tablespace however spillage due to
> > sorting/hashing that exceeds work_mem goes to base/pgsql_tmp which we
> > have symlinked out to a local SSD drive.
>
> Which is also where temp tables are created.
>

This isn't true, at least in our environment. Just as proof, I have
created a couple of temp tables, and querying the relfilenodes, they only
show up under base/<dbid>/t4_<relfilenode>:

test=# CREATE TEMP TABLE foo(id int);
CREATE TABLE
test=# INSERT INTO foo SELECT * FROM generate_series(1,100);
INSERT 0 100
test=# CREATE TEMP TABLE bar();
CREATE TABLE
test=# SELECT relfilenode FROM pg_class WHERE relname IN('foo','bar');
relfilenode
-------------
20941
20944
(2 rows)

postgres(at)foo:/san/<cluster>/pgdata/base$ ls -l
total 44
drwx------ 2 postgres postgres 4096 Jul 7 15:19 1
drwx------ 2 postgres postgres 4096 Nov 29 2016 12408
drwx------ 2 postgres postgres 4096 Jul 14 14:00 12409
drwx------ 2 postgres postgres 12288 Jul 7 15:19 18289
drwx------ 2 postgres postgres 12288 Jul 7 15:19 18803
drwx------ 2 postgres postgres 4096 Jul 7 15:19 20613
drwx------ 2 postgres postgres 4096 Aug 15 08:06 20886
lrwxrwxrwx 1 postgres postgres 30 Jul 7 15:15 pgsql_tmp ->
/local/pgsql_tmp/9.6/<cluster>

postgres(at)pgsnap05:/san/<cluster>/pgdata/base$ ls -l 20886 | grep
'20941\|20944'
-rw------- 1 postgres postgres 8192 Aug 15 10:55 t4_20941
-rw------- 1 postgres postgres 0 Aug 15 10:55 t4_20944
postgres(at)pgsnap05:/san/dba_dev_d/pgdata/base$ cd pgsql_tmp
postgres(at)pgsnap05:/san/dba_dev_d/pgdata/base/pgsql_tmp$ ls -l
total 0