Message-ID: <4FD38ED4.3020003@consistentstate.com>
Date: Sat, 09 Jun 2012 11:58:44 -0600
From: Kevin Kempter <cs_dba@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: partitioning performance question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi All;

We have a client that has a table where large blobs (bytea) are stored. 
the table has a key column that is numbers (like 112362) but 
unfortunately it's a varchar column so the blobs are accessed via 
queries like:

select * from bigtable where keycol = '217765'

The primary reason we want to partition the table is for maintenance, 
the table is well over 1.2 Terabytes in size and they have never 
successfully vacuumed it. However I don't want to make performance even 
worse. The table does have a serial key, I'm thinking the best options 
will be to partition by range on the serial key, or maybe via the keycol 
character column via using an in statement on the check constraints, 
thus allowing the planner to actually leverage the above sql.  I suspect 
doing a typecast to integer in the check constraints will prove to be a 
bad idea if the keycol column remains a varchar.

Thoughts?

Here's the table:


                                         Table "problemchild"
   Column   |           Type           |                             
Modifiers
-----------+--------------------------+-------------------------------------------------------------------- 

  keycol                | character varying        |
  blob_data           | bytea                    |
  removed_date    | timestamp with time zone |
  alt_key | bigint                   | not null default 
nextval('problemchild_alt_key_seq'::regclass)
Indexes:
     "pc_pkey" PRIMARY KEY, btree (alt_key)
     "key2" btree (keycol)


Thanks in advance