Performance of complicated query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am fairly new to squeezing performance out of Postgres, but I hope this mailing list can help me.  I have read the instructions found at http://wiki.postgresql.org/wiki/Slow_Query_Questions and have tried to abide by them the best that I can.  I am running "PostgreSQL 9.1.7, compiled by Visual C++ build 1500, 64-bit" on an x64 Windows 7 Professional Service Pack 1 machine with 8 GB of RAM.  I installed this using the downloadable installer.  I am testing this using pgAdminIII but ultimately this will be deployed within a Rails application.  Here are the values of some configuration parameters:

shared_buffers = 1GB
temp_buffers = 8MB
work_mem = 10MB
maintenance_work_mem = 256MB
random_page_cost = 1.2
default_statistics_target = 10000

Table schema:

reads-- ~250,000 rows
CREATE TABLE reads
(
  id serial NOT NULL,
  device_id integer NOT NULL,
  value bigint NOT NULL,
  read_datetime timestamp without time zone NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT reads_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE reads
  OWNER TO postgres;

CREATE INDEX index_reads_on_device_id
  ON reads
  USING btree
  (device_id );

CREATE INDEX index_reads_on_device_id_and_read_datetime
  ON reads
  USING btree
  (device_id , read_datetime );

CREATE INDEX index_reads_on_read_datetime
  ON reads
  USING btree
  (read_datetime );

devices -- ~25,000 rows
CREATE TABLE devices
(
  id serial NOT NULL,
  serial_number character varying(20) NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT devices_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE devices
  OWNER TO postgres;

CREATE UNIQUE INDEX index_devices_on_serial_number
  ON devices
  USING btree
  (serial_number COLLATE pg_catalog."default" );

patient_devices -- ~25,000 rows
CREATE TABLE patient_devices
(
  id serial NOT NULL,
  patient_id integer NOT NULL,
  device_id integer NOT NULL,
  issuance_datetime timestamp without time zone NOT NULL,
  unassignment_datetime timestamp without time zone,
  issued_value bigint NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT patient_devices_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE patient_devices
  OWNER TO postgres;

CREATE INDEX index_patient_devices_on_device_id
  ON patient_devices
  USING btree
  (device_id );

CREATE INDEX index_patient_devices_on_issuance_datetime
  ON patient_devices
  USING btree
  (issuance_datetime );

CREATE INDEX index_patient_devices_on_patient_id
  ON patient_devices
  USING btree
  (patient_id );

CREATE INDEX index_patient_devices_on_unassignment_datetime
  ON patient_devices
  USING btree
  (unassignment_datetime );

patients -- ~1000 rows
CREATE TABLE patients
(
  id serial NOT NULL,
  first_name character varying(50) NOT NULL,
  last_name character varying(50) NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT patients_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE patients
  OWNER TO postgres;

Finally, this is the query I am running:

SELECT first_name, last_name, serial_number, latest_read, value, lifetime_value, lifetime.patient_id
FROM (
SELECT DISTINCT patient_id, first_name, last_name, MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read, SUM(value) OVER(PARTITION BY patient_id) AS value, first_value(serial_number) OVER(PARTITION BY patient_id ORDER BY max_read DESC) AS serial_number
FROM (
SELECT patient_id, first_name, last_name, value - issued_value AS value, serial_number, read_datetime, MAX(read_datetime) OVER (PARTITION BY patient_devices.id) AS max_read
FROM reads
INNER JOIN devices ON devices.id = reads.device_id
INNER JOIN patient_devices ON patient_devices.device_id = devices.id
AND read_datetime >= issuance_datetime
AND read_datetime < COALESCE(unassignment_datetime , 'infinity'::timestamp)
INNER JOIN patients ON patients.id = patient_devices.patient_id
WHERE read_datetime BETWEEN '2012-01-01 10:30:01' AND '2013-05-18 03:03:42'
) AS first WHERE read_datetime = max_read
) AS filtered
INNER JOIN (
SELECT DISTINCT patient_id, SUM(value) AS lifetime_value
FROM (
SELECT patient_id, value - issued_value AS value, read_datetime, MAX(read_datetime) OVER (PARTITION BY patient_devices.id) AS max_read
FROM reads
INNER JOIN devices ON devices.id = reads.device_id
INNER JOIN patient_devices ON patient_devices.device_id = devices.id
AND read_datetime >= issuance_datetime
AND read_datetime < COALESCE(unassignment_datetime , 'infinity'::timestamp)
) AS first WHERE read_datetime = max_read GROUP BY patient_id
) AS lifetime ON filtered.patient_id = lifetime.patient_id

The EXPLAIN (ANALYZE, BUFFERS) output can be found at the following link http://explain.depesz.com/s/7Zr.  Ultimately what I want to do is to find a sum of values for each patient.  The scenario is that each patient is assigned a device and they get incremental values on their device.  Since these values are incremental if a patient never switches devices, the reported value should be the last value for a patient.  However, if a patient switches devices then the reported value should be the sum of the last value for each device that the patient was assigned.  This leads to the conditions read_datetime >= issuance_datetime AND read_datetime < COALESCE(unassignment_datetime , 'infinity'::timestamp).  In addition I must report the serial number of the last device that the patient was assigned (or is currently assigned).  The only way I could come up with doing that is first_value(serial_number) OVER(PARTITION BY patient_id ORDER BY max_read DESC) AS serial_number.  Finally, I must report 2 values, one with respect to a time range and one which is the lifetime value.  In order to satisfy this requirement, I have to run essentially the same query twice (one with the WHERE time clause and one without) and INNER JOIN the results.  My questions are

1.  Can I make the query as I have constructed it faster by adding indices or changing any postgres configuration parameters?
2.  Can I modify the query to return the same results in a faster way?
3.  Can I modify my tables to make this query (which is the crux of my application) run faster?

Thanks

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux