Thread: Joins on over 10 tables returns unreliable results

Jeff Seaman (jeffery.l.seaman@intel.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Joins on over 10 tables returns unreliable results

Long Description
Running Postgres version 7.01 on Redhat linux 6.2.  When joining on more than 10 tables not all of the expected results
arereturned.  The query can be repeated several times, with different result sets when the data itself is static.  The
mostcommon result is the return of only one record.  11 tables seems to be the magic limit.  Joins on 10 tables works
withouta hitch.  This join was based on a column id that exists in all of the tables. 

Sample Code


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:

> Running Postgres version 7.01 on Redhat linux 6.2.  When joining on
> more than 10 tables not all of the expected results are returned.
> The query can be repeated several times, with different result sets
> when the data itself is static.  The most common result is the return
> of only one record.  11 tables seems to be the magic limit.  Joins on
> 10 tables works without a hitch.  This join was based on a column id
> that exists in all of the tables.

This seems to be a problem in the genetic query optimizer.  But to examine
closer we'd really need to get sample data and queries to reproduce this.

For now you can SET geqo TO 'off'; to disable this.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

pgsql-bugs@postgresql.org writes:
> Running Postgres version 7.01 on Redhat linux 6.2.  When joining on
> more than 10 tables not all of the expected results are returned.

Do you get a query plan involving mergejoins for these queries?
If so it's probably fixed for 7.0.3, please try that as soon as
it's out...

            regards, tom lane