MIME-Version: 1.0
Date: Tue, 13 Apr 2010 16:25:59 +0200
From: <tv@fuzzy.cz>
To: "Satish Burnwal (sburnwal)" <sburnwal@cisco.com>
Cc: Bill Moran <wmoran@potentialtech.com>, <pgsql-general@postgresql.org>
Subject: Re: [ADMIN] Query is stuck
In-Reply-To: <3A8C969225424C4D8E6BEE65ED8552DA0119F1AD@XMB-BGL-41C.cisco.com>
References: 
 <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com><o2ne4edc9361004130603r9acb3257yb800890f447eb2ce@mail.gmail.com>
	<20100413091248.a295737b.wmoran@potentialtech.com>
	<3A8C969225424C4D8E6BEE65ED8552DA0119F1AD@XMB-BGL-41C.cisco.com>
Message-ID: <6013c776bd38b7738ff9e43815ce8adc@mail.gransy.com>
User-Agent: RoundCube Webmail/0.2.1
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset="UTF-8"


> INFO:  "repcopy": scanned 3000 of 4652 pages, containing 128964 live rows
> and 0 dead rows; 3000 rows in sample, 199980 estimated total rows
> VACUUM
> controlsmartdb=# select distinct report_status from repcopy ;

According to the vacuum output, there are about 200000 rows in the
"repcopy" table, occupying roughly 40MB. And according to the explain plan
you've posted earlier, there's a seq scan for each row - that gives 200000
sequential scans on the table ... which is about 8TB of data. Sure, most of
the data will be read from disk cache / shared buffers etc. but still it's
a lot of data to process - that's why it takes so long.

I'd recommend creating a index on (dm_user, dm_ip) columns, but it depends
on how many different values are in these columns (the more the better).

What information do we need to give better recommendations:

1) info about structure of the "repcopy" table (column data types, indexes)
2) info about data (how many different values are there)
3) what does the system do when running the query (use 'top' or 'dstat' to
get iowait / CPU / disk / memory etc.)

regards
Tomas