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 B7C1752E97 for ; Tue, 2 Aug 2005 04:05:59 -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 12448-05 for ; Tue, 2 Aug 2005 07:05:50 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by svr1.postgresql.org (Postfix) with ESMTP id 032AA52E96 for ; Tue, 2 Aug 2005 04:05:48 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i4so1162935wra for ; Tue, 02 Aug 2005 00:05:50 -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=A6mAIeGuY5dNZIzyOJIpQxh/LfPDIZDEvq3UmrT3GMMr6qM4IF8pb6vdpcVcaxN4xeo5TNqb6r0XhfxO0u0LAErHlyjbORUtilr+Rkp1u3CVoiM5x10SqNczvE+yX9CWhtuBaivsZRkqlR+NUvbJwMARog3+wCHcblu9b1Z2hlg= Received: by 10.54.2.57 with SMTP id 57mr3555676wrb; Tue, 02 Aug 2005 00:05:50 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Tue, 2 Aug 2005 00:05:50 -0700 (PDT) Message-ID: Date: Tue, 2 Aug 2005 09:05:50 +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: <26292.1122941726@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3703_21659964.1122966350462" 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.185 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/18 X-Sequence-Number: 13765 ------=_Part_3703_21659964.1122966350462 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks Tom, That modifies the query plan slightly, but the planner still decides to do = a=20 hash join for the lte_user table aliased 't'. Though, if I make this change= =20 and set enable_hashjoin to off, the query plan (and execution time) gets=20 even better. 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 tim= e=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 time= =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=3D3= 2)=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) (= actual=20 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=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 time= =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 time= =3D 0.000..157.000 rows=3D3467 loops=3D1) -> Seq Scan on candidates617004 c (cost=3D0.00..76.34 rows=3D3467 width=3D3= 2)=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 Thanks, Meetesh 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 > > 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=3D20 > > 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, > where ... AND c.sourceId =3D 617004 ... >=20 > regards, tom lane > ------=_Part_3703_21659964.1122966350462 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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

On 8/2/05, Tom 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. Du= ring some processing, we create a candidates=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_3703_21659964.1122966350462--