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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
