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

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
>
>

--