On Wed, 2004-10-13 at 02:21, Robin Ericsson wrote: > 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.
Did you do a VACUUM FULL ANALYZE on the database or just a VACUUM? It looks like your statistics in your query are all off which ANALYZE should fix. > 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 > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org