Speeding up the pgAdmin query tool

The pgAdmin query tool (see the bottom two screenshots here) has caused people to mistakenly think their PostgreSQL queries have been overly slow a number of times in the past. In actual fact, PostgreSQL is normally running just fine, as is the query. Some users have then pointed their finger at the network, questioning whether SSL might be to blame, or the TCP/IP stack or firewall on their client machine. Well, sometimes the network is the cause, but in a number of cases what the user is actually seeing is the machine's native listview control taking it's time leisurely rendering the results.

We've actually been aware of this for a long time, and have noted the problem a few times on various mailing lists. We've even gone some way to alleviating the problem by showing two query time - if you look in the bottom right hand corner of the query tool you'll see that timings look like: 51243ms + 65343ms.

This is telling you that the query was executed by PostgreSQL, and data transferred to the client in 51.243 seconds, and it was then rendered in the grid in 65.343 seconds.

Edward Di Geronimo Jr recently stepped up and started woring on improving this - specifically to reduce that second figure that tends to throw people, and to provide enhanced copy/paste capabilities. After just three revisions to his original patch it is now looking ready to apply and is showing rendering time consistently reduced by about 75%! In addition, you can now copy data from arbitrary cells, columns, rows or blocks of cells. Nice.

So how does it work? Well, the original code ran the query, and asynchronously populated a wxListView control in a background thread from the result set once the query completed.

The first change in the new code replaces the wxListView control with a wxGrid control. The wxGrid is known to be more light-weight than the wxListView, but is less feature-rich and refined. This meant that special handling was needed to work around quirks such as the control not properly receiving focus when column or row headers were clicked.

The other major change was to create a new, extremely lightweight class (sqlResultTable) to store the query result itself, rather than relying on the grid. The grid is then populated from the result table.

So, the upshot is that the query tool is much faster than it was, and more convenient to copy data from - look out for it in pgAdmin III v1.6!!

And before I forget, many thanks to Ed for his hard work.