X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 3A68A9FB31C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Aug 2006 10:54:16 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 23368-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Aug 2006 10:54:03 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mir3-fs.mir3.com (mail.mir3.com [65.208.188.100])
	by postgresql.org (Postfix) with ESMTP id 77DFF9FB274
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Aug 2006 10:54:04 -0300 (ADT)
Received: mir3-fs.mir3.com 172.16.1.11 from 172.16.2.68 172.16.2.68 via HTTP
	with MS-WebStorage 6.0.6249
Received: from archimedes.mirlogic.com by mir3-fs.mir3.com;
	17 Aug 2006 06:54:00 -0700
Subject: Re: PostgreSQL runs a query much slower than BDE and
From: Mark Lewis <mark.lewis@mir3.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Hardman <peter@ssbg.zetnet.co.uk>, pgsql-performance@postgresql.org
In-Reply-To: <5321.1155820310@sss.pgh.pa.us>
References: <44E35A5D.19933.35F376@peter.ssbg.zetnet.co.uk>
	<44E4430D.15373.6A0C60@peter.ssbg.zetnet.co.uk>
	<5321.1155820310@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Thu, 17 Aug 2006 06:54:00 -0700
Message-Id: <1155822840.16967.238.camel@archimedes>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-27) 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.379 tagged_above=0 required=5 tests=AWL,
	UNPARSEABLE_RELAY
X-Spam-Level: 
X-Archive-Number: 200608/282
X-Sequence-Number: 20467

MSSQL can give either a graphical query plan or a text-based one similar
to PG.  There's no way that I've found to get the equivalent of an
EXPLAIN ANALYZE, but I'm by no means an MSSQL guru.

To get a neat-looking but not very useful graphical query plan from the
Query Analyzer tool, hit <Ctrl-L>.

To get the text-based one, execute "SET SHOWPLAN_ALL ON" which toggles
diagnostic mode on, and each query that you run will return the explain
plan instead of actually running until you execute "SET SHOWPLAN_ALL
OFF".

-- Mark Lewis

On Thu, 2006-08-17 at 09:11 -0400, Tom Lane wrote:
> "Peter Hardman" <peter@ssbg.zetnet.co.uk> writes:
> > I wonder whether Paradox and MySQL are just not doing the sort (this
> > seems to be what eats up the time), since the output of the subquery
> > is in fact already in the proper order.
> 
> MSSQL (from the other thread).  I feel fairly safe in assuming that
> MySQL's query optimizer is not nearly in the league to do this query
> effectively.  (I like the theory Arjen mentioned that what you are
> measuring there is the effects of their query cache rather than a
> smart fundamental implementation.)  I wonder whether MSSQL has an
> EXPLAIN equivalent ...
> 
> Anywy, your point about the sort being redundant is a good one, and
> offhand I'd have expected PG to catch that; I'll have to look into
> why it didn't.  But that's not going to explain a 10x speed
> difference, because the sort isn't 90% of the runtime.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org