From: Joel Fradkin <jfradkin@wazagua.com>
To: 'Michael Fuhr' <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: MSSQL versus Postgres timing
Date: Tue, 1 Feb 2005 14:26:07 -0500
Message-ID: <000101c50893$e25da5d0$797ba8c0@jfradkin> (raw)
In-Reply-To: <20050201170654.GA65518@winnie.fuhr.org>
With seq scan on.
-----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/Attachments:explainanalyzeviwassoclist.txt (text/plain, 1.6K) download
QUERY PLAN
"Merge Join (cost=47489.81..47975.65 rows=3758 width=111) (actual time=27167.305..29701.080 rows=85694 loops=1)"
" Merge Cond: (""outer"".locationid = ""inner"".locationid)"
" -> Sort (cost=1168.37..1169.15 rows=312 width=48) (actual time=261.096..262.410 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=213.107..259.160 rows=402 loops=1)"
" Index Cond: ('SAKS'::text = (clientnum)::text)"
" -> Sort (cost=46321.45..46535.47 rows=85611 width=74) (actual time=26906.148..27689.258 rows=85695 loops=1)"
" Sort Key: a.locationid"
" -> Merge Right Join (cost=38119.24..39307.55 rows=85611 width=74) (actual time=22236.915..25384.945 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=164.976..2290.760 rows=5662 loops=1)"
" Filter: (1 = presentationid)"
" -> Sort (cost=38119.24..38333.26 rows=85611 width=52) (actual time=20667.645..21031.627 rows=99139 loops=1)"
" Sort Key: (a.clientnum)::text, a.jobtitleid"
" -> Seq Scan on tblassociate a (cost=0.00..31105.34 rows=85611 width=52) (actual time=14.768..16024.395 rows=99139 loops=1)"
" Filter: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 30319.859 ms"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jfradkin@wazagua.com, mike@fuhr.org
Subject: Re: MSSQL versus Postgres timing
In-Reply-To: <000101c50893$e25da5d0$797ba8c0@jfradkin>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox