SQL query row scan slows down after a few 1000 rows

<p>Hey everyone,</p> <p>I have to query about 15000 rows from a Postgres database, of 1 or 2 columns. I do this with the pq driver and regular database/sql. Run rows, err := db.Query(...), start a for rows.Next() loop and Scan into some variables. However, this will go well for a few thousand rows and then start sporadically halt for dozens of seconds, at what appears to be the rows.Next() point.</p> <p>I&#39;ve searched all over but I can&#39;t seem to find an explanation for this. Does a query page through the results? As in, fetch a bunch and requery for the next batch once the end is reached?</p> <p>Any help would be greatly appreciated</p> <p>Thanks!</p> <hr/>**评论：**<br/><br/>hell_0n_wheel: <pre><blockquote> <p>I&#39;ve searched all over but I can&#39;t seem to find an explanation for this.</p> </blockquote> <p>Because the explanation is on your server, not on the internet. And we aren&#39;t going to be able to explain it to you because we can&#39;t see what&#39;s on your server either. This is all you. </p> <p>Your next steps might go something like: find the option(s) and/or tool(s) provided with your DB to start logging slow queries. Find out what are the slowest, then use EXPLAIN to see what&#39;s going on... it might tell you that you&#39;re missing an index or something.</p> <p>You might also look at lock contention, to see if your query is hung up waiting for some row(s) to become unlocked.</p> <p>You could also take a packet capture of the traffic between the DB and your program. Could be some clues there.</p> <blockquote> <p>at what appears to be the rows.Next() point.</p> </blockquote> <p>You might also profile your program, to make sure this is what&#39;s happening. You could spend all day looking at your DB but if your program&#39;s locking itself, all that DB work is for naught.</p></pre>watr: <pre><p>If you figure this out, please make sure to post the solution. I&#39;m very interested. </p> <p>First step is always to isolate the problem. Make sure it&#39;s not postgres first, then focus on your app. Postgres has a large and helpful community.</p></pre>Everlag: <pre><p>Without the actual code, I can&#39;t really give you more than &#39;use the profiler&#39;.</p> <p>Specifically, <a href="https://github.com/pkg/profile" rel="nofollow">this package</a> should drop into your <code>main()</code>. Since you probably want to ctrl-c your program rather than wait once the slowdown happens, <a href="https://stackoverflow.com/a/11269077" rel="nofollow">catch the signal</a> and call <code>profile.Stop()</code> using a closure to capture the result from <code>profile.Start()</code>.</p> <p>Some potential issues could be heap allocation or constant regex compilation. Profiling should reveal this.</p></pre>hobbified: <pre><p>The profiler is going to say &#34;socket read&#34;, most likely.</p></pre>