Thread: BUG #12963: WHERE constraints on (INNER) JOIN columns are not propagated to both tables

The following bug has been logged on the website:

Bug reference:      12963
Logged by:          Gian-Carlo Pascutto
Email address:      gcp@sjeng.org
PostgreSQL version: 9.4.1
Operating system:   Linux
Description:

The relevant parts of the schema are as follows:

CREATE TABLE matches
(
  seq integer NOT NULL,
  start_time integer NOT NULL,
  <blah>
  CONSTRAINT matches_pkey PRIMARY KEY (seq)
)

CREATE TABLE matches_players
(
  seq integer NOT NULL,
  player_num smallint NOT NULL,
  account_id integer,
  <blah>
  CONSTRAINT matches_players_pkey PRIMARY KEY (seq, player_num),
  CONSTRAINT matches_players_seq_fkey FOREIGN KEY (seq)
      REFERENCES matches (seq) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

The following is the EXPLAIN ANALYZE output of 2 queries.
https://dl.dropboxusercontent.com/u/32496746/postgresql.txt

Because this is an (INNER) JOIN, a WHERE constraint that is given to one
table, on the joined column, applies to the other table as well.
PostgreSQL's query planner does not understand this and consequently
produces a query plan that is a factor 35 slower than when one restates the
constraint.

That is, when given:

        join matches on matches.seq = matches_players.seq
        where matches.seq > 1151575404 and matches.seq < 1155066769

there should be no need to add:

        and matches_players.seq > 1151575404 and matches_players.seq <
1155066769;

because this is implied by the JOIN ON.

gcp@sjeng.org writes:
> That is, when given:
>         join matches on matches.seq = matches_players.seq
>         where matches.seq > 1151575404 and matches.seq < 1155066769
> there should be no need to add:
>         and matches_players.seq > 1151575404 and matches_players.seq <
> 1155066769;
> because this is implied by the JOIN ON.

We do make deductions of this sort for equality constraints, but not for
inequalities.  There are no plans to teach the planner to do such
deductions.  Sorry, but for most queries trying to propagate inequalities
would just chew up planning cycles with little result.  The fact that when
it does apply it can be a big win doesn't convince me that it wouldn't be
a net loss overall.

            regards, tom lane