| From: | Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se> | 
|---|---|
| To: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> | 
| Subject: | query gone haywire :) | 
| Date: | 2004-10-08 09:19:57 | 
| Message-ID: | 1097227197.7088.90.camel@pylver.localhost.nu. | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
The query have been running ok for some time now, but this morning I
decided to run vacuum analyze (vacuumdb -a -z) on the cluster, and
suddenly the query isn't running very well at all. This query has only
one value in the "IN", if I add another id the query becomes really
really slow.
Query:
SELECT
	data.entered,
	data.machine_id,
	datatemplate_intervals.template_id,
	data_values.value
FROM
	data, data_values, datatemplate_intervals
WHERE
	datatemplate_intervals.id = data_values.template_id AND
	data_values.data_id = data.id AND
	data.machine_id IN (2) AND
	current_timestamp::timestamp - interval '60 seconds' < data.entered
Indexes exists on data_values.template_id, data.entered,
data.machine_id, datatemplate_intervals.machine_id,
datatemplate_intervals.template_id.
Data contains almost 1.5milj entries, and data_values around 9.1milj. As
I write this letter I check the tables in pgAdmin, and it tells me this
for table data """
Rows (estimated) 1
Rows (counted)   1491401
""" even though I run vacuum analyze on the table itself from pgadmin.
Explain analyze result attached as explain-analyze.txt
Explain without analyze when using IN(2,3) attached as explain.txt
Regards,
Robin
| Attachment | Content-Type | Size | 
|---|---|---|
| explain-analyze.txt | text/plain | 1018 bytes | 
| explain.txt | text/plain | 886 bytes | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Miles Keaton | 2004-10-08 10:00:07 | interesting! a sequence clashes with data already in that table | 
| Previous Message | Martijn van Oosterhout | 2004-10-08 08:22:53 | Re: Question about timezones |