Digital Geography

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

HeidiSQL pgAdmin III difference
2,509 49,639 47,130

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

HeidiSQL pgAdmin III difference
0,247 4,866 4,619

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

HeidiSQL pgAdmin III Differenz
1,381 1,403 0,022

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

HeidiSQL pgAdmin III Differenz
1,403 8,978 7,575

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.

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Rodrigo Principe Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
newest oldest most voted
Notify of
Rodrigo Principe
Guest

I am just begging with PostGIS, and it is very useful to know there is an excellent (and free) client. As soon as I get some money into paypal I will donate too. Thank you Maximilian!