1

I have a query that is very slow on first invocation (14s) but acceptably fast on all followup ones (<500ms).

I ran EXPLAIN (ANALYZE,BUFFERS) to see which disk reads are causing the initial latency. To my surprise all of the buffer numbers seem to match pretty closely between the two reports (first and second invocation). All subsequent invocations are as fast as the second one.

Is there some metric I'm overlooking?

slow invocation report

fast invocation report

(formatted in https://explain.depesz.com/)

1 Answer 1

0

First run reads data from disk and puts it in cache. It takes time because disk reads are slow. Next runs just take data from cache. No disk reads means less time. Buffer cache read is equal in both cases.

2
  • Which cache do you have in mind? The "buffers read" should report how many reads were performed on disk and for the most costly node it's 18k for the first execution and 17.4k on the second one. A 3% percent decrease in reads should not justify a 99% decrease in execution time. Commented Jun 3, 2023 at 9:11
  • I imagine that means the caching must happen outside of postgres - at the OS or Disk level. I just found out about track_io_timing gonna try to get a comparison of the plans with this enabled. Commented Jun 3, 2023 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.