Re: how to vacuum thru psycopg

Ooops, sorry about that. My response got away from there.

Yes, I do a select each time. However, the list is VERY limited in size
so why would the query grow linearly with insertions? It may be
inefficient, but it should be bounded; and, I think bounded to a small
value because the first 10 or 20 photos contain all of the unique
values. Hence my confusion at the linear growth.

All that said, I need to peek at my db and make sure the tables look
like I think they should and that I do not have a programming error. I
have not done that yet because it is just more learning on my part since
I have never done it before...

On Fri, 2009-06-12 at 15:07 -0700, Tim Roberts wrote: 
> Al Niessner wrote:
> > On Fri, 2009-06-12 at 11:58 -0700, Tim Roberts wrote:
> >   
> >> 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?
> >
> > Yes, I do agree that if I bundled them up it would speed things up.
> > However, since there are only a fixed number of unique values and that
> > set is smaller than 10000 and is probably less than 1000, I do not think
> > that is responsible for the linear time growth.
> >   
> 
> Your response leaves my primary question unanswered.  When you get a new
> image, and you have 44 key/value pairs, how do you discover whether
> those pairs already exist in the key/value table?  Are you keeping a
> Python dictionary in memory, or are you doing a SELECT for each of the
> 44 pairs?  If you are doing a SELECT for each pair, then the number of
> unique values is not relevent.  The key is that you are doing 44 SELECT
> calls per image.  10,000 images with 44 SELECTs is going to result in a
> half-million database queries over a table that is getting progressively
> larger.
> 
> If you're doing it with a Python dictionary, then it will still be
> linear in time, but it will be a much shorter time.
> 
-- 
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