From: tv@fuzzy.cz
To: Satish Burnwal (sburnwal) <sburnwal@cisco.com>
Cc: Bill Moran <wmoran@potentialtech.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [ADMIN] Query is stuck
Date: Tue, 13 Apr 2010 16:25:59 +0200
Message-ID: <6013c776bd38b7738ff9e43815ce8adc@mail.gransy.com> (raw)
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>
> 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
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-admin@postgresql.org
Cc: tv@fuzzy.cz, sburnwal@cisco.com, wmoran@potentialtech.com, pgsql-general@postgresql.org
Subject: Re: [ADMIN] Query is stuck
In-Reply-To: <6013c776bd38b7738ff9e43815ce8adc@mail.gransy.com>
* 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