Обсуждение: BUG #12963: WHERE constraints on (INNER) JOIN columns are not propagated to both tables
BUG #12963: WHERE constraints on (INNER) JOIN columns are not propagated to both tables
От
gcp@sjeng.org
Дата:
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