Re: 9.6 query slower than 9.5.3

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(at)gmail(dot)com> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Adam Brusselback <adambrusselback(at)gmail(dot)com> 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
>>
>
>