Thread: select operations that generate disk writes

Hello,

Time for a broad question.  I'm aware of some specific select queries that will generate disk writes - for example, a
sortoperation when there's not enough work_mem can cause PG to write out some temp tables (not the correct
terminology?). That scenario is easily remedied by enabling "log_temp_files" and specifying the threshold in temp file
sizeat which you want logging to happen. 

I've recently been trying to put some of my recent reading of Greg's book and other performance-related documentation
touse by seeking out queries that take an inordinate amount of time to run.  Given that we're usually disk-bound, I've
gottenin the habit of running an iostat in a terminal while running and tweaking some of the problem queries.  I find
thisgives me some nice instant feedback on how hard the query is causing PG to hit the disks.  What's currently
puzzlingme are some selects with complex joins and sorts that generate some fairly large bursts of write activity while
theyrun.  I was able to reduce this by increasing work_mem (client-side) to give the sorts an opportunity to happen in
memory. I now see no temp file writes being logged, and indeed the query sped up. 

So my question is, what else can generate writes when doing read-only operations?  I know it sounds like a simple
question,but I'm just not finding a concise answer anywhere. 

Thanks,

Charles

Hello

2012/7/6 CSS <css@morefoo.com>:
> Hello,
>
> Time for a broad question.  I'm aware of some specific select queries that will generate disk writes - for example, a
sortoperation when there's not enough work_mem can cause PG to write out some temp tables (not the correct
terminology?). That scenario is easily remedied by enabling "log_temp_files" and specifying the threshold in temp file
sizeat which you want logging to happen. 
>
> I've recently been trying to put some of my recent reading of Greg's book and other performance-related documentation
touse by seeking out queries that take an inordinate amount of time to run.  Given that we're usually disk-bound, I've
gottenin the habit of running an iostat in a terminal while running and tweaking some of the problem queries.  I find
thisgives me some nice instant feedback on how hard the query is causing PG to hit the disks.  What's currently
puzzlingme are some selects with complex joins and sorts that generate some fairly large bursts of write activity while
theyrun.  I was able to reduce this by increasing work_mem (client-side) to give the sorts an opportunity to happen in
memory. I now see no temp file writes being logged, and indeed the query sped up. 
>
> So my question is, what else can generate writes when doing read-only operations?  I know it sounds like a simple
question,but I'm just not finding a concise answer anywhere. 

statistics http://www.postgresql.org/docs/9.1/interactive/runtime-config-statistics.html

Regards

Pavel

>
> Thanks,
>
> Charles
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Hello

2012/7/6 CSS <css@morefoo.com>:

So my question is, what else can generate writes when doing read-only operations?  I know it sounds like a simple question, but I'm just not finding a concise answer anywhere.

statistics http://www.postgresql.org/docs/9.1/interactive/runtime-config-statistics.html