X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9CF4152CEA for ; Fri, 19 Aug 2005 04:18:03 -0300 (ADT) 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 45563-10 for ; Fri, 19 Aug 2005 07:17:59 +0000 (GMT) Received: from linuxworld.com.au (unknown [203.34.46.50]) by svr1.postgresql.org (Postfix) with ESMTP id 71FEE52CBB for ; Fri, 19 Aug 2005 04:17:56 -0300 (ADT) Received: from linuxworld.com.au (IDENT:swm@localhost.localdomain [127.0.0.1]) by linuxworld.com.au (8.13.2/8.13.2) with ESMTP id j7J7HlIO031072; Fri, 19 Aug 2005 17:17:48 +1000 Received: from localhost (swm@localhost) by linuxworld.com.au (8.13.2/8.13.2/Submit) with ESMTP id j7J7HjEF031069; Fri, 19 Aug 2005 17:17:47 +1000 X-Authentication-Warning: linuxworld.com.au: swm owned process doing -bs Date: Fri, 19 Aug 2005 17:17:45 +1000 (EST) From: Gavin Sherry X-X-Sender: swm@linuxworld.com.au To: Roger Hand Cc: pgsql-performance@postgresql.org Subject: Re: Query plan looks OK, but slow I/O - settings advice? In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 required=5 tests=[AWL=0.002] X-Spam-Level: X-Archive-Number: 200508/248 X-Sequence-Number: 13995 The query plan does *not* look okay. > electric=# EXPLAIN ANALYZE > electric-# SELECT datavalue, logfielddatatype, timestamp FROM logdata_recent > electric-# WHERE (logfielddatatype = 70 OR logfielddatatype = 71 OR logfielddatatype = 69) > electric-# AND graphtargetlog = 1327 > electric-# AND timestamp >= 1123052400 AND timestamp <= 1123138800 > electric-# ORDER BY timestamp; > QUERY PLAN > -------------------------------------------------- > Sort (cost=82.48..82.50 rows=6 width=14) (actual time=60208.968..60211.232 rows=2625 loops=1) > Sort Key: public.logdata_recent."timestamp" > -> Result (cost=0.00..82.41 rows=6 width=14) (actual time=52.483..60200.868 rows=2625 loops=1) > -> Append (cost=0.00..82.41 rows=6 width=14) (actual time=52.476..60189.929 rows=2625 loops=1) > -> Seq Scan on logdata_recent (cost=0.00..46.25 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=1) > Filter: (((logfielddatatype = 70) OR (logfielddatatype = 71) OR (logfielddatatype = 69)) AND (graphtargetlog = 1327) AND ("timestamp" >= 1123052400) AND ("timestamp" <= 1123138800)) > -> Index Scan using logdata_recent_1123085306_ix_t_fld_gtl, logdata_recent_1123085306_ix_t_fld_gtl, logdata_recent_1123085306_ix_t_fld_gtl on logdata_recent_stale logdata_recent (cost=0.00..18.08 rows=3 width=14) (actual time=52.465..60181.624 rows=2625 loops=1) Notice here that expected rows is 3, but actual rows is a hell of a lot higher. Try increasing stats collections for the columns on which logdata_recent_1123085306_ix_t_fld_gtl is declared. Also, the actual index scan is taking a long time. How recently have you vacuum full'd? Thanks, Gavin