Re: SQL Server performing much better?!?!
От | Ian Harding |
---|---|
Тема | Re: SQL Server performing much better?!?! |
Дата | |
Msg-id | sc984180.025@mail.tpchd.org обсуждение исходный текст |
Ответ на | SQL Server performing much better?!?! ("Christian Cabanero" <chumpboy@yahoo.com>) |
Список | pgsql-general |
MS SQL Server is a very good database. It has hundreds of well paid programmers working on it. It makes decisions for youabout things that PostgreSQL will not. I migrated from MSSQL Server to PostgreSQL and found that I had done a lot of things 'wrong' or at least inefficiently andMSSQL Server let me get away with it, PostgreSQL did not. PostgreSQL will not probably ever outperform MSSQL in all areas. PostgreSQL will always outperform MSSQL Server in price,stability, portability, and support. Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: iharding@tpchd.org >>> "Christian Cabanero" <chumpboy@yahoo.com> 03/19/02 06:06PM >>> Hi, I'm having a little bit of trouble figuring out a query and why it takes so long to execute. As a preface, I have the same exact database (or at least very closely duplicated) set up in micro$oft SQL Server, with the same schema, data and the same indexes, etc. When I run this query on a dinky Windows2000 machine (my laptop) it runs relatively fast and comes back in 7 seconds. I have postgreSQL set up on a sun solaris box with 1 GB of memory and it takes over 25 seconds! One caveat is that it's a shared managed server run by verio (VPS). But I still figure it's beafier than my puny laptop! Both databases contain the same data and the same indexes. I've even tried bumping up the sort_mem up from 512 to 16384 (16 MB?). The result set that comes back is 8604 rows. But even if i put a limit of 5 it still takes a long time to run. Also, I've observed that when I run this query in the PostgreSQL database it pegs the CPU on the server at 100%, I don't see any iowait or anything, memory usage doesn't jump up or anything and the swap in use stays the same (all monitored from top). From the plan it looks like it's using indexes where appropriate and when it does do a table scan the cost doesn't seem to be that much. The big operations seem to be when merge joins are performed. I've included both the query and the plan bellow. Any help would be HUGELY appreciated as I'm in the embarrasing situation where SQL Server is heavily out performing PostgreSQL running on a sun box. Heck, it's even running faster on my laptop running cygwin configured in the same way! PLEASE HELP! Thanks! Christian ==================================================== Query: ==================================================== SELECT a.user_id, b.sample_id FROM user_company a, sample_manufacturer b, samples c WHERE a.company_id = b.manufacturer_id AND b.sample_id = c.sample_id AND c.sample_state = 1 UNION SELECT a.user_id, b.sample_id FROM user_company a, samples b, users c WHERE a.company_id = b.sample_manufacturer_id AND b.sample_state = 1 AND b.sample_author_id = c.user_id AND NOT EXISTS ( SELECT p.territory_id FROM territories p, territory_ranges q, manufacturer_territories r WHERE r.manufacturer_id = b.sample_manufacturer_id AND r.assignment_flag = 2 AND r.territory_id = p.territory_id AND p.territory_id = q.territory_id AND p.type IN (1, 2) AND c.zip BETWEEN q.start_value AND q.end_value ) limit 5 ==================================================== Plan: ==================================================== Limit (cost=26137.82..26138.07 rows=5 width=33) (actual time=25895.91..25896.01 rows=5 loops=1) -> Unique (cost=26137.82..26251.99 rows=2283 width=33) (actual time=25895.90..25895.98 rows=6 loops=1) -> Sort (cost=26137.82..26137.82 rows=22834 width=33) (actual time=25895.88..25895.90 rows=13 loops=1) -> Append (cost=98.67..24484.74 rows=22834 width=33) (actual time=82.38..25102.67 rows=19265 loops=1) -> Subquery Scan *SELECT* 1 (cost=98.67..342.06 rows=16937 width=20) (actual time=82.37..609.69 rows=17098 loops=1) -> Merge Join (cost=98.67..342.06 rows=16937 width=20) (actual time=82.35..434.78 rows=17098 loops=1) -> Index Scan using ix_user_company_company_id on user_company a (cost=0.00..26.85 rows=1168 width=8) (actual time=0.41..11.83 rows=373 loops=1) -> Sort (cost=98.67..98.67 rows=763 width=12) (actual time=79.78..108.86 rows=17098 loops=1) -> Hash Join (cost=33.09..62.16 rows=763 width=12) (actual time=26.18..58.97 rows=769 loops=1) -> Seq Scan on sample_manufacturer b (cost=0.00..15.69 rows=769 width=8) (actual time=0.12..13.54 rows=769 loops=1) -> Hash (cost=31.59..31.59 rows=602 width=4) (actual time=23.93..23.93 rows=0 loops=1) -> Seq Scan on samples c (cost=0.00..31.59 rows=602 width=4) (actual time=0.17..18.48 rows=602 loops=1) -> Subquery Scan *SELECT* 2 (cost=24038.44..24142.68 rows=5897 width=33) (actual time=24381.93..24456.97 rows=2167 loops=1) -> Merge Join (cost=24038.44..24142.68 rows=5897 width=33) (actual time=24381.92..24433.35 rows=2167 loops=1) -> Index Scan using ix_user_company_company_id on user_company a (cost=0.00..26.85 rows=1168 width=8) (actual time=0.06..10.58 rows=373 loops=1) -> Sort (cost=24038.44..24038.44 rows=301 width=25) (actual time=24380.15..24383.39 rows=2165 loops=1) -> Merge Join (cost=0.00..24026.05 rows=301 width=25) (actual time=1346.68..24378.12 rows=97 loops=1) -> Index Scan using ix_samples_author_id on samples b (cost=0.00..42.12 rows=602 width=12) (actual time=0.46..32.05 rows=602 loops=1) -> Index Scan using users_pkey on users c (cost=0.00..60.58 rows=1144 width=13) (actual time=0.40..57.70 rows=1419 loops=1) SubPlan -> Nested Loop (cost=0.00..53.30 rows=1 width=12) (actual time=40.27..40.27 rows=1 loops=602) -> Nested Loop (cost=0.00..48.43 rows=2 width=8) (actual time=40.18..40.18 rows=1 loops=602) -> Index Scan using ix_manufacturer_terr_combo3 on manufacturer_territories r (cost=0.00..19.80 rows=5 width=4) (actual time=0.11..0.91 rows=27 loops=602) -> Index Scan using ix_territory_ranges_combo_1 on territory_ranges q (cost=0.00..5.71 rows=1 width=4) (actual time=1.46..1.46 rows=0 loops=15995) -> Index Scan using territories_pkey on territories p (cost=0.00..3.02 rows=1 width=4) (actual time=0.08..0.08 rows=1 loops=505) Total runtime: 25915.75 msec _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
В списке pgsql-general по дате отправления:
Предыдущее
От: Thomas LockhartДата:
Сообщение: Re: Bad timestamp external representation '2001-05-11