Postgres version: PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
- Open psql and run query. Takes 80ms.
Run same query again. Takes 11ms
Close psql and open again.
- Running same query again results in 80ms first time and 11ms second time.
We are using pgbouncer with Transaction pooling mode. Same behavior is observed from Java application code.
Only difference is seen in bitmap index scan times.
Bitmap Heap Scan on TABLE_NAME_MASKED (cost=5.09..304.74 rows=79 width=638) (actual time=5.070..83.190 rows=105 loops=1) Bitmap Heap Scan on TABLE_NAME_MASKED (cost=5.09..304.74 rows=79 width=638) (actual time=2.161..12.129 rows=105 loops=1)
Will changing pooling mode of pgbouncer help here? We usually see 8 TCP connections established between JVM and pgbouncer, so we hope application is not closing and opening psql session.
Any pointers to bring down execution times to 10ms level for most of the queries in production?