Performance of complicated query - Mailing list pgsql-performance

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