Hi Guys,
So here is the full information attached as well as in the link provided
below:
http://pgsql.privatepaste.com/41207bea45
I can provide new information as well.
Would like to see if queries of these type can actually run in postgres
server?
If yes, what would be the minimum requirements for hardware? We would like
to migrate our whole solution on PostgreSQL as we can spend on hardware as
much as we can but working on a proprietary appliance is becoming very
difficult for us.
Vivek
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx]
Sent: Saturday, March 14, 2015 3:56 AM
To: Varadharajan Mukundan
Cc: vjoshi@xxxxxxxxxxxxxxxxxxx; Tomas Vondra;
pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Performance issues
On Fri, Mar 13, 2015 at 4:03 PM, Varadharajan Mukundan
<srinathsmn@xxxxxxxxx> wrote:
>> We might even consider taking experts advice on how to tune queries
>> and server, but if postgres is going to behave like this, I am not
>> sure we would be able to continue with it.
>>
>> Having said that, I would day again that I am completely new to this
>> territory, so I might miss lots and lots of thing.
>
> My two cents: Postgres out of the box might not be a good choice for
> data warehouse style queries, that is because it is optimized to run
> thousands of small queries (OLTP style processing) and not one big
> monolithic query. I've faced similar problems myself before and here
> are few tricks i followed to get my elephant do real time adhoc
> analysis on a table with ~45 columns and few billion rows in it.
>
> 1. Partition your table! use constraint exclusion to the fullest
> extent 2. Fire multiple small queries distributed over partitions and
> aggregate them at the application layer. This is needed because, you
> might to exploit all your cores to the fullest extent (Assuming that
> you've enough memory for effective FS cache). If your dataset goes
> beyond the capability of a single system, try something like Stado
> (GridSQL)
> 3. Storing index on a RAM / faster disk disk (using tablespaces) and
> using it properly makes the system blazing fast. CAUTION: This
> requires some other infrastructure setup for backup and recovery 4. If
> you're accessing a small set of columns in a big table and if you feel
> compressing the data helps a lot, give this FDW a try -
> https://github.com/citusdata/cstore_fdw
Agreed here. IF you're gonna run reporting queries against postgresql you
have to optimize for fast seq scan stuff. I.e. an IO subsystem that can read
a big table in hundreds of megabytes per second.
Gigabytes if you can get it. A lot of spinning drives on a fast RAID card or
good software raid can do this on the cheapish, since a lot of times you
don't need big drives if you have a lot. 24 cheap 1TB drives that each can
read at ~100 MB/s can gang up on the data and you can read a 100GB in a few
seconds. But you can't deny physics. If you need to read a 2TB table it's
going to take time.
If you're only running 1 or 2 queries at a time, you can crank up the
work_mem to something crazy like 1GB even on an 8GB machine. Stopping sorts
from spilling to disk, or at least giving queries a big playground to work
in can make a huge difference. If you're gonna give big work_mem then
definitely limit connections to a handful. If you need a lot of persistent
connections then use a pooler.
The single biggest mistake people make in setting up reporting servers on
postgresql is thinking that the same hardware that worked well for
transactional stuff (a handful of SSDs and lots of memory) might not help
when you're working with TB data sets. The hardware you need isn't the same,
and using that for a reporting server is gonna result in sub-optimal
performance.
--
To understand recursion, one must first understand recursion.
Query Used in report:
=========================================================================================================================
SELECT
Z.SENT_IND AS IS_SENT,
COALESCE(X.EMAILS, 0) AS EMAILS,
COALESCE(X.PERCENT, 0) PERCENT
FROM
(
SELECT
CASE WHEN SENT_IND <= 1
THEN 1
WHEN SENT_IND <= 5
THEN 2
WHEN SENT_IND <= 10
THEN 3
WHEN SENT_IND <= 15
THEN 4
WHEN SENT_IND <= 20
THEN 5
WHEN SENT_IND <= 30
THEN 6
WHEN SENT_IND <= 50
THEN 7
WHEN SENT_IND <= 75
THEN 8
WHEN SENT_IND <= 100
THEN 9
ELSE 10
END AS SEND_RANK,
COUNT(DISTINCT TARGET_ID) AS EMAILS,
(COUNT(DISTINCT TARGET_ID)+0.0) / (
SELECT
COUNT(DISTINCT TARGET_ID)
FROM
S_V_F_PROMOTION_HISTORY_EMAIL PH
INNER JOIN S_V_D_CAMPAIGN_HIERARCHY CH
ON PH.TOUCHPOINT_EXECUTION_ID = CH.TOUCHPOINT_EXECUTION_ID
WHERE
1=1
AND SEND_DT >= '2014-03-13'
AND SEND_DT <= '2015-03-14'
-- AND ($X{IN,CH.CAMPAIGN_INITIATIVE,param_CAMPAIGN_INITIATIVE_2} OR $X{IN,'--ALL INITIATIVES--',param_CAMPAIGN_INITIATIVE_2})
) AS PERCENT
FROM
(
SELECT
TARGET_ID,
COUNT(PROMO_HIST_ID) AS SENT_IND
FROM
S_V_F_PROMOTION_HISTORY_EMAIL PH
INNER JOIN S_V_D_CAMPAIGN_HIERARCHY CH
ON PH.TOUCHPOINT_EXECUTION_ID = CH.TOUCHPOINT_EXECUTION_ID
WHERE
1=1
AND SEND_DT >= '2014-01-01'
AND SEND_DT <= '2015-03-14'
AND ($X{IN, CH.CAMPAIGN_INITIATIVE, param_CAMPAIGN_INITIATIVE_2}
OR $X{IN, '--ALL INITIATIVES--', param_CAMPAIGN_INITIATIVE_2})
GROUP BY
TARGET_ID
) A
GROUP BY
SEND_RANK
) X
RIGHT OUTER JOIN (
SELECT
1 AS SEND_RANK,
'1' AS SENT_IND
UNION
SELECT
2,
'2-5'
UNION
SELECT
3,
'6-10'
UNION
SELECT
4,
'11-15'
UNION
SELECT
5,
'16-20'
UNION
SELECT
6,
'21-30'
UNION
SELECT
7,
'31-50'
UNION
SELECT
8,
'51-75'
UNION
SELECT
9,
'76-100'
UNION
SELECT
10,
'101 OR MORE'
) Z
ON Z.SEND_RANK = X.SEND_RANK
ORDER BY
Z.SEND_RANK ASC ;
=========================================================================================================================
Views Used in the campaigns:
S_V_F_PROMOTION_HISTORY_EMAIL
S_V_D_CAMPAIGN_HIERARCHY
=========================================================================================================================
S_V_F_PROMOTION_HISTORY_EMAIL:
WITH valid_executions AS (
SELECT ste.touchpoint_execution_id
FROM s_v_d_successful_touchpoint_executions ste
JOIN s_d_touchpoint_execution tpe ON ste.touchpoint_execution_id = tpe.touchpoint_execution_id
JOIN s_d_touchpoint tp ON tpe.touchpoint_id = tp.touchpoint_id AND tp.channel_type_id = 1
)
SELECT base.promo_hist_id,
base.audience_member_id,
base.target_id,
base.touchpoint_execution_id,
base.contact_group_id,
base.content_version_execution_id,
base.sent_ind,
CASE
WHEN email.sbounce_ind IS NOT NULL THEN email.sbounce_ind::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS sbounce_ind,
CASE
WHEN email.hbounce_ind IS NOT NULL THEN email.hbounce_ind::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS hbounce_ind,
CASE
WHEN email.opened_ind IS NOT NULL THEN email.opened_ind::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS opened_ind,
CASE
WHEN email.clicked_ind IS NOT NULL THEN email.clicked_ind::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS clicked_ind,
CASE
WHEN email.unsubscribe_ind IS NOT NULL THEN email.unsubscribe_ind::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS unsubscribe_ind,
CASE
WHEN email.unsubscribe_spam_ind IS NOT NULL THEN email.unsubscribe_spam_ind::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS unsubscribe_spam_ind,
CASE
WHEN email.gross_open_cnt IS NOT NULL THEN email.gross_open_cnt::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS gross_open_cnt,
CASE
WHEN email.distinct_click_cnt IS NOT NULL THEN email.distinct_click_cnt::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS distinct_click_cnt,
CASE
WHEN email.gross_click_cnt IS NOT NULL THEN email.gross_click_cnt::integer
WHEN 0 IS NOT NULL THEN 0
ELSE NULL::integer
END AS gross_click_cnt,
base.send_dt,
email.first_click_dt,
email.first_open_dt,
email.unsubscribe_dt,
email.unsubscribe_spam_dt,
email.bounce_dt,
CASE
WHEN email.creation_dt IS NOT NULL THEN email.creation_dt
WHEN base.creation_dt IS NOT NULL THEN base.creation_dt
ELSE NULL::timestamp without time zone
END AS creation_dt,
CASE
WHEN email.modified_dt IS NOT NULL THEN email.modified_dt
WHEN base.modified_dt IS NOT NULL THEN base.modified_dt
ELSE NULL::timestamp without time zone
END AS modified_dt
FROM s_f_promotion_history base
LEFT JOIN s_f_promotion_history_email email ON base.promo_hist_id = email.promo_hist_id AND base.audience_member_id =
email.audience_member_id
JOIN valid_executions ON base.touchpoint_execution_id = valid_executions.touchpoint_execution_id;
S_V_D_CAMPAIGN_HIERARCHY :
WITH valid_executions AS (
SELECT s_f_touchpoint_execution_status_history.touchpoint_execution_id
FROM s_f_touchpoint_execution_status_history
JOIN ( SELECT s_f_touchpoint_execution_status_history_1.touchpoint_execution_id,
max(s_f_touchpoint_execution_status_history_1.creation_dt) AS last_status_change
FROM s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1
GROUP BY s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) tmp ON s_f_touchpoint_execut
ion_status_history.touchpoint_execution_id = tmp.touchpoint_execution_id AND s_f_touchpoint_execution_status_history.creat
ion_dt = tmp.last_status_change AND (s_f_touchpoint_execution_status_history.touchpoint_execution_status_type_id = ANY (AR
RAY[3, 4]))
)
SELECT camp.campaign_id,
camp.campaign_name,
camp.initiative AS campaign_initiative,
camp.objective AS campaign_objective,
camp.category_id AS campaign_category_id,
"CATEGORY".category_name AS campaign_category_name,
camp_exec.campaign_execution_id,
camp_exec.campaign_execution_name,
camp_exec.group_id,
grup.group_name,
camp_exec.start_dt AS campaign_execution_start_dt,
wave.wave_id,
wave.wave_name,
wave_exec.wave_execution_id,
wave_exec.wave_execution_name,
wave_exec.start_dt AS wave_execution_start_dt,
tp.touchpoint_id,
tp.touchpoint_name,
tp_exec.touchpoint_execution_id,
tp_exec.start_dt AS touchpoint_execution_start_dt,
channel.channel_type_id,
channel.channel_type_name,
tp_exec.message_type_id,
message_type.message_type_name,
tp_exec.content_id,
content.content_name
FROM s_d_touchpoint_execution tp_exec
JOIN s_d_wave_execution wave_exec ON tp_exec.wave_execution_id = wave_exec.wave_execution_id
JOIN s_d_campaign_execution camp_exec ON wave_exec.campaign_execution_id = camp_exec.campaign_execution_id
JOIN s_d_touchpoint tp ON tp_exec.touchpoint_id = tp.touchpoint_id
JOIN s_d_wave wave ON wave_exec.wave_id = wave.wave_id AND tp.wave_id = wave.wave_id
JOIN s_d_campaign camp ON camp_exec.campaign_id = camp.campaign_id AND wave.campaign_id = camp.campaign_id
LEFT JOIN s_d_content content ON tp_exec.content_id = content.content_id
LEFT JOIN s_d_message_type message_type ON tp_exec.message_type_id = message_type.message_type_id
LEFT JOIN s_d_group grup ON camp_exec.group_id = grup.group_id
JOIN s_d_channel_type channel ON tp.channel_type_id = channel.channel_type_id
LEFT JOIN s_d_category "CATEGORY" ON camp.category_id = "CATEGORY".category_id
JOIN valid_executions ON tp_exec.touchpoint_execution_id = valid_executions.touchpoint_execution_id;
=========================================================================================================================
Tables used in views along with the counts: These counts are for the testing server.
S_V_F_PROMOTION_HISTORY_EMAIL:
s_v_d_successful_touchpoint_executions (This is again a view). See definition below.
s_d_touchpoint_execution - 72956
s_d_touchpoint - 10972
s_f_promotion_history - 10000000
s_f_promotion_history_email - 46971
S_V_D_CAMPAIGN_HIERARCHY:
s_f_touchpoint_execution_status_history -- 291104
s_d_touchpoint_execution -- 72956
s_d_wave_execution -- 46247
s_d_campaign_execution -- 46244
s_d_touchpoint -- 10792
s_d_wave -- 9455
s_d_campaign -- 9433
s_d_content - 3180
s_d_message_type - 3
s_d_group - 7
s_d_channel_type - 5
s_d_category - 2
View Definition:
s_v_d_successful_touchpoint_executions:
SELECT DISTINCT s_f_touchpoint_execution_status_history.touchpoint_execution_id
FROM s_f_touchpoint_execution_status_history
JOIN ( SELECT s_f_touchpoint_execution_status_history_1.touchpoint_execution_id,
max(s_f_touchpoint_execution_status_history_1.creation_dt) AS last_status_change
FROM s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1
GROUP BY s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) tmp ON s_f_touchpoint_execution_status_history.touchpoint_execution_id = tmp.touchpoint_execution_id AND s_f_touchpoint_execution_status_history.creation_dt = tmp.last_status_change AND (s_f_touchpoint_execution_status_history.touchpoint_execution_status_type_id = ANY (ARRAY[3, 4, 6]));
Table Used:
s_f_touchpoint_execution_status_history
Count: 291104
=========================================================================================================================
Table definitions:
S_F_PROMOTION_HISTORY
TABLE "PUBLIC.S_F_PROMOTION_HISTORY"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
------------------------------+-----------------------------+--------------------+---------+--------------+-------------
PROMO_HIST_ID | BIGINT | NOT NULL | PLAIN | |
TARGET_ID | BIGINT | NOT NULL | PLAIN | |
AUDIENCE_MEMBER_ID | BIGINT | NOT NULL | PLAIN | |
TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
CONTACT_GROUP_ID | BIGINT | NOT NULL | PLAIN | |
CONTENT_VERSION_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
SENT_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
SEND_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_F_PROMOTION_HISTORY_IDX_PR" BTREE (PROMO_HIST_ID)
HAS OIDS: NO
S_F_PROMOTION_HISTORY_EMAIL
TABLE "PUBLIC.S_F_PROMOTION_HISTORY_EMAIL"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------------------+-----------------------------+--------------------+---------+--------------+-------------
PROMO_HIST_ID | BIGINT | NOT NULL | PLAIN | |
TARGET_ID | BIGINT | NOT NULL | PLAIN | |
AUDIENCE_MEMBER_ID | BIGINT | NOT NULL | PLAIN | |
TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
CONTACT_GROUP_ID | BIGINT | NOT NULL | PLAIN | |
SBOUNCE_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
HBOUNCE_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
OPENED_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
CLICKED_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
UNSUBSCRIBE_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
UNSUBSCRIBE_SPAM_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
GROSS_OPEN_CNT | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
DISTINCT_CLICK_CNT | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
GROSS_CLICK_CNT | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | |
FIRST_CLICK_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | |
FIRST_OPEN_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | |
UNSUBSCRIBE_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | |
UNSUBSCRIBE_SPAM_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | |
BOUNCE_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_F_PROMOTION_HISTORY_EMAIL_PK1" PRIMARY KEY, BTREE (PROMO_HIST_ID)
HAS OIDS: NO
S_F_TOUCHPOINT_EXECUTION_STATUS_HISTORY
TABLE "PUBLIC.S_F_TOUCHPOINT_EXECUTION_STATUS_HISTORY"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------------------------------+-----------------------------+----------------------------------------+----------+--------------+-------------
TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
TOUCHPOINT_EXECUTION_STATUS_TYPE_ID | INTEGER | NOT NULL | PLAIN | |
STATUS_MESSAGE | CHARACTER VARYING(255) | NOT NULL DEFAULT ''::CHARACTER VARYING | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
HAS OIDS: NO
S_D_TOUCHPOINT_EXECUTION
TABLE "PUBLIC.S_D_TOUCHPOINT_EXECUTION"
COLUMN | TYPE | MODIFIERS
-------------------------+-----------------------------+-----------
TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL
WAVE_EXECUTION_ID | BIGINT | NOT NULL
TOUCHPOINT_ID | BIGINT | NOT NULL
CHANNEL_TYPE_ID | SMALLINT | NOT NULL
CONTENT_ID | BIGINT | NOT NULL
MESSAGE_TYPE_ID | SMALLINT | NOT NULL
START_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL
INDEXES:
"S_D_TOUCHPOINT_EXECUTION_PKEY" PRIMARY KEY, BTREE (TOUCHPOINT_EXECUTION_ID, CHANNEL_TYPE_ID)
S_D_WAVE_EXECUTION
TABLE "PUBLIC.S_D_WAVE_EXECUTION"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-----------------------+-----------------------------+-----------+----------+--------------+-------------
WAVE_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
WAVE_ID | BIGINT | NOT NULL | PLAIN | |
CAMPAIGN_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
WAVE_EXECUTION_NAME | CHARACTER VARYING(300) | NOT NULL | EXTENDED | |
START_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"WAVE_EXECUTION_ID_IDX" UNIQUE, BTREE (WAVE_EXECUTION_ID)
HAS OIDS: NO
S_D_CAMPAIGN_EXECUTION
TABLE "PUBLIC.S_D_CAMPAIGN_EXECUTION"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------------------+-----------------------------+-----------+----------+--------------+-------------
CAMPAIGN_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | |
CAMPAIGN_ID | BIGINT | NOT NULL | PLAIN | |
GROUP_ID | SMALLINT | NOT NULL | PLAIN | |
CAMPAIGN_EXECUTION_NAME | CHARACTER VARYING(300) | NOT NULL | EXTENDED | |
START_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_CAMPAIGN_EXECUTION_IDX" BTREE (CAMPAIGN_EXECUTION_ID)
HAS OIDS: NO
S_D_TOUCHPOINT
TABLE "PUBLIC.S_D_TOUCHPOINT"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-----------------+-----------------------------+-----------+----------+--------------+-------------
TOUCHPOINT_ID | BIGINT | NOT NULL | PLAIN | |
WAVE_ID | BIGINT | NOT NULL | PLAIN | |
CHANNEL_TYPE_ID | SMALLINT | NOT NULL | PLAIN | |
TOUCHPOINT_NAME | CHARACTER VARYING(255) | NOT NULL | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_TOUCHPOINT_PKEY" PRIMARY KEY, BTREE (TOUCHPOINT_ID, CHANNEL_TYPE_ID)
HAS OIDS: NO
S_D_WAVE
TABLE "PUBLIC.S_D_WAVE"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------+-----------------------------+-----------+----------+--------------+-------------
WAVE_ID | BIGINT | NOT NULL | PLAIN | |
CAMPAIGN_ID | BIGINT | NOT NULL | PLAIN | |
WAVE_NAME | CHARACTER VARYING(255) | NOT NULL | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_WAVE_PKEY" PRIMARY KEY, BTREE (WAVE_ID)
HAS OIDS: NO
S_D_CAMPAIGN
TABLE "PUBLIC.S_D_CAMPAIGN"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
---------------+-----------------------------+-----------+----------+--------------+-------------
CAMPAIGN_ID | BIGINT | NOT NULL | PLAIN | |
CAMPAIGN_NAME | CHARACTER VARYING(150) | NOT NULL | EXTENDED | |
OBJECTIVE | CHARACTER VARYING(256) | | EXTENDED | |
INITIATIVE | CHARACTER VARYING(256) | | EXTENDED | |
CATEGORY_ID | SMALLINT | NOT NULL | PLAIN | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_CAMPAIGN_PKEY" PRIMARY KEY, BTREE (CAMPAIGN_ID)
HAS OIDS: NO
S_D_CONTENT
TABLE "PUBLIC.S_D_CONTENT"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-----------------+-----------------------------+-----------+----------+--------------+-------------
CONTENT_ID | BIGINT | NOT NULL | PLAIN | |
CONTENT_NAME | CHARACTER VARYING(255) | NOT NULL | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
CHANNEL_TYPE_ID | SMALLINT | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_CONTENT_PKEY" PRIMARY KEY, BTREE (CONTENT_ID, CHANNEL_TYPE_ID)
HAS OIDS: NO
S_D_MESSAGE_TYPE
TABLE "PUBLIC.S_D_MESSAGE_TYPE"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------------+-----------------------------+-----------+----------+--------------+-------------
MESSAGE_TYPE_ID | SMALLINT | NOT NULL | PLAIN | |
MESSAGE_TYPE_NAME | CHARACTER VARYING(50) | NOT NULL | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_MESSAGE_TYPE_PKEY" PRIMARY KEY, BTREE (MESSAGE_TYPE_ID)
HAS OIDS: NO
S_D_GROUP
TABLE "PUBLIC.S_D_GROUP"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------+-----------------------------+-----------+----------+--------------+-------------
GROUP_ID | SMALLINT | NOT NULL | PLAIN | |
GROUP_NAME | CHARACTER VARYING(150) | NOT NULL | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_GROUP_PKEY" PRIMARY KEY, BTREE (GROUP_ID)
HAS OIDS: NO
S_D_CHANNEL_TYPE
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
-------------------+-----------------------+-----------+----------+--------------+-------------
CHANNEL_TYPE_ID | SMALLINT | NOT NULL | PLAIN | |
CHANNEL_TYPE_NAME | CHARACTER VARYING(50) | NOT NULL | EXTENDED | |
INDEXES:
"D_CHANNEL_PK" PRIMARY KEY, BTREE (CHANNEL_TYPE_ID)
HAS OIDS: NO
S_D_CATEGORY
TABLE "PUBLIC.S_D_CATEGORY"
COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION
---------------+-----------------------------+-----------+----------+--------------+-------------
CATEGORY_ID | SMALLINT | NOT NULL | PLAIN | |
CATEGORY_NAME | CHARACTER VARYING(50) | NOT NULL | EXTENDED | |
CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | |
INDEXES:
"S_D_CATEGORY_PKEY" PRIMARY KEY, BTREE (CATEGORY_ID)
HAS OIDS: NO
=========================================================================================================================
Explain for the Query:
"Merge Left Join (cost=711668.51..711668.64 rows=10 width=76)"
" Merge Cond: (z.send_rank = (CASE WHEN (a.sent_ind <= 1) THEN 1 WHEN (a.sent_ind <= 5) THEN 2 WHEN (a.sent_ind <= 10) THEN 3 WHEN (a.sent_ind <= 15) THEN 4 WHEN (a.sent_ind <= 20) THEN 5 WHEN (a.sent_ind <= 30) THEN 6 WHEN (a.sent_ind <= 50) THEN 7 WHEN ( (...)"
" -> Sort (cost=0.62..0.64 rows=10 width=36)"
" Sort Key: z.send_rank"
" -> Subquery Scan on z (cost=0.25..0.45 rows=10 width=36)"
" -> HashAggregate (cost=0.25..0.35 rows=10 width=0)"
" -> Append (cost=0.00..0.20 rows=10 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Materialize (cost=711667.89..711667.96 rows=1 width=44)"
" -> GroupAggregate (cost=711667.89..711667.95 rows=1 width=16)"
" InitPlan 3 (returns $14)"
" -> Aggregate (cost=355832.51..355832.52 rows=1 width=8)"
" -> Nested Loop (cost=68171.53..355832.51 rows=1 width=8)"
" Join Filter: (base_1.touchpoint_execution_id = tp_exec_1.touchpoint_execution_id)"
" -> Nested Loop (cost=33666.96..37971.39 rows=1 width=894)"
" Join Filter: (tp_exec_1.touchpoint_execution_id = valid_executions_2.touchpoint_execution_id)"
" CTE valid_executions"
" -> Hash Join (cost=13753.53..31711.17 rows=1 width=8)"
" Hash Cond: ((s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history_2.touchpoint_execution_id) AND ((max(s_f_touchpoint_execution_status_history_1_1.creation_d (...)"
" -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_1 (cost=0.00..4766.04 rows=291104 width=16)"
" -> Hash (cost=5493.80..5493.80 rows=135878 width=16)"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_2 (cost=0.00..5493.80 rows=135878 width=16)"
" Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))"
" -> Nested Loop Left Join (cost=1955.80..6260.19 rows=1 width=894)"
" -> Nested Loop (cost=1955.67..6260.04 rows=1 width=776)"
" -> Nested Loop Left Join (cost=1955.54..6259.87 rows=1 width=658)"
" -> Nested Loop Left Join (cost=1955.40..6259.71 rows=1 width=340)"
" -> Nested Loop Left Join (cost=1955.27..6259.55 rows=1 width=222)"
" -> Nested Loop (cost=1954.99..6259.24 rows=1 width=197)"
" -> Nested Loop (cost=1954.71..6258.92 rows=1 width=173)"
" Join Filter: (camp_exec_1.campaign_id = wave_1.campaign_id)"
" -> Nested Loop (cost=1954.42..6254.67 rows=13 width=167)"
" -> Hash Join (cost=1954.13..6249.67 rows=13 width=108)"
" Hash Cond: ((tp_exec_1.touchpoint_id = tp_2.touchpoint_id) AND (wave_exec_1.wave_id = tp_2.wave_id))"
" -> Hash Join (cost=1576.83..4595.51 rows=72956 width=90)"
" Hash Cond: (tp_exec_1.wave_execution_id = wave_exec_1.wave_execution_id)"
" -> Seq Scan on s_d_touchpoint_execution tp_exec_1 (cost=0.00..1559.56 rows=72956 width=42)"
" -> Hash (cost=1001.37..1001.37 rows=46037 width=56)"
" -> Seq Scan on s_d_wave_execution wave_exec_1 (cost=0.00..1001.37 rows=46037 width=56)"
" -> Hash (cost=212.72..212.72 rows=10972 width=26)"
" -> Seq Scan on s_d_touchpoint tp_2 (cost=0.00..212.72 rows=10972 width=26)"
" -> Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec_1 (cost=0.29..0.37 rows=1 width=67)"
" Index Cond: (campaign_execution_id = wave_exec_1.campaign_execution_id)"
" -> Index Scan using s_d_wave_pkey on s_d_wave wave_1 (cost=0.29..0.31 rows=1 width=22)"
" Index Cond: (wave_id = wave_exec_1.wave_id)"
" -> Index Scan using s_d_campaign_pkey on s_d_campaign camp_1 (cost=0.29..0.32 rows=1 width=40)"
" Index Cond: (campaign_id = camp_exec_1.campaign_id)"
" -> Index Scan using s_d_content_pkey on s_d_content content_1 (cost=0.28..0.30 rows=1 width=33)"
" Index Cond: (tp_exec_1.content_id = content_id)"
" -> Index Scan using s_d_message_type_pkey on s_d_message_type message_type_1 (cost=0.13..0.15 rows=1 width=120)"
" Index Cond: (tp_exec_1.message_type_id = message_type_id)"
" -> Index Scan using s_d_group_pkey on s_d_group grup_1 (cost=0.13..0.15 rows=1 width=320)"
" Index Cond: (camp_exec_1.group_id = group_id)"
" -> Index Scan using d_channel_pk on s_d_channel_type channel_1 (cost=0.13..0.15 rows=1 width=120)"
" Index Cond: (channel_type_id = tp_2.channel_type_id)"
" -> Index Scan using s_d_category_pkey on s_d_category "CATEGORY_1" (cost=0.13..0.15 rows=1 width=120)"
" Index Cond: (camp_1.category_id = category_id)"
" -> CTE Scan on valid_executions valid_executions_2 (cost=0.00..0.02 rows=1 width=8)"
" -> Nested Loop Left Join (cost=34504.57..317861.06 rows=2 width=148)"
" CTE valid_executions"
" -> Nested Loop (cost=32782.31..34504.28 rows=1 width=8)"
" -> Nested Loop (cost=32782.03..34503.96 rows=1 width=16)"
" -> Unique (cost=32781.61..34495.50 rows=1 width=8)"
" -> Merge Join (cost=32781.61..34495.50 rows=1 width=8)"
" Merge Cond: ((s_f_touchpoint_execution_status_history.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) AND (s_f_touchpoint_execution_status_history.crea (...)"
" -> Sort (cost=19697.87..20098.14 rows=160107 width=16)"
" Sort Key: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt"
" -> Seq Scan on s_f_touchpoint_execution_status_history (cost=0.00..5857.68 rows=160107 width=16)"
" Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))"
" -> Sort (cost=13083.74..13254.76 rows=68410 width=16)"
" Sort Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1.creation_dt))"
" -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 (cost=0.00..4766.04 rows=291104 width=16)"
" -> Index Scan using s_d_touchpoint_execution_pkey on s_d_touchpoint_execution tpe (cost=0.42..8.44 rows=1 width=16)"
" Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)"
" -> Index Only Scan using s_d_touchpoint_pkey on s_d_touchpoint tp_1 (cost=0.29..0.32 rows=1 width=8)"
" Index Cond: ((touchpoint_id = tpe.touchpoint_id) AND (channel_type_id = 1))"
" -> Nested Loop (cost=0.00..283351.20 rows=2 width=74)"
" Join Filter: (base_1.touchpoint_execution_id = valid_executions_3.touchpoint_execution_id)"
" -> CTE Scan on valid_executions valid_executions_3 (cost=0.00..0.02 rows=1 width=8)"
" -> Seq Scan on s_f_promotion_history base_1 (cost=0.00..283334.17 rows=1361 width=74)"
" Filter: ((send_dt >= '2014-03-13 00:00:00'::timestamp without time zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))"
" -> Index Scan using s_f_promotion_history_email_pk1 on s_f_promotion_history_email email_1 (cost=0.29..2.76 rows=1 width=90)"
" Index Cond: (base_1.promo_hist_id = promo_hist_id)"
" Filter: (base_1.audience_member_id = audience_member_id)"
" -> Sort (cost=355835.37..355835.38 rows=1 width=16)"
" Sort Key: (CASE WHEN (a.sent_ind <= 1) THEN 1 WHEN (a.sent_ind <= 5) THEN 2 WHEN (a.sent_ind <= 10) THEN 3 WHEN (a.sent_ind <= 15) THEN 4 WHEN (a.sent_ind <= 20) THEN 5 WHEN (a.sent_ind <= 30) THEN 6 WHEN (a.sent_ind <= 50) THEN 7 WHEN (...)"
" -> Subquery Scan on a (cost=355835.32..355835.36 rows=1 width=16)"
" -> HashAggregate (cost=355835.32..355835.33 rows=1 width=16)"
" -> Nested Loop (cost=68171.53..355835.31 rows=1 width=16)"
" Join Filter: (base.touchpoint_execution_id = tp_exec.touchpoint_execution_id)"
" -> Nested Loop (cost=33666.96..37971.39 rows=1 width=894)"
" Join Filter: (tp_exec.touchpoint_execution_id = valid_executions.touchpoint_execution_id)"
" CTE valid_executions"
" -> Hash Join (cost=13753.53..31711.17 rows=1 width=8)"
" Hash Cond: ((s_f_touchpoint_execution_status_history_1_3.touchpoint_execution_id = s_f_touchpoint_execution_status_history_4.touchpoint_execution_id) AND ((max(s_f_touchpoint_execution_status_history_1_3. (...)"
" -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_3 (cost=0.00..4766.04 rows=291104 width=16)"
" -> Hash (cost=5493.80..5493.80 rows=135878 width=16)"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_4 (cost=0.00..5493.80 rows=135878 width=16)"
" Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))"
" -> Nested Loop Left Join (cost=1955.80..6260.19 rows=1 width=894)"
" -> Nested Loop (cost=1955.67..6260.04 rows=1 width=776)"
" -> Nested Loop Left Join (cost=1955.54..6259.87 rows=1 width=658)"
" -> Nested Loop Left Join (cost=1955.40..6259.71 rows=1 width=340)"
" -> Nested Loop Left Join (cost=1955.27..6259.55 rows=1 width=222)"
" -> Nested Loop (cost=1954.99..6259.24 rows=1 width=197)"
" -> Nested Loop (cost=1954.71..6258.92 rows=1 width=173)"
" Join Filter: (camp_exec.campaign_id = wave.campaign_id)"
" -> Nested Loop (cost=1954.42..6254.67 rows=13 width=167)"
" -> Hash Join (cost=1954.13..6249.67 rows=13 width=108)"
" Hash Cond: ((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id = tp.wave_id))"
" -> Hash Join (cost=1576.83..4595.51 rows=72956 width=90)"
" Hash Cond: (tp_exec.wave_execution_id = wave_exec.wave_execution_id)"
" -> Seq Scan on s_d_touchpoint_execution tp_exec (cost=0.00..1559.56 rows=72956 width=42)"
" -> Hash (cost=1001.37..1001.37 rows=46037 width=56)"
" -> Seq Scan on s_d_wave_execution wave_exec (cost=0.00..1001.37 rows=46037 width=56)"
" -> Hash (cost=212.72..212.72 rows=10972 width=26)"
" -> Seq Scan on s_d_touchpoint tp (cost=0.00..212.72 rows=10972 width=26)"
" -> Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec (cost=0.29..0.37 rows=1 width=67)"
" Index Cond: (campaign_execution_id = wave_exec.campaign_execution_id)"
" -> Index Scan using s_d_wave_pkey on s_d_wave wave (cost=0.29..0.31 rows=1 width=22)"
" Index Cond: (wave_id = wave_exec.wave_id)"
" -> Index Scan using s_d_campaign_pkey on s_d_campaign camp (cost=0.29..0.32 rows=1 width=40)"
" Index Cond: (campaign_id = camp_exec.campaign_id)"
" -> Index Scan using s_d_content_pkey on s_d_content content (cost=0.28..0.30 rows=1 width=33)"
" Index Cond: (tp_exec.content_id = content_id)"
" -> Index Scan using s_d_message_type_pkey on s_d_message_type message_type (cost=0.13..0.15 rows=1 width=120)"
" Index Cond: (tp_exec.message_type_id = message_type_id)"
" -> Index Scan using s_d_group_pkey on s_d_group grup (cost=0.13..0.15 rows=1 width=320)"
" Index Cond: (camp_exec.group_id = group_id)"
" -> Index Scan using d_channel_pk on s_d_channel_type channel (cost=0.13..0.15 rows=1 width=120)"
" Index Cond: (channel_type_id = tp.channel_type_id)"
" -> Index Scan using s_d_category_pkey on s_d_category "CATEGORY" (cost=0.13..0.15 rows=1 width=120)"
" Index Cond: (camp.category_id = category_id)"
" -> CTE Scan on valid_executions (cost=0.00..0.02 rows=1 width=8)"
" -> Nested Loop Left Join (cost=34504.57..317863.87 rows=2 width=148)"
" CTE valid_executions"
" -> Nested Loop (cost=32782.31..34504.28 rows=1 width=8)"
" -> Nested Loop (cost=32782.03..34503.96 rows=1 width=16)"
" -> Unique (cost=32781.61..34495.50 rows=1 width=8)"
" -> Merge Join (cost=32781.61..34495.50 rows=1 width=8)"
" Merge Cond: ((s_f_touchpoint_execution_status_history_3.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1_2.touchpoint_execution_id) AND (s_f_touchpoint_execution_statu (...)"
" -> Sort (cost=19697.87..20098.14 rows=160107 width=16)"
" Sort Key: s_f_touchpoint_execution_status_history_3.touchpoint_execution_id, s_f_touchpoint_execution_status_history_3.creation_dt"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_3 (cost=0.00..5857.68 rows=160107 width=16)"
" Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))"
" -> Sort (cost=13083.74..13254.76 rows=68410 width=16)"
" Sort Key: s_f_touchpoint_execution_status_history_1_2.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1_2.creation_dt))"
" -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)"
" -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_2 (cost=0.00..4766.04 rows=291104 width=16)"
" -> Index Scan using s_d_touchpoint_execution_pkey on s_d_touchpoint_execution tpe_1 (cost=0.42..8.44 rows=1 width=16)"
" Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history_3.touchpoint_execution_id)"
" -> Index Only Scan using s_d_touchpoint_pkey on s_d_touchpoint tp_3 (cost=0.29..0.32 rows=1 width=8)"
" Index Cond: ((touchpoint_id = tpe_1.touchpoint_id) AND (channel_type_id = 1))"
" -> Nested Loop (cost=0.00..283354.75 rows=2 width=74)"
" Join Filter: (base.touchpoint_execution_id = valid_executions_1.touchpoint_execution_id)"
" -> CTE Scan on valid_executions valid_executions_1 (cost=0.00..0.02 rows=1 width=8)"
" -> Seq Scan on s_f_promotion_history base (cost=0.00..283334.17 rows=1645 width=74)"
" Filter: ((send_dt >= '2014-01-01 00:00:00'::timestamp without time zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))"
" -> Index Scan using s_f_promotion_history_email_pk1 on s_f_promotion_history_email email (cost=0.29..2.39 rows=1 width=90)"
" Index Cond: (base.promo_hist_id = promo_hist_id)"
" Filter: (base.audience_member_id = audience_member_id)"
=========================================================================================================================
EXPLAIN ANALYZE: Query Never completed
Postgres version : PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 64-bit
History : Running for the first time.
Maintenance Setup: New Server and data has been just inserted.
WAL Configuration: Default Settings
shared_buffers = 6GB
work_mem = 1GB
maintenance_work_mem = 3GB
effective_cache_size = 16GB
All are commented:
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 548472 8193880 46160 5597832 0 0 8 34 0 0 1 0 99 0 0
==============
iostat
Linux 2.6.18-348.4.1.el5 (AT-PU-MERCURY) 03/13/2015
avg-cpu: %user %nice %system %iowait %steal %idle
0.54 0.00 0.15 0.04 0.00 99.26
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 5.72 62.89 262.98 3661856221 15313306008
sda1 0.00 0.02 0.00 911167 35000
sda2 5.72 62.87 262.98 3660943046 15313271008
dm-0 7.43 4.66 57.98 271216554 3375965736
dm-1 24.10 57.78 188.70 3364343202 10988093896
dm-2 1.72 0.00 13.79 127826 803126920
dm-3 0.37 0.43 2.51 25255000 146084456
==============
df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
49580256 36870396 10150680 79% /
/dev/mapper/VolGroup00-LogVol03
748313168 660142564 49545324 94% /var
/dev/mapper/VolGroup00-LogVol02
19838052 211072 18602980 2% /tmp
/dev/sda1 295561 24745 255556 9% /boot
tmpfs 12333076 0 12333076 0% /dev/shm
========
cat /proc/meminfo
MemTotal: 24666152 kB
MemFree: 8192640 kB
Buffers: 46512 kB
Cached: 5597864 kB
SwapCached: 183964 kB
Active: 15507332 kB
Inactive: 756136 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 24666152 kB
LowFree: 8192640 kB
SwapTotal: 32767992 kB
SwapFree: 32219520 kB
Dirty: 292 kB
Writeback: 0 kB
AnonPages: 10617088 kB
Mapped: 2175692 kB
Slab: 120216 kB
PageTables: 38128 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 45101068 kB
Committed_AS: 18324080 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 279376 kB
VmallocChunk: 34359458551 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB
=========================
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
