Re: BUG #13908: Query returns too few rows

"barrid" is "character varying(8) NOT NULL", and some examples are 'CANCEJ1' and 'USA06Z1'.

In case it helps, the following are the table definitions and all the constraints and indices I have on the three tables:

files.models_direct_file ("fff")

CREATE TABLE files.models_direct_file
(
idx serial NOT NULL,
file_class character varying(32) NOT NULL,
file_name character varying(64) NOT NULL,
file_name_data character varying(32) NOT NULL,
file_name_date date,
header_columns character varying(32)[],
file_created timestamp without time zone,
file_imported_to_db timestamp without time zone,
num_rows integer,
file_name_model character varying(8) NOT NULL,
file_name_model_horizon character varying(1) NOT NULL,
file_name_exchange character varying(8) NOT NULL,
file_name_etf character varying(12) NOT NULL,
file_name_model_forecast_horizon character varying(1) NOT NULL,
file_name_model_speed character varying(1) NOT NULL,
file_name_model_ver character varying(3) NOT NULL,
header_vendor character varying(16),
header_model_ver character varying(3),
header_release_date date,
header_release_datetime timestamp without time zone,
header_last_modified_date date,
header_bim_ver character varying(4),
header_bimef_ver character varying(4),
CONSTRAINT pk_models_direct_file PRIMARY KEY (idx)
)
WITH (
OIDS=FALSE
);

CREATE INDEX ix_models_direct_file_file_name_date
ON files.models_direct_file
USING btree
(file_name_date);

CREATE UNIQUE INDEX ix_models_direct_file_unique
ON files.models_direct_file
USING btree
(file_name COLLATE pg_catalog."default");

files.models_direct_row_asset_data ("rrr")

CREATE TABLE files.models_direct_row_asset_data
(
idx serial NOT NULL,
row_number integer NOT NULL,
barrid character varying(8) NOT NULL,
yield_pct double precision,
total_risk_pct double precision,
spec_risk_pct double precision,
hist_beta double precision,
pred_beta double precision,
data_date date NOT NULL,
barra_file_idx integer NOT NULL,
CONSTRAINT pk_models_direct_row_asset_data PRIMARY KEY (idx),
CONSTRAINT fk_models_direct_row_asset_data_barra_file_idx_models_direct_fi FOREIGN KEY (barra_file_idx)
REFERENCES files.models_direct_file (idx) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=FALSE
);

CREATE INDEX ix_models_direct_row_asset_data_barrid
ON files.models_direct_row_asset_data
USING btree
(barrid COLLATE pg_catalog."default");

CREATE INDEX ix_models_direct_row_asset_data_data_date
ON files.models_direct_row_asset_data
USING btree
(data_date);

CREATE UNIQUE INDEX ix_models_direct_row_asset_data_unique
ON files.models_direct_row_asset_data
USING btree
(barra_file_idx, row_number);

temp_universe_instruments ("uuu")

CREATE TABLE files.temp_universe_instruments
(
universe_hash bigserial NOT NULL,
barrid character varying(8) NOT NULL,
CONSTRAINT pk_temp_universe_instruments PRIMARY KEY (universe_hash, barrid)
)
WITH (
OIDS=FALSE
);

no indices

________________________________
From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Tuesday, February 2, 2016 10:04 PM
To: Seth P
Cc: Tom Lane; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #13908: Query returns too few rows

On Tue, Feb 2, 2016 at 7:05 PM, Seth P <seth-p(at)outlook(dot)com<mailto:seth-p(at)outlook(dot)com>> wrote:

Below are the EXPLAIN ANALYZE results. I will try to reproduce the problem with isolated/toy data, but that may take a while.

Nothing obvious...what type of column is "barrid" and, if it is indeed textual, can you provide some example values?

David J.