Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id D900213C6999 for ; Fri, 4 May 2012 11:24:50 -0300 (ADT) Received: from mail-wg0-f50.google.com ([74.125.82.50]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SQJRA-0002aQ-78 for pgsql-performance@postgresql.org; Fri, 04 May 2012 14:24:50 +0000 Received: by wgbds12 with SMTP id ds12so2849770wgb.19 for ; Fri, 04 May 2012 07:24:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:content-type:subject:date:message-id:to:mime-version:x-mailer; bh=V+5BOup+YLfvQZUCUCUXiskMFB0mnTYNvfw5wNSFjMM=; b=A1omdb29jHLZ40RD/UWiI2qqYDh73/J7Cx2oo4awNOkiVBhgzKIk8BF3edxjWCMIZu 8vdJDvbP4By0hM6DNt21ynv8Zb6zHBy18meoKxJfsL7xUS4iAo2ueVeKSl/VG/ZGz2PU wxQYMaiDv1anP03uLKkJJfJPhwJkslLipmjYeyIWvV5NCjFHtiL48LpQ/n8RA3TqU/C4 eo9KvA5yYam8OZ+tFAFc1H5qXJAIR65tyZkygsmG5bZfQrtnbtZkFC/tCVVTdW9twbZw VEXaxqNIDhQmEXv9SzeP04muXuBr9n1Zm/88CKZr7m4LsdF9QI3mMtUnTVt8N882luxw VEDQ== Received: by 10.216.131.34 with SMTP id l34mr4005858wei.54.1336141475265; Fri, 04 May 2012 07:24:35 -0700 (PDT) Received: from [192.168.0.4] (cpc16-aztw25-2-0-cust224.aztw.cable.virginmedia.com. [77.102.173.225]) by mx.google.com with ESMTPS id l5sm1480454wia.11.2012.05.04.07.24.34 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 04 May 2012 07:24:34 -0700 (PDT) From: Dan Fairs Content-Type: multipart/alternative; boundary="Apple-Mail=_D61AE1CB-17AB-4895-B32C-0A2B63012594" Subject: Unexpected sequence scan Date: Fri, 4 May 2012 15:24:33 +0100 Message-Id: <07F01B2A-648A-4BA7-B056-EC4EA2FFE0D0@gmail.com> To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Apple Message framework v1257) X-Mailer: Apple Mail (2.1257) X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201205/46 X-Sequence-Number: 46870 --Apple-Mail=_D61AE1CB-17AB-4895-B32C-0A2B63012594 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi, I'm fairly new to PostgreSQL query tuning, so please forgive me if I've = overlooked something obvious. I have a query which is running slowly, and the query plan shows an = unexpected sequence scan where I'd have expected the planner to use an = index. Setting enable_seqscan=3Doff causes the planner to use the index = as expected. The types of the field that the join where the index should = be used do indeed match (int4) - I've read that's the usual reason for = an index not being used. I've tried rebuilding the = twitter_tweet_transmission_id index, and have re-ANALYSE'd the table. I can use the enable_seqscan=3Doff workaround for now, but I'd be = interested in knowing why the index isn't being used. Production is PostgreSQL 8.4 on Ubuntu 10.04 LTS, 16G RAM. I get a = broadly similar query plan both on a local dev machine running PG 9.1 = with 8G RAM and single SATA drive, and on the production server with 16G = RAM and a RAID10 array (some of the costs etc. are different, but the = use or absence of the index is common). I'm using a restored production = database on dev, so data volumes are the same. Here's the query, table and index definitions, query plans, and server = configs where the defaults have changed (query plans and config from the = dev machine) - I've only really done rudimentary tuning with pgtune, so = there are probably some howlers in the conf. Anyway: SELECT "timecode_transmission"."id", COUNT("twitter_tweet"."id") AS "tweet_count" FROM "timecode_transmission" LEFT OUTER JOIN "twitter_tweet" ON ("timecode_transmission"."id" =3D = "twitter_tweet"."transmission_id") WHERE "timecode_transmission"."tx" <=3D '2012-04-06 23:59:59' AND "timecode_transmission"."tx" >=3D '2012-04-06 00:00:00' GROUP BY "timecode_transmission"."id" The twitter_tweet table has about 25m rows, as you'll see from the query = plans. Any hints are appreciated. Cheers, Dan Table definitions: CREATE TABLE "public"."twitter_tweet" ( "id" int8 NOT NULL DEFAULT = nextval('twitter_tweet_id_seq'::regclass), "twitter_id" int8 NOT NULL, "created_at" timestamp(6) WITH TIME ZONE NOT NULL, "from_user" varchar(20) NOT NULL, "text" text NOT NULL, "from_user_id" int8 NOT NULL, "sentiment" float8 NOT NULL, "transmission_id" int4, "from_user_name" varchar(500), "gender" int2, CONSTRAINT "twitter_tweet_pkey" PRIMARY KEY ("id") NOT = DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "transmission_id_refs_id_23b9da6852fe9f37" FOREIGN = KEY ("transmission_id") REFERENCES "public"."timecode_transmission" = ("id") ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY = DEFERRED , CONSTRAINT "ck_gender_pstv_4bc0eb22f3ec191e" CHECK ((gender >=3D = 0)) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "twitter_tweet_gender_check" CHECK ((gender >=3D 0)) = NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "twitter_tweet_klout_score_check" CHECK ((klout_score = >=3D 0)) NOT DEFERRABLE INITIALLY IMMEDIATE ) WITH (OIDS=3DFALSE); CREATE INDEX "twitter_tweet_created_at" ON "public"."twitter_tweet" = USING btree(created_at ASC NULLS LAST); CREATE INDEX "twitter_tweet_transmission_id" ON "public"."twitter_tweet" = USING btree(transmission_id ASC NULLS LAST); CREATE TABLE "public"."timecode_transmission" ( "id" int4 NOT NULL DEFAULT = nextval('timecode_transmission_id_seq'::regclass), "episode_id" int4 NOT NULL, "channel_id" int4 NOT NULL, "tx" timestamp(6) WITH TIME ZONE NOT NULL, "status" varchar(100) NOT NULL, "end" timestamp(6) WITH TIME ZONE NOT NULL, "duration" int4, "include_in_listings" bool NOT NULL, CONSTRAINT "timecode_transmission_pkey" PRIMARY KEY ("id") NOT = DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "channel_id_refs_id_42fae8846ea37b15" FOREIGN KEY = ("channel_id") REFERENCES "public"."timecode_channel" ("id") ON UPDATE = NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED , CONSTRAINT "episode_id_refs_id_52ab388b54a13ff3" FOREIGN KEY = ("episode_id") REFERENCES "public"."timecode_episode" ("id") ON UPDATE = NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED , CONSTRAINT "timecode_transmission_tx_72eeb3dac42e185_uniq" = UNIQUE ("tx", "channel_id") NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "timecode_transmission_duration_check" CHECK = ((duration >=3D 0)) NOT DEFERRABLE INITIALLY IMMEDIATE ) WITH (OIDS=3DFALSE); CREATE INDEX "timecode_transmission_channel_id" ON = "public"."timecode_transmission" USING btree(channel_id ASC NULLS LAST); CREATE INDEX "timecode_transmission_episode_id" ON = "public"."timecode_transmission" USING btree(episode_id ASC NULLS LAST); CREATE INDEX "timecode_transmission_status" ON = "public"."timecode_transmission" USING btree(status ASC NULLS LAST); CREATE INDEX "timecode_transmission_tx" ON = "public"."timecode_transmission" USING btree(tx ASC NULLS LAST); Query plans (PG 9.1, dev): With enable_seqscan=3Don: HashAggregate (cost=3D3722291.37..3722301.78 rows=3D1041 width=3D12) = (actual time=3D255056.070..255056.167 rows=3D1074 loops=3D1) -> Hash Right Join (cost=3D68.20..3721927.54 rows=3D72766 width=3D12) = (actual time=3D229054.781..255005.491 rows=3D415193 loops=3D1) Hash Cond: (twitter_tweet.transmission_id =3D = timecode_transmission.id) -> Seq Scan on twitter_tweet (cost=3D0.00..3628135.86 = rows=3D24798886 width=3D12) (actual time=3D0.003..251157.607 = rows=3D24799190 loops=3D1) -> Hash (cost=3D55.18..55.18 rows=3D1041 width=3D4) (actual = time=3D0.922..0.922 rows=3D1074 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 38kB -> Index Scan using timecode_transmission_tx on = timecode_transmission (cost=3D0.00..55.18 rows=3D1041 width=3D4) = (actual time=3D0.023..0.659 rows=3D1074 loops=3D1) Index Cond: ((tx <=3D '2012-04-06 = 23:59:59+01'::timestamp with time zone) AND (tx >=3D '2012-04-06 = 00:00:00+01'::timestamp with time zone)) Total runtime: 255083.009 ms With enable_seqscan=3Doff: GroupAggregate (cost=3D0.00..5972744.82 rows=3D1041 width=3D12) (actual = time=3D63.504..272.790 rows=3D1074 loops=3D1) -> Nested Loop Left Join (cost=3D0.00..5972370.58 rows=3D72766 = width=3D12) (actual time=3D63.498..244.115 rows=3D415193 loops=3D1) -> Index Scan using timecode_transmission_pkey on = timecode_transmission (cost=3D0.00..13520.56 rows=3D1041 width=3D4) = (actual time=3D63.486..68.130 rows=3D1074 loops=3D1) Filter: ((tx <=3D '2012-04-06 23:59:59+01'::timestamp with = time zone) AND (tx >=3D '2012-04-06 00:00:00+01'::timestamp with time = zone)) -> Index Scan using twitter_tweet_transmission_id on = twitter_tweet (cost=3D0.00..5677.78 rows=3D3710 width=3D12) (actual = time=3D0.002..0.130 rows=3D386 loops=3D1074) Index Cond: (timecode_transmission.id =3D transmission_id) Total runtime: 272.871 ms Query plans (PG 8.4, prod): With enable_seqscan=3Don: HashAggregate (cost=3D4931753.82..4931766.82 rows=3D1040 width=3D12) = (actual time=3D130720.665..130720.873 rows=3D1071 loops=3D1) -> Hash Left Join (cost=3D4205000.99..4931360.42 rows=3D78679 = width=3D12) (actual time=3D126841.549..130645.028 rows=3D421286 loops=3D1)= Hash Cond: (timecode_transmission.id =3D = twitter_tweet.transmission_id) -> Index Scan using timecode_transmission_tx on = timecode_transmission (cost=3D0.00..57.25 rows=3D1040 width=3D4) = (actual time=3D8.730..19.873 rows=3D1071 loops=3D1) Index Cond: ((tx <=3D '2012-04-06 23:59:59+00'::timestamp = with time zone) AND (tx >=3D '2012-04-06 00:00:00+00'::timestamp with = time zone)) -> Hash (cost=3D3736125.66..3736125.66 rows=3D26973466 = width=3D12) (actual time=3D126824.846..126824.846 rows=3D23224197 = loops=3D1) -> Seq Scan on twitter_tweet (cost=3D0.00..3736125.66 = rows=3D26973466 width=3D12) (actual time=3D8.084..117210.707 = rows=3D25165171 loops=3D1) Total runtime: 130729.920 ms With enable_seqscan=3Doff; GroupAggregate (cost=3D0.00..6117044.05 rows=3D1040 width=3D12) (actual = time=3D72.242..470.289 rows=3D1071 loops=3D1) -> Nested Loop Left Join (cost=3D0.00..6116637.65 rows=3D78681 = width=3D12) (actual time=3D72.180..426.434 rows=3D421286 loops=3D1) -> Index Scan using timecode_transmission_pkey on = timecode_transmission (cost=3D0.00..13610.82 rows=3D1040 width=3D4) = (actual time=3D72.164..78.575 rows=3D1071 loops=3D1) Filter: ((tx <=3D '2012-04-06 23:59:59+00'::timestamp with = time zone) AND (tx >=3D '2012-04-06 00:00:00+00'::timestamp with time = zone)) -> Index Scan using twitter_tweet_transmission_id on = twitter_tweet (cost=3D0.00..5816.73 rows=3D4125 width=3D12) (actual = time=3D0.003..0.277 rows=3D393 loops=3D1071) Index Cond: (timecode_transmission.id =3D = twitter_tweet.transmission_id) Total runtime: 470.421 ms Non-default configuration (dev, PG9.1): default_statistics_target =3D 100 # pgtune wizard 2012-01-11 maintenance_work_mem =3D 400MB # pgtune wizard 2012-01-11 constraint_exclusion =3D partition # pgtune wizard 2012-01-11 checkpoint_completion_target =3D 0.9 # pgtune wizard 2012-01-11 effective_cache_size =3D 7552MB # pgtune wizard 2012-01-11 work_mem =3D 50MB # pgtune wizard 2012-01-11 wal_buffers =3D 16MB # pgtune wizard 2012-01-11 checkpoint_segments =3D 16 # pgtune wizard 2012-01-11 shared_buffers =3D 3840MB # pgtune wizard 2012-01-11 max_connections =3D 80 # pgtune wizard 2012-01-11 Non-default configuration (prod, PG8.4) maintenance_work_mem =3D 960MB checkpoint_completion_target =3D 0.9 checkpoint_segments =3D 32 wal_buffers =3D 16MB effective_cache_size =3D 11GB shared_buffers =3D 4GB max_connections =3D 32 work_mem =3D 100MB -- Dan Fairs | dan.fairs@gmail.com | www.fezconsulting.com --Apple-Mail=_D61AE1CB-17AB-4895-B32C-0A2B63012594 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
        =      COUNT("twitter_tweet"."id") AS = "tweet_count"
