query problem

From: Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: query problem
Date: 2004-10-13 09:21:11
Message-ID: 1097659271.24018.68.camel@pylver.localhost.nu.
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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(dot)ericsson(at)profecta(dot)se>
Profecta HB

Attachment Content-Type Size
explain2.txt text/plain 1.2 KB
database.txt text/plain 2.6 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2004-10-13 09:36:53 Re: [GENERAL] Reusable pl/pgsql samples ?
Previous Message Tino Wildenhain 2004-10-13 09:08:49 Re: adding two tables

Browse pgsql-performance by date

  From Date Subject
Next Message ken 2004-10-13 14:37:43 Re: query problem
Previous Message CoL 2004-10-13 08:46:59 Re: Which plattform do you recommend I run PostgreSQL for best