Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bdNib-0006G3-9c for pgsql-performance@arkaria.postgresql.org; Fri, 26 Aug 2016 20:27:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bdNia-0006ji-Oe for pgsql-performance@arkaria.postgresql.org; Fri, 26 Aug 2016 20:27:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bdNiZ-0006iu-6j for pgsql-performance@postgresql.org; Fri, 26 Aug 2016 20:27:11 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bdNiU-00042j-Mv for pgsql-performance@postgresql.org; Fri, 26 Aug 2016 20:27:10 +0000 Received: from [10.9.9.211] (helo=mailfront11.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1bdNiT-0003n8-BY for pgsql-performance@postgresql.org; Fri, 26 Aug 2016 22:27:05 +0200 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront11.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1bdNiE-0003WM-5n for pgsql-performance@postgresql.org; Fri, 26 Aug 2016 22:26:51 +0200 From: "Mike Sofen" To: References: In-Reply-To: Subject: Re: Slow query with big tables Date: Fri, 26 Aug 2016 13:26:46 -0700 Message-ID: <00e601d1ffd8$2beb8850$83c298f0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E7_01D1FF9D.7F942A60" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJFnt9AqfQA4ue1aL9OsCnOW0bBZAItaE3tAKvd/eCfXacK0A== Content-Language: en-us X-Pg-Spam-Score: -2.9 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_00E7_01D1FF9D.7F942A60 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable =20 From: pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tommi K Sent: Friday, August 26, 2016 7:25 AM To: Craig James Cc: andreas kretschmer ; = pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query with big tables =20 Ok, sorry that I did not add the original message. I thought that it = would be automatically added to the message thread. =20 Here is the question again: =20 Is there way to keep query time constant as the database size grows. = Should I use partitioning or partial indexes? =20 Thanks, Tommi Kaksonen =20 =20 =20 > Hello,=20 >=20 > 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. >=20 > Thanks, > Tommi K. >=20 >=20 > --Table definitions--- > 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=3DFALSE > ); >=20 > CREATE INDEX measurement_time_index > ON "Measurement" > USING btree > (measurement_time); > ALTER TABLE "Measurement" CLUSTER ON measurement_time_index; >=20 > CREATE TABLE "Product" > ( > id bigserial NOT NULL, > name text, > description text, > info text, > system_name text, > CONSTRAINT "Product_pkey" PRIMARY KEY (id) > ) > WITH ( > OIDS=3DFALSE > ); >=20 >=20 > 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=3DFALSE > ); >=20 > CREATE INDEX extra_info_measurement_id_index > ON "Extra_info" > USING btree > (measurement_id); >=20 > 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=3DFALSE > ); >=20 > CREATE INDEX feature_measurement_id_and_name_index > ON "Feature" > USING btree > (measurement_id, name COLLATE pg_catalog."default"); >=20 > CREATE INDEX feature_measurement_id_index > ON "Feature" > USING hash > (measurement_id); >=20 >=20 > 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=3DFALSE > ); >=20 > CREATE INDEX point_feature_id_index > ON "Point" > USING btree > (feature_id); >=20 > 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=3DFALSE > ); >=20 > CREATE INDEX warning_feature_id_index > ON "Warning" > USING btree > (feature_id); >=20 >=20 > ---Query--- > SELECT > f.name ,=20 > f.description, > SUM(CASE WHEN p.status_x =3D 0 AND p.status_y =3D 0 AND p.status_z =3D = 0 AND warning.id IS NULL THEN 1 ELSE 0 END) AS = green_count,=20 > SUM(CASE WHEN p.status_x =3D 0 AND p.status_y =3D 0 AND p.status_z =3D = 0 AND warning.id IS NOT NULL THEN 1 ELSE 0 END) AS = green_warned_count, > SUM(CASE WHEN NOT (p.status_x =3D 0 AND p.status_y =3D 0 AND = p.status_z =3D 0) AND NOT (p.status_x =3D 2 OR p.status_y =3D 2 OR = p.status_z =3D 2) AND warning.id IS NULL THEN 1 = ELSE 0 END) AS yellow_count,=20 > SUM(CASE WHEN NOT (p.status_x =3D 0 AND p.status_y =3D 0 AND = p.status_z =3D 0) AND NOT (p.status_x =3D 2 OR p.status_y =3D 2 OR = p.status_z =3D 2) AND warning.id IS NOT NULL THEN 1 = ELSE 0 END) AS yellow_warned_count,=20 > SUM(CASE WHEN (p.status_x =3D 2 OR p.status_y =3D 2 OR p.status_z =3D = 2) AND warning.id IS NULL THEN 1 ELSE 0 END) AS = red_count,=20 > SUM(CASE WHEN (p.status_x =3D 2 OR p.status_y =3D 2 OR p.status_z =3D = 2) AND warning.id IS NOT NULL THEN 1 ELSE 0 END) AS = red_warned_count,=20 > SUM(CASE WHEN (p.status_x =3D 1000 OR p.status_y =3D 1000 OR = p.status_z =3D 1000) AND warning.id IS NOT NULL = THEN 1 ELSE 0 END) AS unable_to_measure_count=20 > FROM "Point" p=20 > JOIN "Feature" f ON f.id =3D p.feature_id > JOIN "Measurement" measurement ON measurement.id = =3D f.measurement_id=20 > JOIN "Product" product ON product.id =3D = measurement.product_id=20 > LEFT JOIN "Warning" warning ON f.id =3D = warning.feature_id > WHERE (product.name ILIKE 'Part 1') AND=20 > measurement.measurement_start_time >=3D '2015-06-18 17:00:00' AND=20 > measurement.measurement_start_time <=3D '2015-06-18 18:00:00' AND=20 > measurement.id NOT IN(SELECT measurement_id = FROM "Extra_info" e=20 > WHERE e.measurement_id =3D measurement.id AND = e.description =3D 'Clamp' AND e.value_string ILIKE 'Clamped%') > GROUP BY f.name , f.description; >=20 >=20 > ---Explain Analyze--- > GroupAggregate (cost=3D1336999.08..1337569.18 rows=3D5562 width=3D33) = (actual time=3D6223.622..6272.321 rows=3D255 loops=3D1) > Buffers: shared hit=3D263552 read=3D996, temp read=3D119 = written=3D119 > -> Sort (cost=3D1336999.08..1337012.98 rows=3D5562 width=3D33) = (actual time=3D6223.262..6231.106 rows=3D26265 loops=3D1) > Sort Key: f.name , f.description > Sort Method: external merge Disk: 936kB > Buffers: shared hit=3D263552 read=3D996, temp read=3D119 = written=3D119 > -> Nested Loop Left Join (cost=3D0.00..1336653.08 = rows=3D5562 width=3D33) (actual time=3D55.792..6128.875 rows=3D26265 = loops=3D1) > Buffers: shared hit=3D263552 read=3D996 > -> Nested Loop (cost=3D0.00..1220487.17 rows=3D5562 = width=3D33) (actual time=3D55.773..5910.852 rows=3D26265 loops=3D1) > Buffers: shared hit=3D182401 read=3D954 > -> Nested Loop (cost=3D0.00..22593.53 = rows=3D8272 width=3D27) (actual time=3D30.980..3252.869 rows=3D38831 = loops=3D1) > Buffers: shared hit=3D972 read=3D528 > -> Nested Loop (cost=3D0.00..657.24 = rows=3D22 width=3D8) (actual time=3D0.102..109.577 rows=3D103 loops=3D1) > Join Filter: (measurement.product_id = =3D product.id ) > Rows Removed by Join Filter: 18 > Buffers: shared hit=3D484 read=3D9 > -> Seq Scan on "Product" product = (cost=3D0.00..1.04 rows=3D1 width=3D8) (actual time=3D0.010..0.019 = rows=3D1 loops=3D1) > Filter: (name ~~* 'Part = 1'::text) > Rows Removed by Filter: 2 > Buffers: shared hit=3D1 > -> Index Scan using = measurement_start_time_index on "Measurement" measurement = (cost=3D0.00..655.37 rows=3D67 width=3D16) (actual time=3D0.042..109.416 = rows=3D121 loops=3D1) > Index Cond: = ((measurement_start_time >=3D '2015-06-18 17:00:00'::timestamp without = time zone) AND (measurement_start_time <=3D '2015-06-18 = 18:00:00'::timestamp without time zone)) > Filter: (NOT (SubPlan 1)) > Buffers: shared hit=3D483 = read=3D9 > SubPlan 1 > -> Index Scan using = extra_info_measurement_id_index on "Extra_info" e (cost=3D0.00..9.66 = rows=3D1 width=3D8) (actual time=3D0.900..0.900 rows=3D0 loops=3D121) > Index Cond: = (measurement_id =3D measurement.id ) > Filter: ((value_string = ~~* 'Clamped%'::text) AND (description =3D 'Clamp'::text)) > Rows Removed by Filter: = 2 > Buffers: shared = hit=3D479 read=3D7 > -> Index Scan using = feature_measurement_id_and_name_index on "Feature" rf = (cost=3D0.00..993.40 rows=3D370 width=3D35) (actual = time=3D28.152..30.407 rows=3D377 loops=3D103) > Index Cond: (measurement_id =3D = measurement.id ) > Buffers: shared hit=3D488 read=3D519 > -> Index Scan using point_feature_id_index on = "Point" p (cost=3D0.00..144.80 rows=3D1 width=3D14) (actual = time=3D0.067..0.067 rows=3D1 loops=3D38831) > Index Cond: (feature_id =3D f.id = ) > Buffers: shared hit=3D181429 read=3D426 > -> Index Scan using warning_feature_id_index on = "Warning" warning (cost=3D0.00..20.88 rows=3D1 width=3D16) (actual = time=3D0.007..0.007 rows=3D0 loops=3D26265) > Index Cond: (f.id =3D feature_id) > Buffers: shared hit=3D81151 read=3D42 > Total runtime: 6273.312 ms >=20 >=20 > ---Version--- > PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit >=20 >=20 > ---Table sizes--- > Extra_info 1223400 rows > Feature 185436000 rows > Measurement 500000 rows > Point 124681000 rows > Warning 11766800 rows >=20 > ---Hardware--- > 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 >=20 > ---History--- > Query gets slower as more data is added to the database >=20 > ---Maintenance--- > Autovacuum is used with default settings >=20 > Tommi Kaksonen wrote: =20 =20 I don=E2=80=99t see a reason to partition such small data. What I do = see is you attempting to run a big query on what looks like a small = desktop pc. 6GB of ram, especially under Windows 7, isn=E2=80=99t = enough ram for a database server. Run the query on a normal small = server of say 16gb and it should perform fine. IMO. =20 Mike =20 ------=_NextPart_000_00E7_01D1FF9D.7F942A60 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

 

From:<= /b> = pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Tommi K
Sent: Friday, August 26, 2016 7:25 = AM
To: Craig James <cjames@emolecules.com>
Cc: = andreas kretschmer <akretschmer@spamfence.net>; = pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow = query with big tables

 

Ok, sorry that I did not add the original = message. I thought that it would be automatically added to the message = thread.

 

Here is the question = again:

 

Is there way to keep query time constant as = the database size grows. Should I use partitioning or partial = indexes?

 

Thanks,

Tommi = Kaksonen

 

 

 

> = 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.

> 

> 

> --Table = definitions---

> 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=3DFALSE

> = );