FROM "timecode_transmission"
LEFT = OUTER JOIN "twitter_tweet" ON ("timecode_transmission"."id" =3D = "twitter_tweet"."transmission_id")
WHERE = "timecode_transmission"."tx" <=3D '2012-04-06 = 23:59:59'
    AND "timecode_transmission"."tx" >=3D= '2012-04-06 00:00:00'
GROUP BY = "timecode_transmission"."id"

The twitter_tweet = table has about 25m rows, as you'll see from the query = plans.

Any hints are = appreciated.

Cheers,
Dan

=

Table = definitions:

CREATE TABLE = "public"."twitter_tweet" (
"id" int8 NOT NULL DEFAULT = nextval('twitter_tweet_id_seq'::regclass),
= "twitter_id" int8 NOT NULL,
= "created_at" timestamp(6) WITH TIME ZONE NOT = NULL,
= "from_user" varchar(20) NOT NULL,
"text" = text NOT NULL,
"from_user_id" int8 NOT = NULL,
= "sentiment" float8 NOT NULL,
= "transmission_id" int4,
"from_user_name" = varchar(500),
"gender" int2,
= CONSTRAINT "twitter_tweet_pkey" PRIMARY KEY ("id") NOT DEFERRABLE = INITIALLY IMMEDIATE,
CONSTRAINT = "transmission_id_refs_id_23b9da6852fe9f37" FOREIGN KEY = ("transmission_id") REFERENCES "public"."timecode_transmission" ("id") = ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED = ,
= CONSTRAINT "ck_gender_pstv_4bc0eb22f3ec191e" CHECK ((gender >=3D= 0)) NOT DEFERRABLE INITIALLY IMMEDIATE,
= CONSTRAINT "twitter_tweet_gender_check" CHECK ((gender >=3D = 0)) NOT DEFERRABLE INITIALLY IMMEDIATE,
= CONSTRAINT "twitter_tweet_klout_score_check" CHECK ((klout_score = >=3D 0)) NOT DEFERRABLE INITIALLY = IMMEDIATE
)
WITH (OIDS=3DFALSE);
CREATE = INDEX "twitter_tweet_created_at" ON "public"."twitter_tweet" USING = btree(created_at ASC NULLS LAST);
CREATE INDEX = "twitter_tweet_transmission_id" ON "public"."twitter_tweet" USING = btree(transmission_id ASC NULLS = LAST);

CREATE TABLE = "public"."timecode_transmission" (
"id" int4 = NOT NULL DEFAULT = nextval('timecode_transmission_id_seq'::regclass),
= "episode_id" int4 NOT NULL,
= "channel_id" int4 NOT NULL,
"tx" = timestamp(6) WITH TIME ZONE NOT NULL,
"status" = varchar(100) NOT NULL,
"end" timestamp(6) WITH TIME ZONE = NOT NULL,
"duration" int4,
= "include_in_listings" bool NOT NULL,
= CONSTRAINT "timecode_transmission_pkey" PRIMARY KEY ("id") NOT = DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT = "channel_id_refs_id_42fae8846ea37b15" FOREIGN KEY ("channel_id") = REFERENCES "public"."timecode_channel" ("id") ON UPDATE NO ACTION ON = DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ,
= CONSTRAINT "episode_id_refs_id_52ab388b54a13ff3" FOREIGN KEY = ("episode_id") REFERENCES "public"."timecode_episode" ("id") ON UPDATE = NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED = ,
= CONSTRAINT "timecode_transmission_tx_72eeb3dac42e185_uniq" UNIQUE = ("tx", "channel_id") NOT DEFERRABLE INITIALLY IMMEDIATE,
= CONSTRAINT "timecode_transmission_duration_check" CHECK = ((duration >=3D 0)) NOT DEFERRABLE INITIALLY = IMMEDIATE
)
WITH (OIDS=3DFALSE);
CREATE = INDEX "timecode_transmission_channel_id" ON = "public"."timecode_transmission" USING btree(channel_id ASC NULLS = LAST);
CREATE INDEX "timecode_transmission_episode_id" ON = "public"."timecode_transmission" USING btree(episode_id ASC NULLS = LAST);
CREATE INDEX "timecode_transmission_status" ON = "public"."timecode_transmission" USING btree(status ASC NULLS = LAST);
CREATE INDEX "timecode_transmission_tx" ON = "public"."timecode_transmission" USING btree(tx ASC NULLS = LAST);

Query plans (PG 9.1, = dev):

With = enable_seqscan=3Don:
HashAggregate =  (cost=3D3722291.37..3722301.78 rows=3D1041 width=3D12) (actual = time=3D255056.070..255056.167 rows=3D1074 loops=3D1)
  = ->  Hash Right Join  (cost=3D68.20..3721927.54 rows=3D72766 = width=3D12) (actual time=3D229054.781..255005.491 rows=3D415193 = loops=3D1)
        Hash Cond: = (twitter_tweet.transmission_id =3D = timecode_transmission.id)
        -> =  Seq Scan on twitter_tweet  (cost=3D0.00..3628135.86 = rows=3D24798886 width=3D12) (actual time=3D0.003..251157.607 = rows=3D24799190 loops=3D1)
        -> =  Hash  (cost=3D55.18..55.18 rows=3D1041 width=3D4) (actual = time=3D0.922..0.922 rows=3D1074 loops=3D1)
    =           Buckets: 1024  Batches: 1 =  Memory Usage: 38kB
          =     ->  Index Scan using timecode_transmission_tx on = timecode_transmission  (cost=3D0.00..55.18 rows=3D1041 width=3D4) = (actual time=3D0.023..0.659 rows=3D1074 loops=3D1)
  =                   Index = Cond: ((tx <=3D '2012-04-06 23:59:59+01'::timestamp with time zone) = AND (tx >=3D '2012-04-06 00:00:00+01'::timestamp with time = zone))
