From: Robin Ericsson <robin.ericsson@profecta.se>
To: pgsql-performance@postgresql.org
Subject: query problem
Date: Wed, 13 Oct 2004 11:21:11 +0200
Message-ID: <1097659271.24018.68.camel@pylver.localhost.nu.> (raw)
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 <robin.ericsson@profecta.se>
Profecta HB
Attachments:explain2.txt (text/plain, 1.2K) download
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)
database.txt (text/plain, 2.6K) download
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()
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robin.ericsson@profecta.se
Subject: Re: query problem
In-Reply-To: <1097659271.24018.68.camel@pylver.localhost.nu.>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox