Re: query problem

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
>