Re: Tweaking PG (again)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com>, "PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Tweaking PG (again)
Date: 2008-11-14 02:57:56
Message-ID: 16694.1226631476@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Thu, Nov 13, 2008 at 2:04 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-11-14 03:23:05 Re: vacuum output question
Previous Message Eus 2008-11-14 02:39:47 Re: how to "group" several records with same timestamp into one line?