'Store greenplum temporary tables in memory [temp_buffers]

I'm trying to figure out if greenplum could store temporary table data in memory. According to the documentation it is possible, while the memory size is controlled by the [temp_buffers] parameter.

GP Doc (https://gpdb.docs.pivotal.io/5290/ref_guide/config_params/guc-list.html#temp_buffers) :

temp_buffers

Sets the maximum number of temporary buffers used by each database session. These are session-local buffers used only for access to temporary tables. The setting can be changed within individual sessions, but only up until the first use of temporary tables within a session. The cost of setting a large value in sessions that do not actually need a lot of temporary buffers is only a buffer descriptor, or about 64 bytes, per increment. However if a buffer is actually used, an additional 8192 bytes will be consumed.

But in reality, It doesn't work as I imagined. My tests show it doesn't make any difference. The test is simple: Create an ordinary table, insert some considerable rows as test data. Create a temporary table, insert test data from the ordinary table. Create another temporary table, insert test data from previous temporary table. Record total run time, exit session, then repeat (didn't repeat the ordinary table).

[10 times, temp_buffers from 32MB (default 1024) to 32GB] 1

[Average and minimal runtime] 2

What temp_buffers is for? Am I understanding wrong? Is the any way to test or prove it?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source