"Scott Marlowe" <scott.marlowe@xxxxxxxxx> writes: > On Thu, Nov 13, 2008 at 2:04 PM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote: >> 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. Yeah, but note that the planner knows darn well that this will be an expensive query --- 493427.14 cost units estimated to fetch 2 rows! My interpretation is that the condition on user_id is horribly nonselective (at least for this value of user_id) and the planner knows it. The condition on url_encrypted *is* selective, and the planner knows that too, but there's nothing it can do about it --- the best available plan is to fetch all the rows matching by user_id and then filter them on url_encrypted. Consider creating an index on url_encrypted if you need this type of query to go fast. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
