Re: SQL Server performing much better?!?! - Mailing list 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)