HeidiSQL vs. pgAdmin III – a small speed-test

HeidiSQL, a free database-client, now supports PostgreSQL/PostGIS in the newest nightly builds. Using HeidiSQL with MySQL was always a pleasure and I often had the feeling that the pgAdmin III client of PostgreSQL is slow and not very user-friendly in some points. Because of this I had to test the difference of performance of these two clients. I don’t want to talk about features and handling because this is on the one hand a matter of taste and on the other hand the support of PostgreSQL is very new in HeidiSQL. Instead, I want to compare some simple SQL-queries in regards of the time needed. Perhaps this small test will be extended by a more complex one.

For the results the nightly-version 8.3.0.4840 of HeidiSQL and the stable v. 1.18.1 of pgAdmin III were used. I also compared the results to the new pgAdmin 1.20 beta 2 but the difference to the stable version was marginal. I used a local 9.3.5.1 PostgreSQL Server with the PostGIS extension (2.1.4). The system consists of an Intel i5-4670K @3.40Ghz, 16 GB RAM, Windows 8.1 (64bit), a SSD system drive and a HDD where the PostgreSQL data-folder was stored on. On a system equipped with an i3, 8GB RAM and  Win 7 Prof. the percentages of the results were comparable. Every query was executed 5 times and all results are shown in seconds.

Query 1: SELECT * FROM table; 52 x 18.823 lines

Query 2: SELECT * FROM table2; 25 x 11.511 lines

Query 3: SELECT ST_AsKML(table.geom) FROM table, table2 WHERE ST_Contains(table2.geom, table.geom); 1 x 18.801 lines

Query 4: SELECT ST_Transform(geom, 900913) a, ST_Transform(geom, 3857) b FROM table2; 2 x 11.511 lines

So the difference of HeidiSQL and PostgreSQL is partially almost 2000%, which is a lot in daily use. Again and again I’m amazed what freeware software performs and how motivated the developers are. The very fast respond of a bug-report sometimes is a new nightly-build. All together I just had to donate.

I hope these results were interesting for some of you, who work with PostgreSQL/PostGIS or were unhappy with pgAdmin III.