X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CDF5A32B4DE for ; Wed, 13 Oct 2004 15:38:00 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08358-05 for ; Wed, 13 Oct 2004 14:37:46 +0000 (GMT) Received: from server272.com (server272.com [64.14.68.49]) by svr1.postgresql.org (Postfix) with SMTP id 51FCB32B922 for ; Wed, 13 Oct 2004 15:37:49 +0100 (BST) Received: (qmail 14280 invoked from network); 13 Oct 2004 14:38:08 -0000 Received: from unknown (HELO pesky) (63.170.214.187) by server272.com with SMTP; 13 Oct 2004 14:38:08 -0000 Subject: Re: query problem From: ken To: Robin Ericsson Cc: pgsql-performance@postgresql.org In-Reply-To: <1097659271.24018.68.camel@pylver.localhost.nu.> References: <1097659271.24018.68.camel@pylver.localhost.nu.> Content-Type: text/plain Message-Id: <1097678263.9676.275.camel@pesky> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Wed, 13 Oct 2004 07:37:43 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/173 X-Sequence-Number: 8649 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 >