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.