Total runtime: 255083.009 = ms

With = enable_seqscan=3Doff:
GroupAggregate =  (cost=3D0.00..5972744.82 rows=3D1041 width=3D12) (actual = time=3D63.504..272.790 rows=3D1074 loops=3D1)
  -> =  Nested Loop Left Join  (cost=3D0.00..5972370.58 rows=3D72766 = width=3D12) (actual time=3D63.498..244.115 rows=3D415193 = loops=3D1)
        ->  Index Scan = using timecode_transmission_pkey on timecode_transmission =  (cost=3D0.00..13520.56 rows=3D1041 width=3D4) (actual = time=3D63.486..68.130 rows=3D1074 loops=3D1)
    =           Filter: ((tx <=3D '2012-04-06 = 23:59:59+01'::timestamp with time zone) AND (tx >=3D '2012-04-06 = 00:00:00+01'::timestamp with time zone))
      =   ->  Index Scan using twitter_tweet_transmission_id on = twitter_tweet  (cost=3D0.00..5677.78 rows=3D3710 width=3D12) = (actual time=3D0.002..0.130 rows=3D386 loops=3D1074)
  =             Index Cond: = (timecode_transmission.id =3D transmission_id)
Total runtime: = 272.871 ms

Query plans (PG 8.4, = prod):

