X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 22FCC56420 for ; Thu, 17 Mar 2005 16:21:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90243-10 for ; Thu, 17 Mar 2005 16:21:41 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id EDC9B5643B for ; Thu, 17 Mar 2005 16:21:39 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j2HGLUk4020685; (envelope-from ) Thu, 17 Mar 2005 10:21:36 -0600 (CST) Received: from [192.168.1.13] (12-217-241-0.client.mchsi.com [12.217.241.0]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j2HGGWgu001278 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Thu, 17 Mar 2005 10:16:33 -0600 (CST) Message-ID: <4239AD60.3060607@arbash-meinel.com> Date: Thu, 17 Mar 2005 10:16:32 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Alexander Ranaldi Cc: pgsql-performance@postgresql.org Subject: Re: Building a DB with performance in mind References: In-Reply-To: X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig7590E6DC0DEF68DEE6934DB7" X-Virus-Scanned: ClamAV 0.83/723/Thu Feb 24 05:54:24 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/360 X-Sequence-Number: 11065 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig7590E6DC0DEF68DEE6934DB7 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Alexander Ranaldi wrote: >Greetings everyone, > >I am about to migrate to Postgres from MySQL. My DB isn't enormous (< >1gb), consists mostly of just text, but is accessed quite heavily. >Because size isn't a huge issue, but performance is, I am willing to >normalize as necessary. > >Currently I have a table "Entries" containing 500k rows. The table >contains many text columns, and a few others: >EntryID (unique, indexed) >UserID (references "Users" table, indexed) >Private (boolean. indexed) > >Most of my queries return rows based on UserID, and also only if >Private is FALSE. Would it be in the interest of best performance to >split this table into two tables: "EntriesPrivate", >"EntriesNotPrivate" and remove the "Private" column? > > Perhaps. You might also consider creating a multi-column index on (UserID, Private). However, in a more conceptual idea, separating the tables may help you with preventing accidental queries. It's pretty easy to forget to add "... AND Private = False". It is much harder to accidentally add "... JOIN EntriesPrivate ON ..." >I appreciate any feedback. I'm certainly not a DB design expert. :) > > > It shouldn't be very hard to test which one works better for you: \timing CREATE INDEX entries_user_private_idx ON Entries(UserID, Private); SELECT * FROM Entries WHERE ... AND Private = False; CREATE TABLE EntriesPrivate AS SELECT * FROM Entries WHERE Private=True; CREATE TABLE EntriesPublic AS SELECT * FROM Entries WHERE Private=False; ALTER TABLE EntriesPrivate DROP COLUMN Private; ALTER TABLE EntriesPrivate ADD PRIMARY KEY (EntriesID); ALTER TABLE EntriesPrivate ALTER COLUMN SET DEFAULT=nextval('Entries_...EntryId'); -- Make sure you don't have duplicate entries. This could also be done with a foreign key to some -- other entries table ALTER TABLE EntriesPrivate ADD CONSTRAINT EntriesID NOT in (SELECT EntriesId FROM EntriesPublic); CREATE INDEX entriesprivate_userid_idx ON EntriesPrivate(UserID); -- Do the same thing for EntriesPublic ALTER TABLE EntriesPublic DROP COLUMN Private; These queries have not been tested, but they should give you a decent starting point to creating 2 tables, and running a bunch of test queries on them. I think the biggest difficulty is making sure that you don't get duplicate EntriesID values, assuming that is important to you. Also, if you have foreign key references, this won't work. You'll have to create a new table (it can have just 1 column) containing EntriesID, and then you can reference that column from both of these tables. John =:-> >Thanks, >Alex > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > --------------enig7590E6DC0DEF68DEE6934DB7 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCOa1hJdeBCYSNAAMRAlaRAKCwUtp9Q6dREzvIfNt7qBFZ/I2ayACeJMIk 2QEQXEigMTWnsG3olKo4Jdo= =GapE -----END PGP SIGNATURE----- --------------enig7590E6DC0DEF68DEE6934DB7--