From: Gavin Sherry <swm@alcove.com.au>
To: Roger Hand <RHand@kailea.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan looks OK, but slow I/O - settings advice?
Date: Fri, 19 Aug 2005 17:17:45 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0508191710450.31045@linuxworld.com.au> (raw)
In-Reply-To: <DB28E9B548192448A4E8C8A3C1B1E475611C66@sj1-exch-01.us.corp.kailea.com>
References: <DB28E9B548192448A4E8C8A3C1B1E475611C66@sj1-exch-01.us.corp.kailea.com>
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
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: swm@alcove.com.au, RHand@kailea.com
Subject: Re: Query plan looks OK, but slow I/O - settings advice?
In-Reply-To: <Pine.LNX.4.58.0508191710450.31045@linuxworld.com.au>
* 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