Re: how to vacuum thru psycopg

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.

-- 
Tim Roberts, [email protected]
Providenza & Boekelheide, Inc.