Re: Performance of INSERT into temporary tables using psqlODBC driver

Hello Tim,

>How are you gathering metrics to determine if performance has improved
>or not?
I am measuring the response times through timer for the execution of SQL
statements through psqlODBC driver. The response times for INSERT INTO
temp-table statements have not changed with the parameters I modified.

>Have you seen any change in your explain (analyze, buffers) plans?

There was no change in the EXPLAIN for INSERT INTO statement, but the
performance of the queries improved by about 5%.

>Make sure your table statistics are all up-to-date before performing
>each benchmark test. I often turn off autovacuum when doing this sort of
>testing so that I know exactly when tables get vacuumed and statistics
>get updated (just ensure you remember to turn it back on when your
>finished!).
I ran the VACUUM ANALYZE statement manually before starting the tests. Even
though autovacuum was turned on, it did not get invoked due to the
thresholds and as bulk of the inserts are in temporary tables.

>Are the wal checkpoints being triggered every 30 mins or more
>frequently?
The wal checkpoints are triggered every 30 mins.

>Are you still seeing the system use lots of temp files?
I do not see any files in pgsql_tmp folders in the tablespaces where the
tables are created. Also, I do not see pgsql_tmp folder in base and global
folders. Am I checking for these files in the correct location? Also, I ran
the following query (taken from another forum) to check the temporary files
generated for all the databases:
SELECT temp_files AS "Temporary files", temp_bytes AS "Size of temporary
files" FROM pg_stat_database db;

The result is 0 for both columns.

>Do you have any indexes on the tables your inserting into?
I have not created indexes on these temporary tables, but programatically
executed /ANALYZE <temp-table>/ statement after the data is inserted into
these temp tables, to generate/update statistics for these tables. Indexes
do exist for all regular tables.

>As mentioned previously, there are no simple/quick fixes here - you
>cannot just change a setting and see performance improve. It will be
>necessary to do a lot of experimentation, gathering statistics and
>investigate how postgres is using buffers, disk IO etc. All of these
>parameters interact with each other, so it is critical you have good
>metrics to see exactly what your changes do. It is complex and time
>consuming. Highly recommend PostgreSQL: High Performance (Ahmed & SMith)
>and Mastering Postgres (Shonig) for valuable background/tips - there
>really is just far too much to communicate effectively via email.

Thank you for the suggestions on the books. I will go through these.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html