Re: how to vacuum thru psycopg

Al Niessner wrote:
> ...
> From the metadata table and picture table, I see there is about 43
> elements per photo which is what I expect. The three tables that I
> select from each transaction are group_name, pair_name, and pair_value.
> I do not see these growing linear with insertion over 2000 insertions.
> If it is taking postgresql 30 seconds to scan 6000 elements, then I
> think there is a bit of an issue with the database.
>   

I think you are still missing an order of magnitude here.  It's not
taking Postgres 30 seconds to scan 6,000 elements and return a dataset,
but it is quite possible that it takes 30 seconds to do that same
operation 44 times!

That's my point.  For EACH and EVERY new image, you generate 44 queries
and (on average) 46 insertions.  The insertions can all be batched up
and sent as a unit, but EVERY ONE of those select queries requires a
complete round trip to the server and back so you can fetch the data. 
And you're doing this in the middle of a transaction, right?  That means
that it has to search both the committed tables and the temporary data
that has accumulated during your transaction.  I don't know it for a
fact, but I wouldn't be surprised to learn that queries within the
temporary data in an uncommitted transaction are slower.

I maintain that it is those SELECTs that are killing you.

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