X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 78C2B52972 for ; Wed, 3 Aug 2005 09:48:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30383-05 for ; Wed, 3 Aug 2005 12:48:35 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by svr1.postgresql.org (Postfix) with ESMTP id 2796F52808 for ; Wed, 3 Aug 2005 09:48:34 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i8so119919wra for ; Wed, 03 Aug 2005 05:48:38 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=UXXQJcEQJk0qKskeAO5hn53xadj7ZwXK1ofiFZXH/xu+0XM4qtEDL7Aca1Gd+hEd+BMqyvbKt0CYQJuHZJE8EteOuvyxYW/5TuSYmgAIj7RCgDaDEM6Q4EK6gVbrgXezwMyxJXUhD9Ti7+2vVsS4eStEw/vMPn+rALnTk3uEfPU= Received: by 10.54.14.29 with SMTP id 29mr579846wrn; Wed, 03 Aug 2005 05:48:38 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Wed, 3 Aug 2005 05:48:38 -0700 (PDT) Message-ID: Date: Wed, 3 Aug 2005 14:48:38 +0200 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu To: Tom Lane Subject: Re: Planner incorrectly choosing seq scan over index scan Cc: John Arbash Meinel , pgsql-performance@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_247_16072047.1123073318453" References: <42EEAD4B.4010905@arbash-meinel.com> <26292.1122941726@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.177 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/27 X-Sequence-Number: 13774 ------=_Part_247_16072047.1123073318453 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Btw - I tried playing around with some of the other planner cost constants= =20 but I wasn't able to get the planner to choose the index scan. It seems lik= e=20 the issue is that the estimated cost for fetching one row from the index ( 3.02) is a little high in my case. Is there any way that I can adjust that= =20 cost estimate? Are there any side effects of doing that? Or is my best=20 solution to simple set enable_hashjoin to off for this query? Thanks, Meetesh On 8/2/05, Meetesh Karia wrote: >=20 > Thanks Tom, >=20 > That modifies the query plan slightly, but the planner still decides to d= o=20 > a hash join for the lte_user table aliased 't'. Though, if I make this=20 > change and set enable_hashjoin to off, the query plan (and execution time= )=20 > gets even better. >=20 > enable_hashjoin =3D on > ---------------------------------- > QUERY PLAN > Sort (cost=3D10113.35..10122.02 rows=3D3467 width=3D48) (actual time=3D > 1203.000..1203.000 rows=3D3467 loops=3D1) > Sort Key: c.sourceid, c.targetid > -> Nested Loop (cost=3D8711.19..9909.50 rows=3D3467 width=3D48) (actual t= ime=3D > 1156.000..1203.000 rows=3D3467 loops=3D1) > -> Index Scan using lte_user_pkey on lte_user s (cost=3D0.00..3.02 rows= =3D1=20 > width=3D16) (actual time=3D0.000..0.000 rows=3D1 loops=3D1) > Index Cond: (617004 =3D user_id) > -> Hash Join (cost=3D8711.19..9776.46 rows=3D3467 width=3D40) (actual tim= e=3D > 1156.000..1187.000 rows=3D3467 loops=3D1) > Hash Cond: ("outer".targetid =3D "inner".user_id) > -> Seq Scan on candidates617004 c (cost=3D0.00..76.34 rows=3D3467 width= =3D32)=20 > (actual time=3D0.000..16.000 rows=3D3467 loops=3D1) > Filter: (sourceid =3D 617004) > -> Hash (cost=3D8012.55..8012.55 rows=3D279455 width=3D16) (actual time= =3D > 1141.000..1141.000 rows=3D0 loops=3D1) > -> Seq Scan on lte_user t (cost=3D0.00..8012.55 rows=3D279455 width=3D16)= =20 > (actual time=3D0.000..720.000 rows=3D279395 loops=3D1) > Total runtime: 1218.000 ms >=20 > enable_hashjoin =3D off > ----------------------------------- > QUERY PLAN > Sort (cost=3D10942.56..10951.22 rows=3D3467 width=3D48) (actual time=3D > 188.000..188.000 rows=3D3467 loops=3D1) > Sort Key: c.sourceid, c.targetid > -> Nested Loop (cost=3D0.00..10738.71 rows=3D3467 width=3D48) (actual tim= e=3D > 0.000..188.000 rows=3D3467 loops=3D1) > -> Index Scan using lte_user_pkey on lte_user s (cost=3D0.00..3.02 rows= =3D1=20 > width=3D16) (actual time=3D0.000..0.000 rows=3D1 loops=3D1) > Index Cond: (617004 =3D user_id) > -> Nested Loop (cost=3D0.00..10605.67 rows=3D3467 width=3D40) (actual tim= e=3D > 0.000..157.000 rows=3D3467 loops=3D1) > -> Seq Scan on candidates617004 c (cost=3D0.00..76.34 rows=3D3467 width= =3D32)=20 > (actual time=3D0.000..15.000 rows=3D3467 loops=3D1) > Filter: (sourceid =3D 617004) > -> Index Scan using lte_user_pkey on lte_user t (cost=3D0.00..3.02 rows= =3D1=20 > width=3D16) (actual time=3D0.028..0.037 rows=3D1 loops=3D3467) > Index Cond: ("outer".targetid =3D t.user_id) > Total runtime: 188.000 ms >=20 > Thanks, > Meetesh >=20 > On 8/2/05, Tom Lane wrote: > >=20 > > Meetesh Karia writes: > > > Sure. The lte_user table is just a collection of users. user_id is=20 > > assigned=3D > > > uniquely using a sequence. During some processing, we create a=20 > > candidates=3D=20 > > > table (candidates617004 in our case). This table is usually a temp=20 > > table.=3D > > > sourceid is a user_id (in this case it is always 617004) and targetid= =20 > > is=3D20 > > > also a user_id (2860 distinct values out of 3467). The rest of the=3D= 20=20 > > > information is either only used in the select clause or not used at= =20 > > all=3D20 > > > during this processing. > >=20 > > If you know that sourceid has only a single value, it'd probably be > > helpful to call out that value in the query, ie,=20 > > where ... AND c.sourceId =3D 617004 ... > >=20 > > regards, tom lane > >=20 >=20 > ------=_Part_247_16072047.1123073318453 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Btw - I tried playing around with some of the other planner cost constants but I wasn't able to get the planner to choose the index scan.  It seems like the issue is that the estimated cost for fetching one row from the index (3.02) is a little high in my case.  Is there any way that I can adjust that cost estimate?  Are there any side effects of doing that?  Or is my best solution to simple set enable_hashjoin to off for this query?

