From: "Joel Fradkin" <jfradkin@wazagua.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: MSSQL versus Postgres timing
Date: Tue, 1 Feb 2005 15:19:19 -0500
Message-ID: <000101c5089b$654bb2a0$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_0002_01C50871.7C75AAA0"
In-Reply-To: <20050201170654.GA65518@winnie.fuhr.org>

This is a multi-part message in MIME format.

------=_NextPart_000_0002_01C50871.7C75AAA0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit


-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org] 
Sent: Tuesday, February 01, 2005 12:07 PM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] MSSQL versus Postgres timing

On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote:
> 
> A table with 645,000 records for associates has view (basically select *
> from tblassociates where clientnum = 'test')
> 
> This is taking 13 seconds in postgres and 3 seconds in MSSQL.

Please post the EXPLAIN ANALYZE output for the slow query, once
with enable_seqscan on and once with it off.  For example:

SET enable_seqscan TO on;  -- if not already on
EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test';

SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test';

> Be glad to provide the view and tables etc.

Please do -- it might help us spot something that could be improved.
What version of PostgreSQL are you using?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

------=_NextPart_000_0002_01C50871.7C75AAA0
Content-Type: text/plain;
	name="explainanalyzeviwassoclist_noseqscan.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="explainanalyzeviwassoclist_noseqscan.txt"

QUERY PLAN=0A=
"Merge Join  (cost=3D109454.32..109940.16 rows=3D3758 width=3D111) =
(actual time=3D11646.990..13474.449 rows=3D85694 loops=3D1)"=0A=
"  Merge Cond: (""outer"".locationid =3D ""inner"".locationid)"=0A=
"  ->  Sort  (cost=3D1168.37..1169.15 rows=3D312 width=3D48) (actual =
time=3D35.359..36.651 rows=3D402 loops=3D1)"=0A=
"        Sort Key: l.locationid"=0A=
"        ->  Index Scan using ix_tbllocation on tbllocation l  =
(cost=3D0.00..1155.44 rows=3D312 width=3D48) (actual =
time=3D29.811..33.415 rows=3D402 loops=3D1)"=0A=
"              Index Cond: ('SAKS'::text =3D (clientnum)::text)"=0A=
"  ->  Sort  (cost=3D108285.96..108499.98 rows=3D85611 width=3D74) =
(actual time=3D11611.560..11995.898 rows=3D85695 loops=3D1)"=0A=
"        Sort Key: a.locationid"=0A=
"        ->  Merge Right Join  (cost=3D100083.75..101272.06 rows=3D85611 =
width=3D74) (actual time=3D7758.824..10598.571 rows=3D99139 loops=3D1)"=0A=
"              Merge Cond: (((""outer"".clientnum)::text =3D =
""inner"".""?column10?"") AND (""outer"".id =3D ""inner"".jobtitleid))"=0A=
"              ->  Index Scan using ix_tbljobtitle_id on tbljobtitle jt  =
(cost=3D0.00..338.90 rows=3D6337 width=3D37) (actual =
time=3D0.089..30.193 rows=3D5662 loops=3D1)"=0A=
"                    Filter: (1 =3D presentationid)"=0A=
"              ->  Sort  (cost=3D100083.75..100297.77 rows=3D85611 =
width=3D52) (actual time=3D7709.988..8543.451 rows=3D99139 loops=3D1)"=0A=
"                    Sort Key: (a.clientnum)::text, a.jobtitleid"=0A=
"                    ->  Index Scan using ix_associate_clientnum on =
tblassociate a  (cost=3D0.00..93069.85 rows=3D85611 width=3D52) (actual =
time=3D0.281..2046.482 rows=3D99139 loops=3D1)"=0A=
"                          Index Cond: ((clientnum)::text =3D =
'SAKS'::text)"=0A=
"Total runtime: 13899.614 ms"=0A=

------=_NextPart_000_0002_01C50871.7C75AAA0--