Re: Odd sudden performance degradation related to temp object churn

Oh yeah, sorry. Was looking at a different system where we were using
a tablespace for temp tables.

On Tue, Aug 15, 2017 at 10:00 AM, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote:
>> > 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

--
To understand recursion, one must first understand recursion.