From: Simon Riggs <simon@2ndquadrant.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Outer joins and equivalence
Date: Tue, 27 May 2008 20:59:18 +0100
Message-ID: <1211918358.4489.257.camel@ebony.site> (raw)
I have a complex query where making a small change to the SQL increases
run-time by > 1000 times.
The first SQL statement is of the form
A JOIN B ON (a.id = b.id) LEFT JOIN C ON (a.id = c.id)
and the second is like this
A JOIN B ON (a.id = b.id) LEFT JOIN C ON (b.id = c.id)
the only difference is the substitution of a -> b
This has been verified by examining EXPLAIN of SQL1, SQL2 and SQL1. The
first and third EXPLAINs are equivalent. All ANALYZE etc has been run.
All relevant join columns are INTEGERs. So we have a repeatable
difference in plans attributable to a single change.
The difference in run time occurs because the second form of the query
uses a SeqScan of a large table, whereas the first form is able to use a
nested loops join to access the large table, which then allows it to
access just 3 rows rather than 85 million rows.
There is a clear equivalence between the two forms of SQL, since the
equivalence a = b is derived from a natural rather than an outer join.
This can be applied from the left side to the right side of the join.
So this looks to me like either a bug or just an un-implemented
optimizer feature. The code I've just looked at for equivalent class
relationships appears to refer to using this to propagate constant info
only, so I'm thinking it is not a bug. and hence why it is reported here
and not to pgsql-bugs.
I do recognise that we would *not* be able to deduce this form of SQL
A JOIN B ON (a.id = c.id) LEFT JOIN C ON (b.id = c.id)
though that restriction on outer join equivalence is not relevant here.
(SQL, EXPLAINs etc available off-list only, by request).
I'm looking into this more now.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
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: simon@2ndquadrant.com
Subject: Re: Outer joins and equivalence
In-Reply-To: <1211918358.4489.257.camel@ebony.site>
* 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