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!