pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: ken <southerland@samsixedd.com>
To: Robin Ericsson <robin.ericsson@profecta.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: query problem
Date: Wed, 13 Oct 2004 07:37:43 -0700
Message-ID: <1097678263.9676.275.camel@pesky> (raw)
In-Reply-To: <1097659271.24018.68.camel@pylver.localhost.nu.>
References: <1097659271.24018.68.camel@pylver.localhost.nu.>

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
> 




view thread (9+ messages)  latest in thread

Message-ID: <1097678263.9676.275.camel@pesky>
Permalink:  ../1097678263.9676.275.camel@pesky/
Also on:    postgresql.org/message-id/1097678263.9676.275.camel@pesky

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: southerland@samsixedd.com, robin.ericsson@profecta.se
  Subject: Re: query problem
  In-Reply-To: <1097678263.9676.275.camel@pesky>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox