pgAdminIII performance issue - Mailing list pgadmin-support

From Anderson, Derek
Subject pgAdminIII performance issue
Date
Msg-id e7f1b06191f045139b136dd88a5caec4@MAC-EX2013-DAG1.MAC.MSP.AIRPORT
Whole thread Raw
List pgadmin-support
<div class="WordSection1"><p class="MsoNormal">Just installed pgAdmin 1.22.0 on a new desktop when when I noticed some
basicqueries 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:<pclass="MsoNormal"> <p class="MsoNormal">pgAdmin III v1.22.0 (standard install/no build) running Windows 7, 5
geni5/8GB ram, Gigabit networking.<p class="MsoNormal"> <p class="MsoNormal">Here are the results of running of two
versionsof pgAdmin running on the same windows desktop against the same backend server; The two have very different
outcomes:<tableborder="0" cellpadding="0" cellspacing="0" class="MsoNormalTable"
style="border-collapse:collapse"><tbody><trstyle="height:10.25pt"><td style="width:63.3pt;border:solid windowtext
1.0pt;padding:0in5.4pt 0in 5.4pt;height:10.25pt" valign="top" width="84"><p
class="MsoNormal"><b><i>Version</i></b></td><tdstyle="width:314.65pt;border:solid windowtext
1.0pt;border-left:none;padding:0in5.4pt 0in 5.4pt;height:10.25pt" valign="top" width="420"><p
class="MsoNormal">v1.18.1</td><tdstyle="width:314.65pt;border:solid windowtext 1.0pt;border-left:none;padding:0in 5.4pt
0in5.4pt;height:10.25pt" valign="top" width="420"><p class="MsoNormal">V1.22.0</td></tr><tr><td
style="width:63.3pt;border:solidwindowtext 1.0pt;border-top:none;padding:0in 5.4pt 0in 5.4pt" valign="top"
width="84"><pclass="MsoNormal"><b><i>Connection</i></b></td><td colspan="2"
style="width:629.3pt;border-top:none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid windowtext
1.0pt;padding:0in5.4pt 0in 5.4pt" valign="top" width="839"><p class="MsoNormal">Same connection (host, database name,
port,username, and query) using the same windows desktop.</td></tr><tr><td style="width:63.3pt;border:solid windowtext
1.0pt;border-top:none;padding:0in5.4pt 0in 5.4pt" valign="top" width="84"><p
class="MsoNormal"><b><i>Query</i></b></td><tdcolspan="2"
style="width:629.3pt;border-top:none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid windowtext
1.0pt;padding:0in5.4pt 0in 5.4pt" valign="top" width="839"><p class="MsoNormal">select * from m.events<p
class="MsoNormal">wheretime > '2016-01-01'</td></tr><tr><td style="width:63.3pt;border:solid windowtext
1.0pt;border-top:none;padding:0in5.4pt 0in 5.4pt" valign="top" width="84"><p class="MsoNormal"><b><i>Execution
Time</i></b></td><tdstyle="width:314.65pt;border-top:none;border-left:none;border-bottom:solid windowtext
1.0pt;border-right:solidwindowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="420"><p
class="MsoNormal">~1.8seconds<br /><img height="28" id="_x0000_i1025" src="cid:image001.png@01D18BEF.FE0AA490"
width="187"/><p class="MsoNormal">Execution time min ~1.6s max 1.9s over 30 executions.</td><td
style="width:314.65pt;border-top:none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid
windowtext1.0pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="420"><p class="MsoNormal">~25 seconds<br /><img
height="37"id="_x0000_i1026" src="cid:image002.png@01D18BEF.FE0AA490" width="194" /><p class="MsoNormal">Execution time
min~25.5 max 25.9s over 30 executions.  </td></tr><tr><td style="width:63.3pt;border:solid windowtext
1.0pt;border-top:none;padding:0in5.4pt 0in 5.4pt" valign="top" width="84"><p
class="MsoNormal"><b><i>Explain</i></b></td><td
style="width:314.65pt;border-top:none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid
windowtext1.0pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="420"><p class="MsoNormal"><span
style="font-size:10.0pt">"Append (cost=0.43..5122.55 rows=3920 width=200)"</span><p class="MsoNormal"><span
style="font-size:10.0pt">" ->  Index Scan using events_time_idx on events  (cost=0.43..5077.55 rows=3521
width=203)"</span><pclass="MsoNormal"><span style="font-size:10.0pt">"        Index Cond: (time > '2016-01-01
00:00:00-06'::timestampwith time zone)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"  ->  Seq Scan on
events_2002 (cost=0.00..15.00 rows=133 width=172)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"       
Filter:(time > '2016-01-01 00:00:00-06'::timestamp with time zone)"</span><p class="MsoNormal"><span
style="font-size:10.0pt">" ->  Seq Scan on events_2003  (cost=0.00..15.00 rows=133 width=172)"</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">"        Filter: (time > '2016-01-01 00:00:00-06'::timestamp with
timezone)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"  ->  Seq Scan on events_2004 
(cost=0.00..15.00rows=133 width=172)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"        Filter: (time
>'2016-01-01 00:00:00-06'::timestamp with time zone)"</span></td><td
style="width:314.65pt;border-top:none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid
windowtext1.0pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="420"><p class="MsoNormal"><span
style="font-size:10.0pt">"Append (cost=0.43..5122.55 rows=3920 width=200)"</span><p class="MsoNormal"><span
style="font-size:10.0pt">" ->  Index Scan using events_time_idx on events  (cost=0.43..5077.55 rows=3521
width=203)"</span><pclass="MsoNormal"><span style="font-size:10.0pt">"        Index Cond: (time > '2016-01-01
00:00:00-06'::timestampwith time zone)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"  ->  Seq Scan on
events_2002 (cost=0.00..15.00 rows=133 width=172)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"       
Filter:(time > '2016-01-01 00:00:00-06'::timestamp with time zone)"</span><p class="MsoNormal"><span
style="font-size:10.0pt">" ->  Seq Scan on events_2003  (cost=0.00..15.00 rows=133 width=172)"</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">"        Filter: (time > '2016-01-01 00:00:00-06'::timestamp with
timezone)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"  ->  Seq Scan on events_2004 
(cost=0.00..15.00rows=133 width=172)"</span><p class="MsoNormal"><span style="font-size:10.0pt">"        Filter: (time
>'2016-01-01 00:00:00-06'::timestamp with time zone)"</span></td></tr><tr><td
style="width:63.3pt;border-top:none;border-left:solidwindowtext 1.0pt;border-bottom:none;border-right:solid windowtext
1.0pt;padding:0in5.4pt 0in 5.4pt" valign="top" width="84"><p class="MsoNormal"><b><i>Additional<br />
Notes</i></b></td><tdcolspan="2" style="width:629.3pt;border:none;border-right:solid windowtext 1.0pt;padding:0in 5.4pt
0in5.4pt" valign="top" width="839"><p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><span
style="font-family:Symbol"><spanstyle="mso-list:Ignore">·<span style="font:7.0pt "Times New Roman"">        
</span></span></span>Iran test concurrently (same query run at exact same time using the two different versions of
pgAdminon the same windows 7 desktop, against the same dataset and server) to what would happen: <br /> (<i>I was able
torun the query on v1.18.1 nearly 8 times before the v1.22 completed its 1<sup>st</sup> run with nearly the same
executiontime )  <br /><br /> With that result, we can conclude the database is not bogged down, there isn’t server,
network,or desktop bottleneck.  From my perspective the </i><b>issue</b><i> </i><b>seems to be something very specific
withthe delivery of data from the server to the client without being a server, postgres server, network, or desktop
hardwareissue.</b><p class="MsoNormal" style="margin-left:.5in"><b><i>                </i></b><p
class="MsoListParagraph"style="text-indent:-.25in;mso-list:l0 level1 lfo1"><span style="font-family:Symbol"><span
style="mso-list:Ignore">·<spanstyle="font:7.0pt "Times New Roman"">         </span></span></span>Perform a count
“selectcount(*) where time > '2016-01-01'” both returned in 12 milliseconds.<p class="MsoNormal"> <p
class="MsoListParagraph"style="text-indent:-.25in;mso-list:l0 level1 lfo1"><span style="font-family:Symbol"><span
style="mso-list:Ignore">·<spanstyle="font:7.0pt "Times New Roman"">         </span></span></span>Events table has
~14,000,000rows.</td></tr><tr><td style="width:63.3pt;border:solid windowtext 1.0pt;border-top:none;padding:0in 5.4pt
0in5.4pt" valign="top" width="84"><p class="MsoNormal"><b><i> </i></b></td><td colspan="2"
style="width:629.3pt;border-top:none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid windowtext
1.0pt;padding:0in5.4pt 0in 5.4pt" valign="top" width="839"><p class="MsoNormal"> </td></tr></tbody></table><p
class="MsoNormal"> <pclass="MsoNormal">We’re using PostgresSQL 9.3 (eventually moving to 9.4/9.5).  <p
class="MsoNormal"> <pclass="MsoNormal">We also know that we’re returning a lot of data but there are reasons for
this.<pclass="MsoNormal"> <p class="MsoNormal">(<a
href="https://drive.google.com/open?id=0B33G1940rNayNHE1Z0VjZHdIaXc">PNGfile of table listed above</a>)<p
class="MsoNormal">Hasanyone else seen this or can anyone reproduce similar results?<p class="MsoNormal"> <p
class="MsoNormal">Contactme for additional information.<p class="MsoNormal">-Derek<p class="MsoNormal"> </div> 

pgadmin-support by date:

Previous
From: Stephen Cook
Date:
Subject: Overloaded function sorting in the Object Browser
Next
From: Wladimir Alves
Date:
Subject: no properties are available the current selection