With = enable_seqscan=3Don:
HashAggregate =  (cost=3D4931753.82..4931766.82 rows=3D1040 width=3D12) (actual = time=3D130720.665..130720.873 rows=3D1071 loops=3D1)
  = ->  Hash Left Join  (cost=3D4205000.99..4931360.42 = rows=3D78679 width=3D12) (actual time=3D126841.549..130645.028 = rows=3D421286 loops=3D1)
        Hash = Cond: (timecode_transmission.id =3D = twitter_tweet.transmission_id)
        = ->  Index Scan using timecode_transmission_tx on = timecode_transmission  (cost=3D0.00..57.25 rows=3D1040 width=3D4) = (actual time=3D8.730..19.873 rows=3D1071 loops=3D1)
  =             Index Cond: ((tx <=3D = '2012-04-06 23:59:59+00'::timestamp with time zone) AND (tx >=3D = '2012-04-06 00:00:00+00'::timestamp with time zone))
  =       ->  Hash  (cost=3D3736125.66..3736125.66= rows=3D26973466 width=3D12) (actual time=3D126824.846..126824.846 = rows=3D23224197 loops=3D1)
          =     ->  Seq Scan on twitter_tweet =  (cost=3D0.00..3736125.66 rows=3D26973466 width=3D12) (actual = time=3D8.084..117210.707 rows=3D25165171 loops=3D1)
Total = runtime: 130729.920 ms

With = enable_seqscan=3Doff;
GroupAggregate =  (cost=3D0.00..6117044.05 rows=3D1040 width=3D12) (actual = time=3D72.242..470.289 rows=3D1071 loops=3D1)
  -> =  Nested Loop Left Join  (cost=3D0.00..6116637.65 rows=3D78681 = width=3D12) (actual time=3D72.180..426.434 rows=3D421286 = loops=3D1)
        ->  Index Scan = using timecode_transmission_pkey on timecode_transmission =  (cost=3D0.00..13610.82 rows=3D1040 width=3D4) (actual = time=3D72.164..78.575 rows=3D1071 loops=3D1)
    =           Filter: ((tx <=3D '2012-04-06 = 23:59:59+00'::timestamp with time zone) AND (tx >=3D '2012-04-06 = 00:00:00+00'::timestamp with time zone))
      =   ->  Index Scan using twitter_tweet_transmission_id on = twitter_tweet  (cost=3D0.00..5816.73 rows=3D4125 width=3D12) = (actual time=3D0.003..0.277 rows=3D393 loops=3D1071)
  =             Index Cond: = (timecode_transmission.id =3D = twitter_tweet.transmission_id)
Total runtime: 470.421 = ms

Non-default configuration (dev, = PG9.1):
default_statistics_target =3D 100 # pgtune wizard = 2012-01-11
maintenance_work_mem =3D 400MB # pgtune wizard = 2012-01-11
constraint_exclusion =3D partition # pgtune wizard = 2012-01-11
checkpoint_completion_target =3D 0.9 # pgtune = wizard 2012-01-11
effective_cache_size =3D 7552MB # pgtune = wizard 2012-01-11
work_mem =3D 50MB # pgtune wizard = 2012-01-11
wal_buffers =3D 16MB # pgtune wizard = 2012-01-11
checkpoint_segments =3D 16 # pgtune wizard = 2012-01-11
shared_buffers =3D 3840MB # pgtune wizard = 2012-01-11
max_connections =3D 80 # pgtune wizard = 2012-01-11

Non-default configuration = (prod, PG8.4)
maintenance_work_mem =3D = 960MB
checkpoint_completion_target =3D = 0.9
checkpoint_segments =3D 32
wal_buffers =3D = 16MB
effective_cache_size =3D 11GB
shared_buffers =3D = 4GB
max_connections =3D 32
work_mem =3D = 100MB


--
Dan Fairs | dan.fairs@gmail.com | www.fezconsulting.com

= --Apple-Mail=_D61AE1CB-17AB-4895-B32C-0A2B63012594--