Thread

1. Re: Joins and full index scans...mysql vs postgres?

   ryan groth <postgres@cpusoftware.com> — 2006-02-22T18:52:49Z

   Hmm, it came from the timer on the pgadmin III sql query tool. I guess
   the 1,000ms includes the round-trip? See the wierd thing is that
   mysqlserver is running default configuration on a virtual machine
   (P3/1.3GHZ conf'd for 128mb ram) over a 100m/b ethernet connection.
   Postgres is running on a real P4/3.0ghz 4GB running localhost. Timings
   from the mysql query tool indicate that the 6.5k record query runs in
   "1.3346s (.3361s)" vs. the pgadmin query tool saying that the query runs
   "997+3522 ms". Am I reading these numbers wrong? Are these numbers
   reflective of application performance? Is there an optimization I am
   missing?
   
   Ryan
   
   
   > On Wed, 22 Feb 2006, ryan groth wrote:
   > 
   > > Does this work:
   > >
   > > "Merge Left Join  (cost=0.00..2656.36 rows=6528 width=1522) (actual
   > > time=0.057..123.659 rows=6528 loops=1)"
   > > "  Merge Cond: ("outer".uid = "inner".uid)"
   > > "  ->  Merge Left Join  (cost=0.00..1693.09 rows=6528 width=1264)
   > > (actual time=0.030..58.876 rows=6528 loops=1)"
   > > "        Merge Cond: ("outer".uid = "inner".user_id)"
   > > "        ->  Index Scan using users_pkey on users  (cost=0.00..763.81
   > > rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
   > > "        ->  Index Scan using phorum_users_base_pkey on
   > > phorum_users_base  (cost=0.00..822.92 rows=9902 width=1168) (actual
   > > time=0.007..15.674 rows=9845 loops=1)"
   > > "  ->  Index Scan using useraux_pkey on useraux  (cost=0.00..846.40
   > > rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
   > > "Total runtime: 127.442 ms"
   > 
   > Well, this implies the query took about 127 ms on the server side. Where
   > did the 1000 ms number come from (was that on a client, and if so, what
   > type)?
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: explain analyze is your friend
   > 
   > 
   
   -- 
   
   
   

2. Re: Joins and full index scans...mysql vs postgres?

   PFC <lists@peufeu.com> — 2006-02-22T23:18:58Z

   > "997+3522 ms". Am I reading these numbers wrong? Are these numbers
   > reflective of application performance? Is there an optimization I am
   > missing?
   
   	It also reflects the time it takes to pgadmin to insert the results into  
   its GUI...
   
   	If you want to get an approximation of the time the server needs to  
   process your request, without the data marshalling time on the network and  
   anything, you can either use EXPLAIN ANALYZE (but mysql doesn't have it,  
   and the instrumentation adds overhead), or simply something like "SELECT  
   sum(1) FROM (query to benchmark)", which only returns 1 row, and the sum()  
   overhead is minimal, and it works on most databases. I find it useful  
   because in knowing which portion of the time is spent by the server  
   processing the query, or in data transfer, or in data decoding on the  
   client side, or simply in displaying...
   
   

3. Re: Joins and full index scans...mysql vs postgres?

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-02-23T02:52:11Z

   The pgAdmin query tool is known to give an answer about 5x the real 
   answer - don't believe it!
   
   ryan groth wrote:
   > Hmm, it came from the timer on the pgadmin III sql query tool. I guess
   > the 1,000ms includes the round-trip? See the wierd thing is that
   > mysqlserver is running default configuration on a virtual machine
   > (P3/1.3GHZ conf'd for 128mb ram) over a 100m/b ethernet connection.
   > Postgres is running on a real P4/3.0ghz 4GB running localhost. Timings
   > from the mysql query tool indicate that the 6.5k record query runs in
   > "1.3346s (.3361s)" vs. the pgadmin query tool saying that the query runs
   > "997+3522 ms". Am I reading these numbers wrong? Are these numbers
   > reflective of application performance? Is there an optimization I am
   > missing?
   > 
   > Ryan
   > 
   > 
   >> On Wed, 22 Feb 2006, ryan groth wrote:
   >>
   >>> Does this work:
   >>>
   >>> "Merge Left Join  (cost=0.00..2656.36 rows=6528 width=1522) (actual
   >>> time=0.057..123.659 rows=6528 loops=1)"
   >>> "  Merge Cond: ("outer".uid = "inner".uid)"
   >>> "  ->  Merge Left Join  (cost=0.00..1693.09 rows=6528 width=1264)
   >>> (actual time=0.030..58.876 rows=6528 loops=1)"
   >>> "        Merge Cond: ("outer".uid = "inner".user_id)"
   >>> "        ->  Index Scan using users_pkey on users  (cost=0.00..763.81
   >>> rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
   >>> "        ->  Index Scan using phorum_users_base_pkey on
   >>> phorum_users_base  (cost=0.00..822.92 rows=9902 width=1168) (actual
   >>> time=0.007..15.674 rows=9845 loops=1)"
   >>> "  ->  Index Scan using useraux_pkey on useraux  (cost=0.00..846.40
   >>> rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
   >>> "Total runtime: 127.442 ms"
   >> Well, this implies the query took about 127 ms on the server side. Where
   >> did the 1000 ms number come from (was that on a client, and if so, what
   >> type)?
   >>
   >> ---------------------------(end of broadcast)---------------------------
   >> TIP 6: explain analyze is your friend
   >>
   >>
   > 
   
   
   

4. Re: Joins and full index scans...mysql vs postgres?

   Andreas Pflug <pgadmin@pse-consulting.de> — 2006-02-23T10:21:26Z

   Christopher Kings-Lynne wrote:
   > The pgAdmin query tool is known to give an answer about 5x the real 
   > answer - don't believe it!
   
   Everybody please forget immediately the factor 5. It's no factor at all, 
   but the GUI update time that is *added*, which depends on rows*columns.
   
   
   > ryan groth wrote:
   > 
   >> the pgadmin query tool saying that the query runs
   >> "997+3522 ms".
   
   Means 997ms until all data is at the client (libpq reports the rowset), 
   the rest is GUI overhead.
   
   Regards,
   Andreas