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?

Thanks, Sameer

migrated from stackoverflow.com Jul 18 '16 at 7:10

This question came from our site for professional and enthusiast programmers.

  • 1
    You're probably hitting query cache, which gets cleared when you close psql. – Madara Uchiha Jul 18 '16 at 7:09
  • psql session was just an example, but this behavior is seen from java application also, which we believe, is not closing and opening a session every time. Only thing between java application and DB port 5432 is pgbouncer in session pooling mode. – Sameer Naik Jul 18 '16 at 7:13
  • 1
    What you see in the query plan is the time the database engine needs for fetching the data. Not pooler/netwoek traffic/else is calculated. That suggests that your data falls out of the cache very quickly, or you have a pattern that we don't see from the question - like a WHERE clause that is (sufficiently) different only on the third try (I mean every pair hitting the already visited data pages). – dezso Jul 18 '16 at 7:35
  • 1
    "application is not closing and opening psql session" the session is already established in psql before you run the first query so connection overhead can't account for the difference. As others have suggested it is likely caching that is the difference. When you reconnect psql you are perhaps connecting to a different backend. – Jack Douglas Jul 18 '16 at 7:58
  • Correction: We are using transaction pooling mode, and not session. Checking if changing that will make any difference. – Sameer Naik Jul 18 '16 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.