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 <[EMAIL PROTECTED]>
Profecta HB
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)
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()
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
