Fwd: Tweaking PG (again)

Thanks Scott. Responses below.

>>
>> (1) The culprit SELECT sql is (note that "MYUSER" in this example can
>> be an IP address) --
>
> So, it can be, but might not be? Darn, If it was always an ip I'd
> suggest changing types.
>

Yes, it can either be a registered USER ID or an IP address. I thought
of having two separate fields, where one is null or the other, and
then indexing the concatenation of those two which I could use for the
SQL. But it's difficult to revamp whole code. Instead of that, I have
"user_known". If user_known is 1, then it's a user_id, otherwise it's
an IP address. This is quicker than regexping for IP pattern
everytime.

>> explain analyze SELECT alias, id, title, private_key, aliasEntered
>> FROM books
>> WHERE user_id = 'MYUSER' AND url_encrypted =
>> 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4' ;
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------------------
>> Index Scan using new_idx_books_userid on books (cost=0.00..493427.14
>> rows=2 width=31) (actual time=0.428..8400.299 rows=1 loops=1)
>> Index Cond: ((user_id)::text = 'MYUSER'::text)
>> Filter: (url_encrypted = 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4'::bpchar)
>> Total runtime: 8400.349 ms
>> (4 rows)
>
> 8.4 seconds is a very long time to spend looking up a single record.
> Is this table bloated? What does
>
> vacuum verbose books;
>
> say about it? Look for a line like this:
>
> There were 243 unused item pointers

Thanks but this table "books" has autovac on, and it's manually
vacuumed every hour!

>> (2) The culprit INSERT sql is as follows
>>
>> explain analyze
>> INSERT INTO books (id, book_id, url, user_known, user_id,
>> url_encrypted, alias, title, private_key, status, modify_date)
>> values
>> (
>> 9107579
>> ,'5f7gb'
>> ,'http://www.google.com'
>> ,'0'
>> ,'MYUSER'
>> ,'73684da5ef05d9589f95d8ba9e4429ea062549c7'
>> ,'5f7gb'
>> ,''
>> ,''
>> ,'Y'
>> ,now()
>> )
>> ;
>>
>> QUERY PLAN
>> ------------------------------------------------------------------------------------
>> Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.017..0.022
>> rows=1 loops=1)
>> Total runtime: 106.747 ms
>> (2 rows)
>>
>> Time: 3421.424 ms
>
> When the total run time measured by explain analyze is much lower than
> the actual run time, this is usually either a trigger firing / fk
> issue, or you've got a really expensive (cpu wise) time function on
> your OS. Since there's only one loop here, I'm gonna guess that
> you've got some FK stuff going on. Got a related fk/pk field in
> another table that needs an index? I thought that 8.3 gave some info
> on that stuff in explain analyze, but I'm not really sure.

Yes there is a table VISITCOUNT that has a foreign key on books(id).
But why should that be invoked? Shouldn't that fk be called into
question only when a row is being inserted/updated in VISITCOUNT table
and not BOOKS?

Thanks!