-----Original Message-----
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 01, 2005 12:07 PM
To: Joel Fradkin
Cc: [email protected]
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/
QUERY PLAN
"Merge Join (cost=109454.32..109940.16 rows=3758 width=111) (actual
time=11646.990..13474.449 rows=85694 loops=1)"
" Merge Cond: (""outer"".locationid = ""inner"".locationid)"
" -> Sort (cost=1168.37..1169.15 rows=312 width=48) (actual
time=35.359..36.651 rows=402 loops=1)"
" Sort Key: l.locationid"
" -> Index Scan using ix_tbllocation on tbllocation l
(cost=0.00..1155.44 rows=312 width=48) (actual time=29.811..33.415 rows=402
loops=1)"
" Index Cond: ('SAKS'::text = (clientnum)::text)"
" -> Sort (cost=108285.96..108499.98 rows=85611 width=74) (actual
time=11611.560..11995.898 rows=85695 loops=1)"
" Sort Key: a.locationid"
" -> Merge Right Join (cost=100083.75..101272.06 rows=85611 width=74)
(actual time=7758.824..10598.571 rows=99139 loops=1)"
" Merge Cond: (((""outer"".clientnum)::text =
""inner"".""?column10?"") AND (""outer"".id = ""inner"".jobtitleid))"
" -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt
(cost=0.00..338.90 rows=6337 width=37) (actual time=0.089..30.193 rows=5662
loops=1)"
" Filter: (1 = presentationid)"
" -> Sort (cost=100083.75..100297.77 rows=85611 width=52)
(actual time=7709.988..8543.451 rows=99139 loops=1)"
" Sort Key: (a.clientnum)::text, a.jobtitleid"
" -> Index Scan using ix_associate_clientnum on
tblassociate a (cost=0.00..93069.85 rows=85611 width=52) (actual
time=0.281..2046.482 rows=99139 loops=1)"
" Index Cond: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 13899.614 ms"
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
