pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hugo Ferreira <bytter@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer
Date: Mon, 07 Mar 2005 13:02:07 -0500
Message-ID: <3276.1110218527@sss.pgh.pa.us> (raw)
In-Reply-To: <4e8efcf50503070945332778bb@mail.gmail.com>
References: <20050304002724.650A95AF649@svr4.postgresql.org>
	<4e8efcf50503040413245d80f9@mail.gmail.com>
	<4e8efcf50503070901406808be@mail.gmail.com>
	<200503070928.47118.josh@agliodbs.com>
	<4e8efcf50503070945332778bb@mail.gmail.com>

Hugo Ferreira <bytter@gmail.com> writes:
> SELECT 514, 10000168,  C.contxt_id, C.contxt_elmt_ix, null, null,
> null, null, null, null, 1
> FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id = C.contxt_id
>        INNER JOIN MRS_REPLICATION_OUT as S on S.ent_id=10000029
>                   AND P.contxt_id = S.pk1
>        INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514
>        LEFT  JOIN ON_REPL_DATA_OWNER NRDO on
> NRDO.non_repl_data_owner_id = C.owner_id
>        LEFT  JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id = C.owner_id
>                   AND RDOR.rsdnc_node_id=TRANS.recv_node_id
>        LEFT  JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id = 514
>                   AND OUT.ent_id=10000168 and C.contxt_id = OUT.pk1
>                   AND C.contxt_elmt_ix = OUT.pk2
>        INNER JOIN MRS_TRANSACTION RED_TRANS on
> TRANS.prov_node_id=RED_TRANS.prov_node_id
>                   AND TRANS.recv_node_id=RED_TRANS.recv_node_id
>        LEFT  JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT'
>                   AND RED_TRANS.trans_type in ('X01', 'X02')
>                   AND RED_TRANS.trans_id = RED_OUT.trans_id

I think the problem is that the intermix of inner and left joins forces
Postgres to do the joins in a particular order, per
http://www.postgresql.org/docs/8.0/static/explicit-joins.html
and this order is quite non optimal for your data.  In particular it
looks like joining red_trans to red_out first, instead of last,
would be a good idea (I think but am not 100% certain that this
doesn't change the results).

It is possible but complicated to determine that reordering outer joins
is safe in some cases.  We don't currently have such logic in PG.  It
may be that SQL Server does have that capability and that's why it's
finding a much better plan ... but for now you have to do that by hand
in PG.

			regards, tom lane



view thread (7+ messages)  latest in thread

Message-ID: <3276.1110218527@sss.pgh.pa.us>
Permalink:  ../3276.1110218527@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3276.1110218527@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, bytter@gmail.com, josh@agliodbs.com
  Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer
  In-Reply-To: <3276.1110218527@sss.pgh.pa.us>

* 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