On May 25, 2011, at 9:31 AM, Ben Chobot wrote:
> I'm running 9.0.3, and recently started getting temp files being created. This is a problem because it's making a
bunchof dirty buffers that have to be flushed to disk and my poor little disk isn't up to the task. I'm not sure why
though,because this is the explain verbose for the queries that are creating them:
>
>
> QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..15180.05 rows=4000 width=109) (actual time=159.462..174.694 rows=4000 loops=1)
> -> Index Scan using pending_replication_items on replication_queue (cost=0.00..37247114.20 rows=9814757
width=109)(actual time=159.459..169.061 rows=4000 loops=1)
> Total runtime: 177.437 ms
> (3 rows)
>
>
> My understanding with temp files is that they are generated when sorts need to happen. But given that the index is
doingall the sorting for me, I'm at a loss. Why else might I get temp files?
Just to close the loop, the problem was (apparently) due to table bloat. After I clustered the table, the problems went
awayimmediately. I'd still like to understand what was happening, but at least my problem is solved.