> 

> 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=3DFALSE

> = );

> 

> 

> 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=3DFALSE

> = );

> 

> 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=3DFALSE

> = );

> 

> 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=3DFALSE

> = );

> 

> 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=3DFALSE

> = );

> 

> CREATE INDEX = warning_feature_id_index

>   ON = "Warning"

>   = USING btree

>   = (feature_id);

> 

> 

> = ---Query---

> = SELECT

> f.name, 

=

> = f.description,

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

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

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

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

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

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

> SUM(CASE WHEN (p.status_x =3D 1000 OR = p.status_y =3D 1000 OR p.status_z =3D 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 =3D = p.feature_id

> JOIN = "Measurement" measurement ON measurement.id =3D = f.measurement_id 

> JOIN "Product" product ON product.id =3D = measurement.product_id 

> LEFT JOIN "Warning" warning ON = f.id =3D = warning.feature_id

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

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

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

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

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

> =  GROUP BY f.name, = f.description;

> 

> 

> ---Explain = Analyze---

> = GroupAggregate  (cost=3D1336999.08..1337569.18 rows=3D5562 = width=3D33) (actual time=3D6223.622..6272.321 rows=3D255 = loops=3D1)

>   = Buffers: shared hit=3D263552 read=3D996, temp read=3D119 = written=3D119

>   = ->  Sort  (cost=3D1336999.08..1337012.98 rows=3D5562 = width=3D33) (actual time=3D6223.262..6231.106 rows=3D26265 = loops=3D1)

>   =       Sort Key: f.name, = f.description

>   =       Sort Method: external merge  Disk: = 936kB

>   =       Buffers: shared hit=3D263552 read=3D996, temp = read=3D119 written=3D119

>         -> =  Nested Loop Left Join  (cost=3D0.00..1336653.08 rows=3D5562 = width=3D33) (actual time=3D55.792..6128.875 rows=3D26265 = loops=3D1)

>   =             Buffers: shared hit=3D263552 = read=3D996

>   =             ->  Nested Loop =  (cost=3D0.00..1220487.17 rows=3D5562 width=3D33) (actual = time=3D55.773..5910.852 rows=3D26265 = loops=3D1)

>   =                   Buffers: = shared hit=3D182401 read=3D954

>             =         ->  Nested Loop =  (cost=3D0.00..22593.53 rows=3D8272 width=3D27) (actual = time=3D30.980..3252.869 rows=3D38831 = loops=3D1)

>   =                     =     Buffers: shared hit=3D972 = read=3D528

>   =                     =     ->  Nested Loop  (cost=3D0.00..657.24 = rows=3D22 width=3D8) (actual time=3D0.102..109.577 rows=3D103 = loops=3D1)

>   =                     =           Join Filter: (measurement.product_id = =3D product.id)

>             =                     = Rows Removed by Join Filter: 18

>             =                     = Buffers: shared hit=3D484 read=3D9

>             =                     = ->  Seq Scan on "Product" product =  (cost=3D0.00..1.04 rows=3D1 width=3D8) (actual time=3D0.010..0.019 = rows=3D1 loops=3D1)

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

>   =                     =                 Rows Removed by = Filter: 2

>   =                     =                 Buffers: shared = hit=3D1

>   =                     =           ->  Index Scan using = measurement_start_time_index on "Measurement" measurement =  (cost=3D0.00..655.37 rows=3D67 width=3D16) (actual = time=3D0.042..109.416 rows=3D121 = loops=3D1)

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

>   =                     =                 Filter: (NOT = (SubPlan 1))

>   =                     =                 Buffers: shared = hit=3D483 read=3D9

>   =                     =                 SubPlan = 1

>   =                     =                   -> =  Index Scan using extra_info_measurement_id_index on = "Extra_info" e  (cost=3D0.00..9.66 rows=3D1 width=3D8) = (actual time=3D0.900..0.900 rows=3D0 = loops=3D121)

>   =                     =                     =     Index Cond: (measurement_id =3D measurement.id)

<= /div>

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

>   =                     =                     =     Rows Removed by Filter: = 2

>   =                     =                     =     Buffers: shared hit=3D479 = read=3D7

>   =                     =     ->  Index Scan using = feature_measurement_id_and_name_index on "Feature" rf =  (cost=3D0.00..993.40 rows=3D370 width=3D35) (actual = time=3D28.152..30.407 rows=3D377 = loops=3D103)

>   =                     =           Index Cond: (measurement_id =3D measurement.id)

<= /div>

>             =                     = Buffers: shared hit=3D488 read=3D519

>             =         ->  Index Scan using = point_feature_id_index on "Point" p  (cost=3D0.00..144.80 = rows=3D1 width=3D14) (actual time=3D0.067..0.067 rows=3D1 = loops=3D38831)

>   =                     =     Index Cond: (feature_id =3D f.id)

>             =               Buffers: shared = hit=3D181429 read=3D426

>             =   ->  Index Scan using warning_feature_id_index on = "Warning" warning  (cost=3D0.00..20.88 rows=3D1 = width=3D16) (actual time=3D0.007..0.007 rows=3D0 = loops=3D26265)

>   =                   Index = Cond: (f.id =3D = feature_id)

>   =                   Buffers: = shared hit=3D81151 read=3D42

> Total runtime: 6273.312 = ms

> 

> 

> = ---Version---

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

> 

> 

> ---Table = sizes---

> = Extra_info=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1223400 = rows

> = Feature=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 185436000 = rows

> = Measurement=C2=A0=C2=A0=C2=A0=C2=A0 500000 = rows

> Point = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 124681000 = rows

> = Warning=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 11766800 rows

> 

> = ---Hardware---

> 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

> 

> = ---History---

> Query = gets slower as more data is added to the = database

> 

> = ---Maintenance---

> = Autovacuum is used with default = settings

> 

> Tommi Kaksonen = <t2nn2t(at)gmail(dot)com> = wrote:

 

 

I = don=E2=80=99t see a reason to partition such small data.=C2=A0 What I do = see is you attempting to run a big query on what looks like a small = desktop pc.=C2=A0 6GB of ram, especially under Windows 7, isn=E2=80=99t = enough ram for a database server.=C2=A0 Run the query on a normal small = server of say 16gb and it should perform fine.=C2=A0 = IMO.

 

Mike

 

------=_NextPart_000_00E7_01D1FF9D.7F942A60--