Re: 9.6 query slower than 9.5.3

Поиск
Список
Период
Сортировка
От Adam Brusselback
Тема Re: 9.6 query slower than 9.5.3
Дата
Msg-id CAMjNa7d3RQBzeH7KEsX+yrOGXkVQ0XZrs_-MLp0qQye-oD53+A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: 9.6 query slower than 9.5.3  (Adam Brusselback <adambrusselback@gmail.com>)
Ответы Re: 9.6 query slower than 9.5.3  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
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@gmail.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@sss.pgh.pa.us> wrote:
Adam Brusselback <adambrusselback@gmail.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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Adam Brusselback
Дата:
Сообщение: Re: 9.6 query slower than 9.5.3
Следующее
От: Tom Lane
Дата:
Сообщение: Re: 9.6 query slower than 9.5.3