I have an analytics database on Postgres 9.5 I'm taking advantage of constraint partitioning with table inheritance to split up events into monthly tables. The base (parent) table contains no rows.
I have a relatively simple query but the planner is coming up with a completely insane plan where it materializes within a nested loop as can be seen from the explain.
The query
select count(person_id) as thecount from (
select distinct A.person_id from event_page as O
join alias as A on (O.person_alias = A.alias)
where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'
and O.location_host = 'www.foo.com' AND O.location_path= '/ca/sale'
) as alias_3494697
The explain: https://explain.depesz.com/s/IoO
My theory is that table inheritance with constraint partitioning is tripping up the planner somehow. If I replace the base table with the monthly table for April I get a sane plan with good performance:
https://explain.depesz.com/s/GpbU
NOTE The slight difference from the above query - i'm using the child table instead of the base table (ie no inheritance at play)
select count(person_id) as thecount from (
select distinct A.person_id from event_page_2017_4 as O
join alias as A on (O.person_alias = A.alias)
where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'
and O.location_host = 'www.foo.com' AND O.location_path= '/ca/sale'
) as alias_3494697
All stats are up to date via vacuum analyze. The relevant schema is below:
CREATE TABLE event_page (
id CHAR(24) PRIMARY KEY,
timestamp_ TIMESTAMP NOT NULL,
person_alias VARCHAR(128) NOT NULL,
visitor_id VARCHAR(128) NOT NULL,
session_id VARCHAR(24) NOT NULL,
ip_address VARCHAR(64) ,
user_agent VARCHAR(256) ,
operating_system VARCHAR(64) ,
device_type VARCHAR(64) ,
browser VARCHAR(64) ,
browser_major VARCHAR(64) ,
page_title VARCHAR(1024) ,
location_host VARCHAR(256) ,
location_path VARCHAR(1024) ,
location_query VARCHAR(1024) ,
location_fragment VARCHAR(1024) ,
referrer_host VARCHAR(256) ,
referrer_path VARCHAR(1024) ,
referrer_query VARCHAR(1024) ,
referrer_fragment VARCHAR(1024) ,
duration INT
);
--the monthly tables all look like this example for April 2017
CREATE TABLE event_page_2017_4
(LIKE event_page including defaults including constraints including indexes,
CONSTRAINT page_2017_4_part CHECK ( timestamp_ >= '2017-4-01'::timestamp AND timestamp_ < '2017-05-01'::timestamp )
)
INHERITS (event_page);
--indexes are the sames on each of the monthly tables
CREATE INDEX ep_2017_4_location
ON event_page_2017_4
USING btree
(location_host varchar_pattern_ops, location_path varchar_pattern_ops, location_query varchar_pattern_ops, location_fragment varchar_pattern_ops, timestamp_, person_alias, session_id);
CREATE INDEX ep_2017_4_ts
ON event_page_2017_4
USING btree
(timestamp_, person_alias, session_id);
CREATE INDEX ep_2017_4_a_ses_ts
ON event_page_2017_4
USING btree
(person_alias, session_id, timestamp_);
--the alias tables and two indexes
CREATE TABLE alias (
person_id CHAR(24),
alias VARCHAR(128) NOT NULL,
first_seen TIMESTAMP,
soft BOOLEAN
);
ALTER TABLE ALIAS ADD CONSTRAINT alias_alias_pkey PRIMARY KEY(alias);
CREATE INDEX alias_a_p ON alias (alias, person_id);
CREATE INDEX alias_p_a ON alias (person_id, alias);
NOTE: The problem went away after upgrading to 9.6.1