Thread: Slow Query, many tmpfiles
Hello, some simple query are very slow since a few days, no significant
changes are done the last days. I look for disk-io, swap, looked tables,
all seems to be good.
What I have found is this:
postgres=# SELECT temp_files AS "Temporary files"
, temp_bytes AS "Size of temporary files"
FROM pg_stat_database db;
Temporary files | Size of temporary files
-----------------+-------------------------
0 | 0
0 | 0
0 | 0
0 | 0
13542 | 5313912899891
(5 rows)
postgres@dolly:~/9.6/main/base/pgsql_tmp$ ls -la
total 20
drwx------ 2 postgres postgres 16384 Feb 3 02:25 .
drwx------ 8 postgres postgres 4096 Jul 26 2017 ..
Where does the 13542 tmp files are come from?
How can I delete that?
Where are they stored?
basti <mailinglist@unix-solution.de> writes:
> What I have found is this:
> postgres=# SELECT temp_files AS "Temporary files"
> , temp_bytes AS "Size of temporary files"
> FROM pg_stat_database db;
> Temporary files | Size of temporary files
> -----------------+-------------------------
> 0 | 0
> 0 | 0
> 0 | 0
> 0 | 0
> 13542 | 5313912899891
> (5 rows)
> postgres@dolly:~/9.6/main/base/pgsql_tmp$ ls -la
> total 20
> drwx------ 2 postgres postgres 16384 Feb 3 02:25 .
> drwx------ 8 postgres postgres 4096 Jul 26 2017 ..
> Where does the 13542 tmp files are come from?
> How can I delete that?
> Where are they stored?
Those are temp files that have been created during queries since you last
reset the statistics counters. By the time they're counted in this view,
they've already been deleted, so there's nothing for you to do here
maintenance-wise. But if the numbers seem to be going up a lot over time,
that might suggest that you look for the queries causing it.
regards, tom lane