Slow query with big tables

Hello, 

I have the following tables and query. I would like to get some help to find out why it is slow and how its performance could be improved.

Thanks,

Tommi K.

CREATE TABLE "Measurement"

(

  id bigserial NOT NULL,

  product_id bigserial NOT NULL,

  nominal_data_id bigserial NOT NULL,

  description text,

  serial text,

  measurement_time timestamp without time zone,

  status smallint,

  system_description text,

  CONSTRAINT "Measurement_pkey" PRIMARY KEY (id),

  CONSTRAINT "Measurement_nominal_data_id_fkey" FOREIGN KEY (nominal_data_id)

      REFERENCES "Nominal_data" (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT "Measurement_product_id_fkey" FOREIGN KEY (product_id)

      REFERENCES "Product" (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

  OIDS=FALSE

);

CREATE INDEX measurement_time_index

  ON "Measurement"

  USING btree

  (measurement_time);

ALTER TABLE "Measurement" CLUSTER ON measurement_time_index;

CREATE TABLE "Product"

(

  id bigserial NOT NULL,

  name text,

  description text,

  info text,

  system_name text,

  CONSTRAINT "Product_pkey" PRIMARY KEY (id)

)

WITH (

  OIDS=FALSE

);

CREATE TABLE "Extra_info"

(

  id bigserial NOT NULL,

  measurement_id bigserial NOT NULL,

  name text,

  description text,

  info text,

  type text,

  value_string text,

  value_double double precision,

  value_integer bigint,

  value_bool boolean,

  CONSTRAINT "Extra_info_pkey" PRIMARY KEY (id),

  CONSTRAINT "Extra_info_measurement_id_fkey" FOREIGN KEY (measurement_id)

      REFERENCES "Measurement" (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

  OIDS=FALSE

);

CREATE INDEX extra_info_measurement_id_index

  ON "Extra_info"

  USING btree

  (measurement_id);

CREATE TABLE "Feature"

(

  id bigserial NOT NULL,

  measurement_id bigserial NOT NULL,

  name text,

  description text,

  info text,

  CONSTRAINT "Feature_pkey" PRIMARY KEY (id),

  CONSTRAINT "Feature_measurement_id_fkey" FOREIGN KEY (measurement_id)

      REFERENCES "Measurement" (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

  OIDS=FALSE

);

CREATE INDEX feature_measurement_id_and_name_index

  ON "Feature"

  USING btree

  (measurement_id, name COLLATE pg_catalog."default");

CREATE INDEX feature_measurement_id_index

  ON "Feature"

  USING hash

  (measurement_id);

CREATE TABLE "Point"

(

  id bigserial NOT NULL,

  feature_id bigserial NOT NULL,

  x double precision,

  y double precision,

  z double precision,

  status_x smallint,

  status_y smallint,

  status_z smallint,

  difference_x double precision,

  difference_y double precision,

  difference_z double precision,

  CONSTRAINT "Point_pkey" PRIMARY KEY (id),

  CONSTRAINT "Point_feature_id_fkey" FOREIGN KEY (feature_id)

      REFERENCES "Feature" (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

  OIDS=FALSE

);

CREATE INDEX point_feature_id_index

  ON "Point"

  USING btree

  (feature_id);

CREATE TABLE "Warning"

(

  id bigserial NOT NULL,

  feature_id bigserial NOT NULL,

  "number" smallint,

  info text,

  CONSTRAINT "Warning_pkey" PRIMARY KEY (id),

  CONSTRAINT "Warning_feature_id_fkey" FOREIGN KEY (feature_id)

      REFERENCES "Feature" (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

  OIDS=FALSE

);

CREATE INDEX warning_feature_id_index

  ON "Warning"

  USING btree

  (feature_id);

SELECT

f.name, 

f.description,

SUM(CASE WHEN p.status_x = 0 AND p.status_y = 0 AND p.status_z = 0 AND warning.id IS NULL THEN 1 ELSE 0 END) AS green_count, 

SUM(CASE WHEN p.status_x = 0 AND p.status_y = 0 AND p.status_z = 0 AND warning.id IS NOT NULL THEN 1 ELSE 0 END) AS green_warned_count,

SUM(CASE WHEN NOT (p.status_x = 0 AND p.status_y = 0 AND p.status_z = 0) AND NOT (p.status_x = 2 OR p.status_y = 2 OR p.status_z = 2) AND warning.id IS NULL THEN 1 ELSE 0 END) AS yellow_count, 

SUM(CASE WHEN NOT (p.status_x = 0 AND p.status_y = 0 AND p.status_z = 0) AND NOT (p.status_x = 2 OR p.status_y = 2 OR p.status_z = 2) AND warning.id IS NOT NULL THEN 1 ELSE 0 END) AS yellow_warned_count, 

SUM(CASE WHEN (p.status_x = 2 OR p.status_y = 2 OR p.status_z = 2) AND warning.id IS NULL THEN 1 ELSE 0 END) AS red_count, 

SUM(CASE WHEN (p.status_x = 2 OR p.status_y = 2 OR p.status_z = 2) AND warning.id IS NOT NULL THEN 1 ELSE 0 END) AS red_warned_count, 

SUM(CASE WHEN (p.status_x = 1000 OR p.status_y = 1000 OR p.status_z = 1000) AND warning.id IS NOT NULL THEN 1 ELSE 0 END) AS unable_to_measure_count 

FROM "Point" p 

JOIN "Feature" f ON f.id = p.feature_id

JOIN "Measurement" measurement ON measurement.id = f.measurement_id 

JOIN "Product" product ON product.id = measurement.product_id 

LEFT JOIN "Warning" warning ON f.id = warning.feature_id

WHERE (product.name ILIKE 'Part 1') AND 

measurement.measurement_start_time >= '2015-06-18 17:00:00' AND 

measurement.measurement_start_time <= '2015-06-18 18:00:00' AND 

measurement.id NOT IN(SELECT measurement_id FROM "Extra_info" e 

WHERE e.measurement_id = measurement.id AND e.description = 'Clamp' AND e.value_string ILIKE 'Clamped%')

 GROUP BY f.name, f.description;

GroupAggregate  (cost=1336999.08..1337569.18 rows=5562 width=33) (actual time=6223.622..6272.321 rows=255 loops=1)

  Buffers: shared hit=263552 read=996, temp read=119 written=119

  ->  Sort  (cost=1336999.08..1337012.98 rows=5562 width=33) (actual time=6223.262..6231.106 rows=26265 loops=1)

        Sort Key: f.name, f.description

        Sort Method: external merge  Disk: 936kB

        Buffers: shared hit=263552 read=996, temp read=119 written=119

        ->  Nested Loop Left Join  (cost=0.00..1336653.08 rows=5562 width=33) (actual time=55.792..6128.875 rows=26265 loops=1)

              Buffers: shared hit=263552 read=996

              ->  Nested Loop  (cost=0.00..1220487.17 rows=5562 width=33) (actual time=55.773..5910.852 rows=26265 loops=1)

                    Buffers: shared hit=182401 read=954

                    ->  Nested Loop  (cost=0.00..22593.53 rows=8272 width=27) (actual time=30.980..3252.869 rows=38831 loops=1)

                          Buffers: shared hit=972 read=528

                          ->  Nested Loop  (cost=0.00..657.24 rows=22 width=8) (actual time=0.102..109.577 rows=103 loops=1)

                                Join Filter: (measurement.product_id = product.id)

                                Rows Removed by Join Filter: 18

                                Buffers: shared hit=484 read=9

                                ->  Seq Scan on "Product" product  (cost=0.00..1.04 rows=1 width=8) (actual time=0.010..0.019 rows=1 loops=1)

                                      Filter: (name ~~* 'Part 1'::text)

                                      Rows Removed by Filter: 2

                                      Buffers: shared hit=1

                                ->  Index Scan using measurement_start_time_index on "Measurement" measurement  (cost=0.00..655.37 rows=67 width=16) (actual time=0.042..109.416 rows=121 loops=1)

                                      Index Cond: ((measurement_start_time >= '2015-06-18 17:00:00'::timestamp without time zone) AND (measurement_start_time <= '2015-06-18 18:00:00'::timestamp without time zone))

                                      Filter: (NOT (SubPlan 1))

                                      Buffers: shared hit=483 read=9

                                      SubPlan 1

                                        ->  Index Scan using extra_info_measurement_id_index on "Extra_info" e  (cost=0.00..9.66 rows=1 width=8) (actual time=0.900..0.900 rows=0 loops=121)

                                              Index Cond: (measurement_id = measurement.id)

                                              Filter: ((value_string ~~* 'Clamped%'::text) AND (description = 'Clamp'::text))

                                              Rows Removed by Filter: 2

                                              Buffers: shared hit=479 read=7

                          ->  Index Scan using feature_measurement_id_and_name_index on "Feature" rf  (cost=0.00..993.40 rows=370 width=35) (actual time=28.152..30.407 rows=377 loops=103)

                                Index Cond: (measurement_id = measurement.id)

                                Buffers: shared hit=488 read=519

                    ->  Index Scan using point_feature_id_index on "Point" p  (cost=0.00..144.80 rows=1 width=14) (actual time=0.067..0.067 rows=1 loops=38831)

                          Index Cond: (feature_id = f.id)

                          Buffers: shared hit=181429 read=426

              ->  Index Scan using warning_feature_id_index on "Warning" warning  (cost=0.00..20.88 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=26265)

                    Index Cond: (f.id = feature_id)

                    Buffers: shared hit=81151 read=42

Total runtime: 6273.312 ms

PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit

Extra_info 1223400 rows

Feature 185436000 rows

Measurement 500000 rows

Point 124681000 rows

Warning 11766800 rows

Intel Core i5-2320 CPU 3.00GHz (4 CPUs)

6GB Memory

64-bit Operating System (Windows 7 Professional)

WD Blue 500GB HDD - 7200 RPM SATA 6 Gb/s 16MB Cache

Query gets slower as more data is added to the database

Autovacuum is used with default settings