pgAdminIII performance issue

From: "Anderson, Derek" <Derek(dot)Anderson(at)mspmac(dot)org>
To: "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>
Subject: pgAdminIII performance issue
Date: 2016-04-01 13:25:05
Message-ID: e7f1b06191f045139b136dd88a5caec4@MAC-EX2013-DAG1.MAC.MSP.AIRPORT
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

Just installed pgAdmin 1.22.0 on a new desktop when when I noticed some basic queries started running extremely slowly. I went back and installed 1.18.1 (which was being used on my previous desktop) and ran them concurrently to better attempt to understand what is going on. The following describes the issue:

pgAdmin III v1.22.0 (standard install/no build) running Windows 7, 5 gen i5/8GB ram, Gigabit networking.

Here are the results of running of two versions of pgAdmin running on the same windows desktop against the same backend server; The two have very different outcomes:
Version

v1.18.1

V1.22.0

Connection

Same connection (host, database name, port, username, and query) using the same windows desktop.

Query

select * from m.events
where time > '2016-01-01'

Execution Time

~1.8 seconds
[cid:image001(dot)png(at)01D18BEF(dot)FE0AA490]
Execution time min ~1.6s max 1.9s over 30 executions.

~25 seconds
[cid:image002(dot)png(at)01D18BEF(dot)FE0AA490]
Execution time min ~25.5 max 25.9s over 30 executions.

Explain

"Append (cost=0.43..5122.55 rows=3920 width=200)"
" -> Index Scan using events_time_idx on events (cost=0.43..5077.55 rows=3521 width=203)"
" Index Cond: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"
" -> Seq Scan on events_2002 (cost=0.00..15.00 rows=133 width=172)"
" Filter: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"
" -> Seq Scan on events_2003 (cost=0.00..15.00 rows=133 width=172)"
" Filter: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"
" -> Seq Scan on events_2004 (cost=0.00..15.00 rows=133 width=172)"
" Filter: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"

"Append (cost=0.43..5122.55 rows=3920 width=200)"
" -> Index Scan using events_time_idx on events (cost=0.43..5077.55 rows=3521 width=203)"
" Index Cond: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"
" -> Seq Scan on events_2002 (cost=0.00..15.00 rows=133 width=172)"
" Filter: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"
" -> Seq Scan on events_2003 (cost=0.00..15.00 rows=133 width=172)"
" Filter: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"
" -> Seq Scan on events_2004 (cost=0.00..15.00 rows=133 width=172)"
" Filter: (time > '2016-01-01 00:00:00-06'::timestamp with time zone)"

Additional
Notes

* I ran test concurrently (same query run at exact same time using the two different versions of pgAdmin on the same windows 7 desktop, against the same dataset and server) to what would happen:
(I was able to run the query on v1.18.1 nearly 8 times before the v1.22 completed its 1st run with nearly the same execution time )

With that result, we can conclude the database is not bogged down, there isn't server, network, or desktop bottleneck. From my perspective the issue seems to be something very specific with the delivery of data from the server to the client without being a server, postgres server, network, or desktop hardware issue.

* Perform a count "select count(*) where time > '2016-01-01'" both returned in 12 milliseconds.

* Events table has ~14,000,000 rows.

We're using PostgresSQL 9.3 (eventually moving to 9.4/9.5).

We also know that we're returning a lot of data but there are reasons for this.

(PNG file of table listed above<https://drive.google.com/open?id=0B33G1940rNayNHE1Z0VjZHdIaXc>)
Has anyone else seen this or can anyone reproduce similar results?

Contact me for additional information.
-Derek

Browse pgadmin-support by date

  From Date Subject
Next Message Marc Daelemans 2016-04-03 15:10:55 PgAdmin Debugger problem 56C2437D.4030603@enterprisedb.com (view raw or whole thread)
Previous Message Stephen Cook 2016-04-01 05:16:05 Overloaded function sorting in the Object Browser