Thanks,
Meetesh

On 8/2/05, Meetesh Karia <meetesh.karia@gmail.com> wrote:
Thanks Tom,

That modifies the query plan slightly, but the planner still decides to do a hash join for the lte_user table aliased 't'.  Though, if I make this change and set enable_hashjoin to off, the query plan (and execution time) gets even better.

enable_hashjoin =3D on
----------------------------------
QUERY PLAN
Sort  (cost=3D10113.35..10122.02 rows=3D3467 width=3D48) (actual time= =3D1203.000..1203.000 rows=3D3467 loops=3D1)
  Sort Key: c.sourceid, c.targetid
   ->  Nested Loop  (cost=3D8711.19..9909.50 rows=3D3467 width=3D48) (actual time=3D1156.000..1203.000 rows=3D3467 loops=3D1)
        ->  Index Scan using lte_user_pkey on lte_user s  (cost=3D0.00..3.02 rows=3D1 width=3D16) (actual time=3D0.000..0.000 rows=3D1 loops=3D1)
            &nb= sp; Index Cond: (617004 =3D user_id)
        ->  Hash Join  (cost=3D8711.19..9776.46 rows=3D3467 width=3D40) (actual time=3D1156.000..1187.000 rows=3D3467 loops=3D1)
            &nb= sp; Hash Cond: ("outer".targetid =3D "inner".user_id)             &nb= sp; ->  Seq Scan on candidates617004 c  (cost=3D0.00..76.34 rows=3D3467 width=3D32) (actual time=3D0.000..16.000 rows=3D3467 loops=3D1)=
            &nb= sp;       Filter: (sourceid =3D 617004)
            &nb= sp; ->  Hash  (cost=3D8012.55..8012.55 rows=3D279455 width=3D16) (actual time=3D1141.000..1141.000 rows=3D0 loops=3D1)
            &nb= sp;       ->  Seq Scan on lte_user t  (cost=3D0.00..8012.55 rows=3D279455 width=3D16) (actual time=3D0.000..720.000 rows=3D279395 loops= =3D1)
Total runtime: 1218.000 ms

enable_hashjoin =3D off
-----------------------------------
QUERY PLAN
Sort  (cost=3D10942.56..10951.22 rows=3D3467 width=3D48) (actual time= =3D188.000..188.000 rows=3D3467 loops=3D1)
  Sort Key: c.sourceid, c.targetid
   ->  Nested Loop  (cost=3D0.00..10738.71 rows=3D3467 wid= th=3D48) (actual time=3D0.000..188.000 rows=3D3467 loops=3D1)
        ->  Index Scan using lte_user_pkey on lte_user s  (cost=3D0.00..3.02 rows=3D1 width=3D16) (actual time=3D0.000..0.000 rows=3D1 loops=3D1)
            &nb= sp; Index Cond: (617004 =3D user_id)
        ->  Nested Loop  (cost=3D0.00..10605.67 rows=3D3467 width=3D40) (actual time=3D0.000..157.000 rows=3D3467 loops=3D1)
            &nb= sp; ->  Seq Scan on candidates617004 c  (cost=3D0.00..76.34 rows=3D3467 width=3D32) (actual time=3D0.000..15.000 rows=3D3467 loops=3D1)=
            &nb= sp;       Filter: (sourceid =3D 617004)
            &nb= sp; ->  Index Scan using lte_user_pkey on lte_user t  (cost=3D0.00..3.02 rows=3D1 width=3D16) (actual time=3D0.028..0.037 rows=3D= 1 loops=3D3467)
            &nb= sp;       Index Cond: ("outer".targetid =3D t.user_id)
 Total runtime: 188.000 ms

Thanks,
Meetesh


<= div>On 8/2/05, To= m Lane < tgl@sss.pgh.pa.us> wrote:
Meetesh Karia <meetesh.karia@= gmail.com> writes:
> Sure. The lte_user table is just a collec= tion of users. user_id is assigned=3D
> uniquely using a sequence. During some processing, we create a can= didates=3D
> table (candidates617004 in our case). This table is usually a temp= table.=3D
> sourceid is a user_id (in this case it is always 617004)= and targetid is=3D20
> also a user_id (2860 distinct values out of 3= 467). The rest of the=3D20
> information is either only used in the select clause or not used a= t all=3D20
> during this processing.

If you know that sourceid= has only a single value, it'd probably be
helpful to call out that valu= e in the query, ie,
        where ... AND c.sourceI= d =3D 617004 ...

        &nb= sp;            =    regards, tom lane


------=_Part_247_16072047.1123073318453--