Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #894

Postgresql database behaving unpredictably

Show key headers only | View raw

Hello - I am running an application on postgresql 9.5 that features a 515 million record table. The following query used to run in under 10 seconds 

select  f.patient_num
from i2b2demodata.observation_fact f
where
f.concept_cd IN (select concept_cd from  i2b2demodata.concept_dimension   where concept_path LIKE '\\ACT\\Diagnosis\\ICD10\\V2_2018AA\\A20098492\\A18916316\\A27150507\\A17864418\\%')
group by  f.patient_num

The DDL for the large table is at the bottom

Overnight the query is starting to run in 4 plus minutes

------------ 

so I migrated the database to postgres 12 and now the query runs in a shade over 2 minutes

Is there something that may have happened to the database? Is there anything that I can do to tweak the database?
------------------------------
-- Table: i2b2demodata.observation_fact

-- DROP TABLE i2b2demodata.observation_fact;

CREATE TABLE i2b2demodata.observation_fact
(
  encounter_num integer NOT NULL,
  patient_num integer NOT NULL,
  concept_cd character varying(50) NOT NULL,
  provider_id character varying(50) NOT NULL,
  start_date timestamp without time zone NOT NULL,
  modifier_cd character varying(100) NOT NULL DEFAULT '@'::character varying,
  instance_num integer NOT NULL DEFAULT 1,
  valtype_cd character varying(50),
  tval_char character varying(255),
  nval_num numeric(18,5),
  valueflag_cd character varying(50),
  quantity_num numeric(18,5),
  units_cd character varying(50),
  end_date timestamp without time zone,
  location_cd character varying(50),
  observation_blob text,
  confidence_num numeric(18,5),
  update_date timestamp without time zone,
  download_date timestamp without time zone,
  import_date timestamp without time zone,
  sourcesystem_cd character varying(50),
  upload_id integer,
  text_search_index serial NOT NULL,
  CONSTRAINT observation_fact_pk PRIMARY KEY (patient_num, concept_cd, modifier_cd, start_date, encounter_num, instance_num, provider_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE i2b2demodata.observation_fact
  OWNER TO i2b2demodata;

-- Index: i2b2demodata.of_idx_allobservation_fact

-- DROP INDEX i2b2demodata.of_idx_allobservation_fact;

CREATE INDEX of_idx_allobservation_fact
  ON i2b2demodata.observation_fact
  USING btree
  (patient_num, encounter_num, concept_cd COLLATE pg_catalog."default", start_date, provider_id COLLATE pg_catalog."default", modifier_cd COLLATE pg_catalog."default", instance_num, valtype_cd COLLATE pg_catalog."default", tval_char COLLATE pg_catalog."default", nval_num, valueflag_cd COLLATE pg_catalog."default", quantity_num, units_cd COLLATE pg_catalog."default", end_date, location_cd COLLATE pg_catalog."default", confidence_num);

-- Index: i2b2demodata.of_idx_clusteredconcept

-- DROP INDEX i2b2demodata.of_idx_clusteredconcept;

CREATE INDEX of_idx_clusteredconcept
  ON i2b2demodata.observation_fact
  USING btree
  (concept_cd COLLATE pg_catalog."default");

-- Index: i2b2demodata.of_idx_encounter_patient

-- DROP INDEX i2b2demodata.of_idx_encounter_patient;

CREATE INDEX of_idx_encounter_patient
  ON i2b2demodata.observation_fact
  USING btree
  (encounter_num, patient_num, instance_num);

-- Index: i2b2demodata.of_idx_modifier

-- DROP INDEX i2b2demodata.of_idx_modifier;

CREATE INDEX of_idx_modifier
  ON i2b2demodata.observation_fact
  USING btree
  (modifier_cd COLLATE pg_catalog."default");

-- Index: i2b2demodata.of_idx_sourcesystem_cd

-- DROP INDEX i2b2demodata.of_idx_sourcesystem_cd;

CREATE INDEX of_idx_sourcesystem_cd
  ON i2b2demodata.observation_fact
  USING btree
  (sourcesystem_cd COLLATE pg_catalog."default");

-- Index: i2b2demodata.of_idx_start_date

-- DROP INDEX i2b2demodata.of_idx_start_date;

CREATE INDEX of_idx_start_date
  ON i2b2demodata.observation_fact
  USING btree
  (start_date, patient_num);

-- Index: i2b2demodata.of_idx_uploadid

-- DROP INDEX i2b2demodata.of_idx_uploadid;

CREATE INDEX of_idx_uploadid
  ON i2b2demodata.observation_fact
  USING btree
  (upload_id);

-- Index: i2b2demodata.of_text_search_unique

-- DROP INDEX i2b2demodata.of_text_search_unique;

CREATE UNIQUE INDEX of_text_search_unique
  ON i2b2demodata.observation_fact
  USING btree
  (text_search_index);

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar

Thread

Postgresql database behaving unpredictably shariqtariq <shariqatariq@gmail.com> - 2021-01-14 17:08 -0800
  Re: Postgresql database behaving unpredictably George Neuner <gneuner2@comcast.net> - 2021-01-15 16:53 -0500
    Re: Postgresql database behaving unpredictably Mladen Gogala <mgogala@yahoo.com> - 2021-01-21 04:15 +0000
      Re: Postgresql database behaving unpredictably George Neuner <gneuner2@comcast.net> - 2021-01-21 14:06 -0500

csiph-web