X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6549D32C61D for ; Wed, 13 Oct 2004 10:14:27 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02925-05 for ; Wed, 13 Oct 2004 09:14:20 +0000 (GMT) Received: from zaphod.profecta.se (zaphod.profecta.se [193.235.206.49]) by svr1.postgresql.org (Postfix) with ESMTP id DD92232C609 for ; Wed, 13 Oct 2004 10:14:20 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by zaphod.profecta.se (Postfix) with ESMTP id 9D0241140 for ; Wed, 13 Oct 2004 11:14:13 +0200 (CEST) Received: from [192.168.128.80] (ip050.noname4us.com [212.247.87.50]) by zaphod.profecta.se (Postfix) with ESMTP id 310A91124 for ; Wed, 13 Oct 2004 11:14:13 +0200 (CEST) Subject: query problem From: Robin Ericsson To: pgsql-performance@postgresql.org Content-Type: multipart/mixed; boundary="=-o5nK7lU1Fuj/F/D20jNX" Organization: Profecta HB Date: Wed, 13 Oct 2004 11:21:11 +0200 Message-Id: <1097659271.24018.68.camel@pylver.localhost.nu.> Mime-Version: 1.0 X-Mailer: Evolution 2.0.0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/172 X-Sequence-Number: 8648 --=-o5nK7lU1Fuj/F/D20jNX Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi, I sent this to general earlier but I was redirected to performance. The query have been running ok for quite some time, but after I did a vacuum on the database, it's very very slow. This IN-query is only 2 ids. Before the problem that in was a subselect-query returning around 6-7 ids. The tables included in the query are described in database.txt. status=# select count(id) from data; count --------- 1577621 (1 row) status=# select count(data_id) from data_values; count --------- 9680931 (1 row) I did run a new explain analyze on the query and found the attached result. The obvious problem I see is a full index scan in "idx_dv_data_id". I tried dropping and adding the index again, thats why is't called "idx_data_values_data_id" in the dump. status=# EXPLAIN ANALYZE status-# SELECT status-# data.entered, status-# data.machine_id, status-# datatemplate_intervals.template_id, status-# data_values.value status-# FROM status-# data, data_values, datatemplate_intervals status-# WHERE status-# datatemplate_intervals.id = data_values.template_id AND status-# data_values.data_id = data.id AND status-# data.machine_id IN (2,3) AND status-# current_timestamp::timestamp - interval '60 seconds' < data.entered; Regards, Robin -- Robin Ericsson Profecta HB --=-o5nK7lU1Fuj/F/D20jNX Content-Disposition: attachment; filename=explain2.txt Content-Type: text/plain; name=explain2.txt; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hash Join (cost=28646.01..274260.15 rows=555706 width=24) (actual time=102323.087..102323.196 rows=5 loops=1) Hash Cond: ("outer".template_id = "inner".id) -> Merge Join (cost=28644.09..265922.62 rows=555706 width=24) (actual time=102322.632..102322.709 rows=5 loops=1) Merge Cond: ("outer".data_id = "inner".id) -> Index Scan using idx_dv_data_id on data_values (cost=0.00..205034.19 rows=9580032 width=16) (actual time=17.503..86263.130 rows=9596747 loops=1) -> Sort (cost=28644.09..28870.83 rows=90697 width=16) (actual time=0.829..0.835 rows=1 loops=1) Sort Key: data.id -> Index Scan using idx_d_entered on data (cost=0.00..20202.81 rows=90697 width=16) (actual time=0.146..0.185 rows=1 loops=1) Index Cond: (((('now'::text)::timestamp(6) with time zone)::timestamp without time zone - '00:01:00'::interval) < entered) Filter: ((machine_id = 2) OR (machine_id = 3)) -> Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382 rows=0 loops=1) -> Seq Scan on datatemplate_intervals (cost=0.00..1.74 rows=74 width=8) (actual time=0.024..0.250 rows=74 loops=1) Total runtime: 102323.491 ms (13 rows) --=-o5nK7lU1Fuj/F/D20jNX Content-Disposition: attachment; filename=database.txt Content-Type: text/plain; name=database.txt; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit status=# \d data Table "public.data" Column | Type | Modifiers ------------+-----------------------------+-------------------------------------------- id | integer | not null default nextval('data_seq'::text) updated | timestamp without time zone | entered | timestamp without time zone | machine_id | integer | datas | character varying(512)[] | Indexes: "data_pkey" primary key, btree (id) "idx_d_entered" btree (entered) "idx_d_machine_id" btree (machine_id) Foreign-key constraints: "machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id) Triggers: data_datestamp BEFORE INSERT OR UPDATE ON data FOR EACH ROW EXECUTE PROCEDURE datestamp_e() status=# \d data_values Table "public.data_values" Column | Type | Modifiers -------------+-----------------------------+----------- updated | timestamp without time zone | entered | timestamp without time zone | data_id | integer | template_id | integer | value | character varying(512) | Indexes: "idx_data_values_data_id" btree (data_id) "idx_dv_template_id" btree (template_id) Foreign-key constraints: "data_id" FOREIGN KEY (data_id) REFERENCES data(id) "template_id" FOREIGN KEY (template_id) REFERENCES datatemplate_intervals(id) Triggers: data_values_datestamp BEFORE INSERT OR UPDATE ON data_values FOR EACH ROW EXECUTE PROCEDURE datestamp_e() status=# \d datatemplate_intervals Table "public.datatemplate_intervals" Column | Type | Modifiers -------------+-----------------------------+-------------------------------------------------------------- id | integer | not null default nextval('datatemplate_intervals_seq'::text) updated | timestamp without time zone | entered | timestamp without time zone | machine_id | integer | template_id | integer | interval | integer | Indexes: "datatemplate_intervals_pkey" primary key, btree (id) "idx_di_machine_id" btree (machine_id) "idx_di_template_id" btree (template_id) Foreign-key constraints: "machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id) "template_id" FOREIGN KEY (template_id) REFERENCES datatemplates(id) Triggers: datatemplate_intervals_datestamp BEFORE INSERT OR UPDATE ON datatemplate_intervals FOR EACH ROW EXECUTE PROCEDURE datestamp_e() --=-o5nK7lU1Fuj/F/D20jNX--