Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 2C5FF1337B8E for ; Thu, 18 Nov 2010 09:33:35 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 72284-03 for ; Thu, 18 Nov 2010 13:33:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id 9BF471335B36 for ; Thu, 18 Nov 2010 09:33:26 -0400 (AST) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 7D8D315E4104; Thu, 18 Nov 2010 14:33:25 +0100 (CET) Received: from 89.103.152.5 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Thu, 18 Nov 2010 14:33:25 +0100 Message-ID: In-Reply-To: References: Date: Thu, 18 Nov 2010 14:33:25 +0100 Subject: Re: Low disk performance? From: tv@fuzzy.cz To: "Martin Chlupac" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/315 X-Sequence-Number: 41339 Hi, what is the size of the table and index (in terms of pages and tuples)? Try something like SELECT relpages, reltuples FROM pg_class WHERE relname = 'table or index name'; And what indexes have you created? It seems to me there's just index on the variable_id. It might be useful to create index on (variable_id, ts) or even (variable_id, ts, good_through). Tomas > Hello everybody, > having this SQL query: > > -------------- > select > variable_id,float_value,ts,good_through,interval,datetime_value,string_value,int_value,blob_value,history_value_type > from "records_437954e9-e048-43de-bde3-057658966a9f" where variable_id > in (22727) and (ts >= '2010-10-02 11:19:55' or good_through >= > '2010-10-02 11:19:55') and (ts <= '2010-10-14 11:19:55' or > good_through <= '2010-10-14 11:19:55') > union all > select > variable_id,float_value,ts,good_through,interval,datetime_value,string_value,int_value,blob_value,history_value_type > from "records_1d115712-e943-4ae3-bb14-b56a95796111" where variable_id > in (24052) and (ts >= '2010-10-02 11:19:55' or good_through >= > '2010-10-02 11:19:55') and (ts <= '2010-10-14 11:19:55' or > good_through <= '2010-10-14 11:19:55') > order by ts > limit 2501 offset 0 > > --------------- > > and these two results: > > 1st run: > http://explain.depesz.com/s/1lT > > 2nd run: > http://explain.depesz.com/s/bhA > > is there anything I can do about the speed? Only buying faster > hard-disk seems to me as the solution... Am I right? > > Thank you in advance > Martin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >