Gah, hit send too soon...
CREATE TEMPORARY TABLE _claims_to_process ( claim_id uuid, starting_state enum.claim_state );
CREATE TABLE claim_product
(
claim_product_id uuid NOT NULL DEFAULT gen_random_uuid(),
claim_id uuid NOT NULL,
product_id uuid NOT NULL,
uom_type_id uuid NOT NULL,
rebate_requested_quantity numeric NOT NULL,
rebate_requested_rate numeric NOT NULL,
rebate_allowed_quantity numeric NOT NULL,
rebate_allowed_rate numeric NOT NULL,
distributor_company_id uuid,
location_company_id uuid,
contract_item_id uuid,
claimant_contract_name character varying, -- NOT SOURCE OF TRUTH; Client defined. - Yesod
resolve_date date NOT NULL, -- FIXME: TENTATIVE NAME; Does not mean contract_item_id resolve date. - Yesod
rebate_calculated_rate numeric NOT NULL,
CONSTRAINT claim_product_pkey PRIMARY KEY (claim_product_id),
CONSTRAINT claim_product_claim_id_fkey FOREIGN KEY (claim_id)
REFERENCES claim (claim_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT claim_product_contract_item_id_fkey FOREIGN KEY (contract_item_id)
REFERENCES contract_item (contract_item_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT claim_product_distributor_company_id_fkey FOREIGN KEY (distributor_company_id)
REFERENCES company (company_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT claim_product_location_company_id_fkey FOREIGN KEY (location_company_id)
REFERENCES company (company_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT claim_product_product_id_fkey FOREIGN KEY (product_id)
REFERENCES product (product_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT claim_product_uom_type_id_fkey FOREIGN KEY (uom_type_id)
REFERENCES uom_type (uom_type_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE claim_product
OWNER TO root;
GRANT ALL ON TABLE claim_product TO root;
COMMENT ON COLUMN claim_product.claimant_contract_name IS 'NOT SOURCE OF TRUTH; Client defined. - Yesod';
COMMENT ON COLUMN claim_product.resolve_date IS 'FIXME: TENTATIVE NAME; Does not mean contract_item_id resolve date. - Yesod';
-- Index: idx_claim_product_claim_id
-- DROP INDEX idx_claim_product_claim_id;
CREATE INDEX idx_claim_product_claim_id
ON claim_product
USING btree
(claim_id);
-- Index: idx_claim_product_contract_item_id
-- DROP INDEX idx_claim_product_contract_item_id;
CREATE INDEX idx_claim_product_contract_item_id
ON claim_product
USING btree
(contract_item_id);
-- Trigger: claim_product_iud_trigger on claim_product
-- DROP TRIGGER claim_product_iud_trigger ON claim_product;
CREATE TRIGGER claim_product_iud_trigger
AFTER INSERT OR UPDATE OR DELETE
ON claim_product
FOR EACH ROW
EXECUTE PROCEDURE gosimple.claim_product_on_iud();
-- Trigger: claim_product_statement_trigger on claim_product
-- DROP TRIGGER claim_product_statement_trigger ON claim_product;
CREATE TRIGGER claim_product_statement_trigger
AFTER INSERT OR UPDATE OR DELETE
ON claim_product
FOR EACH STATEMENT
EXECUTE PROCEDURE gosimple.claim_product_statement_refresh_trigger();
CREATE TABLE claim_product_reason_code
(
claim_product_reason_code_id uuid NOT NULL DEFAULT gen_random_uuid(),
claim_product_id uuid NOT NULL,
claim_reason_type enum.claim_reason_type NOT NULL,
claim_reason_code enum.claim_reason_code NOT NULL,
claim_reason_note character varying,
active_range tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone),
CONSTRAINT claim_product_reason_code_pkey PRIMARY KEY (claim_product_reason_code_id),
CONSTRAINT claim_product_reason_code_claim_product_id_fkey FOREIGN KEY (claim_product_id)
REFERENCES claim_product (claim_product_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT claim_product_reason_code_active_range_excl EXCLUDE
USING gist (gosimple.uuid_to_bytea(claim_product_id) WITH =, gosimple.enum_to_oid('enum'::text, 'claim_reason_type'::text, claim_reason_type) WITH =, gosimple.enum_to_oid('enum'::text, 'claim_reason_code'::text, claim_reason_code) WITH =, active_range WITH &&),
CONSTRAINT claim_product_reason_code_excl EXCLUDE
USING gist (gosimple.uuid_to_bytea(claim_product_id) WITH =, (
CASE
WHEN upper(active_range) IS NULL THEN 'infinity'::text
ELSE NULL::text
END) WITH =, gosimple.enum_to_oid('enum'::text, 'claim_reason_type'::text, claim_reason_type) WITH <>),
CONSTRAINT claim_product_reason_code_unique UNIQUE (claim_product_id, claim_reason_type, claim_reason_code, active_range)
)
WITH (
OIDS=FALSE
);
ALTER TABLE claim_product_reason_code
OWNER TO root;
GRANT ALL ON TABLE claim_product_reason_code TO root;
-- Index: claim_product_reason_code_active_range_idx
-- DROP INDEX claim_product_reason_code_active_range_idx;
CREATE INDEX claim_product_reason_code_active_range_idx
ON claim_product_reason_code
USING btree
(claim_product_id, claim_reason_type)
WHERE upper_inf(active_range);
-- Index: claim_product_reason_code_not_pend_unique
-- DROP INDEX claim_product_reason_code_not_pend_unique;
CREATE UNIQUE INDEX claim_product_reason_code_not_pend_unique
ON claim_product_reason_code
USING btree
(claim_product_id, claim_reason_type)
WHERE upper(active_range) IS NULL AND claim_reason_type <> 'PEND'::enum.claim_reason_type;
-- Trigger: claim_product_reason_code_insert_trigger on claim_product_reason_code
-- DROP TRIGGER claim_product_reason_code_insert_trigger ON claim_product_reason_code;
CREATE TRIGGER claim_product_reason_code_insert_trigger
BEFORE INSERT
ON claim_product_reason_code
FOR EACH ROW
EXECUTE PROCEDURE gosimple.update_claim_product_reason_code_active_range();
On Thu, Jun 16, 2016 at 10:09 PM, Adam Brusselback <adambrusselback@xxxxxxxxx> wrote:
I analyzed all tables involved after loading, and also while trying to diagnose this issue.I have the same statistics target settings on both servers.Here are the schemas for the tables:On Thu, Jun 16, 2016 at 10:04 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Adam Brusselback <adambrusselback@xxxxxxxxx> writes:
> Hey all, testing out 9.6 beta 1 right now on Debian 8.5.
> I have a query that is much slower on 9.6 than 9.5.3.
The rowcount estimates in 9.6 seem way off. Did you ANALYZE the tables
after loading them into 9.6? Maybe you forgot some statistics target
settings?
If it's not that, I wonder whether the misestimates are connected to the
foreign-key-based estimation feature. Are there any FKs on the tables
involved? May we see the table schemas?
regards, tom lane
