I think the amount of data being processed would also play a role in if the disk is used for intermediate results or not. As an example if you query a huge table lets say of test_results and you ask the database to sort the data on a non-indexed column say creation_date. If it the database sees a creation_date of 1/1/2001 the database cannot return that row to the user yet because as the database looks through the table it may find a earlier date or 1/1/1999. This forces the database to look through and process all the rows before returning any data to the user. If you place a index on the creation_date column then the database may have a way of pulling the data without sorting the data in memory(or disk) as the index will let the database know which rows come first. There may be some other query operations where the database needs to store large intermediate results that could also spill out to disk.
--JayR
From: Parul Lakkad <parul.lakkad@gmail.com> To: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org> Sent: Tuesday, March 11, 2014 4:24 AM Subject: [NOVICE] Disk usage for intermediate results in join algorithms
Hi,
I am trying to figure out when disk is used to store intermediate results while performing joins in postgres.
According my findings using 'explain analyse ' only merge sort uses disk.
