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 const=
raints 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=3DFALSE
);


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_f=
i FOREIGN KEY (barra_file_idx)
      REFERENCES files.models_direct_file (idx) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
  OIDS=3DFALSE
);


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, barri=
d)
)
WITH (
  OIDS=3DFALSE
);


no indices


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

On Tue, Feb 2, 2016 at 7:05 PM, Seth P <seth-p@outlook.com<mailto:seth-p@ou=
tlook.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 text=
ual, can you provide some example values?


David J.