Re: how to vacuum thru psycopg

Just as a quick note, full documentation and layout can be found at:
http://picturelibrary.sourceforge.net/

I have not been there in a while and things are really really bare
bones, but the documentation is fairly complete. This sections
specifically covers the database:
http://picturelibrary.sourceforge.net/developerManual.html.LyXconv/#x1-8400011

and then how well implementation matches my ideals is:
http://picturelibrary.sourceforge.net/developerManual.html.LyXconv/#x1-9800017.1

I will now go back and answer the more specific questions.

On Fri, 2009-06-12 at 11:58 -0700, Tim Roberts wrote:
> Al Niessner wrote:
> > You all are probably going to hate me for this, but I need to give you a
> > picture to help clarify what I am talking about.
> 
> That's a very revealing graph.  I know you came in hoping to solve a
> very focused problem, and I hope you will not be upset if I veer off to
> explore your database layout a bit.  I'm still not 100% clear on your
> schema, but I have a suspicion that your trouble is coming from the schema.
> 
> > If you do not know, EXIF data is a set of name-value pairs. For brevity,
> > EXIF uses a 16 bit int for its name and then some variable length number
> > of bytes for the value. Of the images being scanned, it is normal for
> > the EXIF data to be 40 or 44 pairs in size, but there are some that are
> > zero. Of these 44 or so pairs, only about 10 of them change. So, what I
> > do is scan the table to see if the pair exists. If it does, then I leave
> > it be and just point the image at it (another table with 2 foreign keys
> > relating the picture to the pair). So, pair table should never really
> > have that many entries. The table that relates the picture to the pair
> > is huge (number of pictures * 44) but it is just a primary key and two
> > foreign keys, which should be very fast.
> >   
> 
> OK, so you have a table with EXIF key, EXIF value, and a unique ID. 
> Then, you have another table with picture number, EXIF key, and the
> unique ID from the EXIF table.  Is that right?
> 
> How are you determining whether the pair exists?  Are you actually doing
> a SELECT for each pair?  That is, you're doing 44 SELECTs for every
> picture before doing the INSERT?  Since each one of those is a server
> round trip, doesn't it seem obvious that this is where your performance
> is being eaten up?
> 
> You're doing these extra lookups just to save disk space, right?  I
> would assert that this is a false optimization.  If it were me, I'd just
> have a table with picture number, EXIF key, and EXIF value, and store
> each image's keys in there unconditionally.  You'll have the same number
> of records as in your relation table, but the records will be a bit
> larger.  You're talking about, what, maybe 400 bytes of EXIF data per
> image?  That's well under 10 megabytes total.
> 
> I think you are making a lot of extra work for yourself with no tangible
> benefit.
> 
> > So, I have this huge time sink that is strictly database related and my
> > best suggestion so far was to vacuum once in a while. I am welcome to
> > entertain other suggestions.
> >   
> 
> Never let it be said that I hesitated in giving unwanted advice.
> 
-- 
Al Niessner

I have never found the companion that was so companionable as solitude.
- From Walden by Henry David Thoreau

The universe is indifferent, and life is brutal; however, it is man's
choice of behavior that makes them malevolent rather than benevolent.

Some will fall in love with life and drink it from a fountain
That is pouring like an avalanche coming down the mountain.
- From the song Pepper by the Butthole Surfers