From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Madison Kelly <linux@alteeve.com>
Subject: Re: Need suggestion high-level suggestion on how to solve a
	performance problem
Date: Thu, 7 Jul 2005 18:04:28 -0700
User-Agent: KMail/1.8
Cc: pgsql-performance@postgresql.org
References: <42CD2A7A.10603@alteeve.com>
In-Reply-To: <42CD2A7A.10603@alteeve.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200507071804.29189.josh@agliodbs.com>

Madison,

> =A0 =A0The problem comes when the user toggles a directory branch's backup
> flag (a simple check box beside the directory name). If it's a directory
> near the end of a branch it is fast enough. If they toggle a single file
> it is nearly instant. However if they toggle say the root directory, so
> every file and directory below it needs to be updated, it can take
> 500-600sec to return. Obviously this is no good.
>
> =A0 =A0What I need is a scheme for being able to say, essentially:
>
> UPDATE file_info_1 SET file_backup=3D't' WHERE file_parent_dir~'^/';

Well, from the sound of it the problem is not selecting the files to be=20
updated, it's updating them.  =20

What I would do, personally, is *not* store an update flag for each file. =
=20
Instead, I would store the update flag for the directory which was=20
selected.  If users want to exclude certain files and subdirectories, I'd=20
also include a dont_update flag.  When it's time to back up, you simply=20
check the tree for the most immediate update or don't update flag above=20
each file.

=46or the table itself, I'd consider using ltree for the directory tree=20
structure.  It has some nice features which makes it siginifcanly better=20
than using a delimited text field.

=2D-=20
=2D-Josh

Josh Berkus
Aglio Database Solutions
San Francisco