From: Mark Lewis <mark.lewis@mir3.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Hardman <peter@ssbg.zetnet.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL runs a query much slower than BDE and
Date: Thu, 17 Aug 2006 06:54:00 -0700
Message-ID: <1155822840.16967.238.camel@archimedes> (raw)
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>
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
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-performance@postgresql.org
Cc: mark.lewis@mir3.com, tgl@sss.pgh.pa.us, peter@ssbg.zetnet.co.uk
Subject: Re: PostgreSQL runs a query much slower than BDE and
In-Reply-To: <1155822840.16967.238.camel@archimedes>
* 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