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 6000252C36 for ; Mon, 1 Aug 2005 20:56:18 -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 07610-10 for ; Mon, 1 Aug 2005 23:56:10 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by svr1.postgresql.org (Postfix) with ESMTP id 04E3652975 for ; Mon, 1 Aug 2005 20:56:09 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i4so1113717wra for ; Mon, 01 Aug 2005 16:56:13 -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=PeOiPrwWnSbHxFREi+8ocmiW59eoHLVtllcMi9DCleHsrDVXW2/+rF6sP/v1cZAjaRFmU4kqQAmx7Rmo7rexL6fLQn8tNbdhFJT9symG+tPAQf6Yyg0YSreHbajvUkdBJ3g3JUrCnskoBT+8hGMUNqs53eTxjvUJ6Ysj8wEbQNY= Received: by 10.54.57.21 with SMTP id f21mr3130742wra; Mon, 01 Aug 2005 16:56:13 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Mon, 1 Aug 2005 16:56:12 -0700 (PDT) Message-ID: Date: Tue, 2 Aug 2005 01:56:13 +0200 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu To: John Arbash Meinel Subject: Re: Planner incorrectly choosing seq scan over index scan Cc: pgsql-performance@postgresql.org In-Reply-To: <42EEAD4B.4010905@arbash-meinel.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3573_25248839.1122940573051" References: <42EEAD4B.4010905@arbash-meinel.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=AWL, HTML_50_60, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/16 X-Sequence-Number: 13763 ------=_Part_3573_25248839.1122940573051 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks John. I've answered your questions below: Has lte_user and candidates617004 been recently ANALYZEd? All estimates, > except for the expected number of rows from lte_user seem to be okay. I ANALYZEd both tables just before putting together my first email. And,=20 unfortunately, modifying the statistics target didn't help either. Is user_id the primary key for lte_user? Yes=20 I'm trying to figure out how you can get 50k rows, by searching a > primary key, against a 3.5k rows. Is user_id only part of the primary > key for lte_user? Hmmm ... I missed that before. But, that surprises me too. Especially since= =20 sourceId in the candidates table has only 1 value. Also, user_id is the=20 complete primary key for lte_user. Can you give us the output of: > \d lte_user > \d candidates617004 Sure, here they are: lte=3D# \d lte_user Table "public.lte_user" Column | Type | Modifiers ---------------+-----------------------------+----------- user_id | bigint | not null firstname | character varying(255) | lastname | character varying(255) | address1 | character varying(255) | address2 | character varying(255) | city | character varying(255) | state | character varying(255) | zip | character varying(255) | phone1 | character varying(255) | phone2 | character varying(255) | username | character varying(255) | password | character varying(255) | deleted | boolean | not null ext_cust_id | character varying(255) | aboutme | character varying(255) | birthday | timestamp without time zone | fm_id | bigint | ar | double precision | Indexes: "lte_user_pkey" PRIMARY KEY, btree (user_id) "idx_user_extid" btree (ext_cust_id) "idx_user_username" btree (username) Foreign-key constraints: "fk_user_fm" FOREIGN KEY (fm_id) REFERENCES fm(fm_id) lte=3D# \d candidates617004 Table "public.candidates617004" Column | Type | Modifiers --------------+------------------+----------- fmid | bigint | sourceid | bigint | sr | double precision | targetid | bigint | tr | double precision |=20 Also, if you could describe the table layouts, that would help. Sure. The lte_user table is just a collection of users. user_id is assigned= =20 uniquely using a sequence. During some processing, we create a candidates= =20 table (candidates617004 in our case). This table is usually a temp table.= =20 sourceid is a user_id (in this case it is always 617004) and targetid is=20 also a user_id (2860 distinct values out of 3467). The rest of the=20 information is either only used in the select clause or not used at all=20 during this processing. Did I miss something in the table layout description that would be helpful? Thanks for your help! Meetesh > -> Sort (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual > > time=3D1156.000..1156.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid > > -> Hash Join (cost=3D8710.44..9708.21 rows=3D3467 width=3D40) > > (actual time=3D1125.000..1156.000 rows=3D3467 loops=3D1) > > Hash Cond: ("outer".targetid =3D "inner".user_id) > > -> Seq Scan on candidates617004 c > > (cost=3D0.00..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..0.000 > > rows=3D3467 loops=3D1) > > -> Hash (cost=3D8011.95..8011.95 rows=3D279395 > > width=3D16) (actual time=3D1125.000..1125.000 rows=3D0 loops=3D1) > > -> Seq Scan on lte_user t > > (cost=3D0.00..8011.95 rows=3D279395 width=3D16) (actual time=3D0.000..6= 70.000 > > rows=3D279395 loops=3D1) > > Total runtime: 1406.000 ms > > > > enable_hashjoin disabled > > ---------------------------------------- > > QUERY PLAN > > Sort (cost=3D14355.37..14364.03 rows=3D3467 width=3D48) (actual > > time=3D391.000..391.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid, c.targetid > > -> Nested Loop (cost=3D271.52..14151.51 rows=3D3467 width=3D48) (actual > > time=3D203.000..359.000 rows=3D3467 loops=3D1) > > -> Merge Join (cost=3D271.52..3490.83 rows=3D3467 width=3D40) > > (actual time=3D203.000..218.000 rows=3D3467 loops=3D1) > > Merge Cond: ("outer".user_id =3D "inner".sourceid) > > -> Index Scan using lte_user_pkey on lte_user s > > (cost=3D0.00..16837.71 rows=3D279395 width=3D16) (actual time=3D0.000..= 126.000 > > rows=3D50034 loops=3D1) > > -> Sort (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actual > > time=3D15.000..30.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid > > -> Seq Scan on candidates617004 c > > (cost=3D0.00..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..0.000 > > rows=3D3467 loops=3D1) > > -> Index Scan using lte_user_pkey on lte_user t > > (cost=3D0.00..3.03 rows=3D1 width=3D16) (actual time=3D0.031..0.036 row= s=3D1 > > loops=3D3467) > > Index Cond: ("outer".targetid =3D t.user_id) > > Total runtime: 406.000 ms > > > > random_page_cost set to 1.5 > > ---------------------------------------------- > > QUERY PLAN > > Sort (cost=3D12702.62..12711.29 rows=3D3467 width=3D48) (actual > > time=3D1407.000..1407.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid, c.targetid > > -> Merge Join (cost=3D9912.07..12498.77 rows=3D3467 width=3D48) (actual > > time=3D1391.000..1407.000 rows=3D3467 loops=3D1) > > Merge Cond: ("outer".user_id =3D "inner".sourceid) > > -> Index Scan using lte_user_pkey on lte_user s > > (cost=3D0.00..12807.34 rows=3D279395 width=3D16) (actual time=3D0.000..= 46.000 > > rows=3D50034 loops=3D1) > > -> Sort (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual > > time=3D1188.000..1188.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid > > -> Hash Join (cost=3D8710.44..9708.21 rows=3D3467 width=3D40) > > (actual time=3D1157.000..1188.000 rows=3D3467 loops=3D1) > > Hash Cond: ("outer".targetid =3D "inner".user_id) > > -> Seq Scan on candidates617004 c > > (cost=3D0.00..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..15.00= 0 > > rows=3D3467 loops=3D1) > > -> Hash (cost=3D8011.95..8011.95 rows=3D279395 > > width=3D16) (actual time=3D1157.000..1157.000 rows=3D0 loops=3D1) > > -> Seq Scan on lte_user t > > (cost=3D0.00..8011.95 rows=3D279395 width=3D16) (actual time=3D0.000..7= 50.000 > > rows=3D279395 loops=3D1) > > Total runtime: 1422.000 ms > > > > random_page_cost set to 1.5 and enable_hashjoin set to false > >=20 > -------------------------------------------------------------------------= ------------------------- > > QUERY PLAN > > Sort (cost=3D13565.58..13574.25 rows=3D3467 width=3D48) (actual > > time=3D390.000..390.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid, c.targetid > > -> Nested Loop (cost=3D271.52..13361.73 rows=3D3467 width=3D48) (actual > > time=3D203.000..360.000 rows=3D3467 loops=3D1) > > -> Merge Join (cost=3D271.52..2762.88 rows=3D3467 width=3D40) > > (actual time=3D203.000..250.000 rows=3D3467 loops=3D1) > > Merge Cond: ("outer".user_id =3D "inner".sourceid) > > -> Index Scan using lte_user_pkey on lte_user s > > (cost=3D0.00..12807.34 rows=3D279395 width=3D16) (actual time=3D0.000..= 48.000 > > rows=3D50034 loops=3D1) > > -> Sort (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actual > > time=3D15.000..31.000 rows=3D3467 loops=3D1) > > Sort Key: c.sourceid > > -> Seq Scan on candidates617004 c > > (cost=3D0.00..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..15.00= 0 > > rows=3D3467 loops=3D1) > > -> Index Scan using lte_user_pkey on lte_user t > > (cost=3D0.00..3.02 rows=3D1 width=3D16) (actual time=3D0.023..0.023 row= s=3D1 > > loops=3D3467) > > Index Cond: ("outer".targetid =3D t.user_id) > > Total runtime: 406.000 ms > > > > Thanks, > > Meetesh >=20 >=20 >=20 > ------=_Part_3573_25248839.1122940573051 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks John.  I've answered your questions below:

Has lte_user and candidates617004 been recently ANALYZEd? All estimates,except for the expected number of rows from lte_user seem to be okay.

I ANALYZEd both tables just before putting together my first email.  And, unfortunately, modifying the statistics target didn't help either.

Is user= _id the primary key for lte_user?

Yes

I'm try= ing to figure out how you can get 50k rows, by searching a
primary key, = against a=20 3.5k rows. Is user_id only part of the primary
key for lte_user?

Hmmm ... I missed that before.  But, that surprises me too.  Especially since sourceId in the candidates table has only 1 value.  Also, user_id is the complete primary key for lte_user.

Can you= give us the output of:
\d lte_user
\d candidates617004

Sure, here they are:

lte=3D# \d lte_user
            &nb= sp;    Table "public.lte_user"
    Column     |            Type            = ; | Modifiers
---------------+-----------------------------+-----------
 user_id       | bigint           &nb= sp;          | not null
 firstname     | character varying(255)  = ;    |
 lastname      | character varying(255) =      |
 address1      | character varying(255) =      |
 address2      | character varying(255) =      |
 city          | characte= r varying(255)      |
 state         | character var= ying(255)      |
 zip           | cha= racter varying(255)      |
 phone1        | character varying(= 255)      |
 phone2        | character varying(= 255)      |
 username      | character varying(255) =      |
 password      | character varying(255) =      |
 deleted       | boolean           &n= bsp;         | not null
 ext_cust_id   | character varying(255)   &nb= sp;  |
 aboutme       | character varying(255)&= nbsp;     |
 birthday      | timestamp without time zone = |
 fm_id       | bigint           &nb= sp;          |
 ar            | double precision            |
Indexes:
    "lte_user_pkey" PRIMARY KEY, btree (user_id)     "idx_user_extid" btree (ext_cust_id)
    "idx_user_username" btree (username)
Foreign-key constraints:
    "fk_user_fm" FOREIGN KEY (fm_id) REFERENCES fm= (fm_id)

lte=3D# \d candidates617004
       Table "public.candidates617004&qu= ot;
    Column    |       Type       | Modifiers
--------------+------------------+-----------
 fmid       | bigint   &n= bsp;       |
 sourceid     | bigint    &nbs= p;      |
 sr            | double precision |=
 targetid     | bigint    &nbs= p;      |
 tr           | double precision |

Also, i= f you could describe the table layouts, that would help.

Sure.  The lte_user table is just a collection of users.  user_id is assigned uniquely using a sequence.  During some processing, we create a candidates table (candidates617004 in our case).  This table is usually a temp table.  sourceid is a user_id (in this case it is always 617004) and targetid is also a user_id (2860 distinct values out of 3467).  The rest of the information is either only used in the select clause or not used at all during this processing.

Did I miss something in the table layout description that would be helpful?=

Thanks for your help!
Meetesh

> &nb= sp;       ->  Sort  (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual
> time=3D1156.000..1156.000 rows=3D3467 loops=3D1= )
>           =     Sort Key: c.sourceid
>    &nbs= p;          ->  Hash Join  (cost=3D8710.44..9708.21 rows=3D3467 width=3D40)
> (actual time=3D1125.000..1156.000 rows=3D3467 loops=3D1= )
>           =           Hash Cond: ("outer".targetid =3D "inner".user_id)
&g= t;            &= nbsp;        ->  Seq Scan on candidates617004 c
> (cost=3D0.00..67.67= rows=3D3467 width=3D32) (actual time=3D0.000..0.000
> rows=3D3467 lo= ops=3D1)
>          = ;           ->  Hash  (cost=3D8011.95..8011.95 rows=3D279395
= > width=3D16) (actual time=3D1125.000..1125.000 rows=3D0 loops=3D1)
&= gt;            =             &nb= sp;  ->  Seq Scan on lte_user t
> (cost=3D0.00..8011.95 rows= =3D279395 width=3D16) (actual time=3D0.000..670.000
> rows=3D279395 l= oops=3D1)
> Total runtime: 1406.000 ms
>
> enable_hashjoi= n disabled
> ----------------------------------------
> QUERY PLAN
> Sort  (cost=3D14355.37..14364.03 rows= =3D3467 width=3D48) (actual
> time=3D391.000..391.000 rows=3D3467 loo= ps=3D1)
>   Sort Key: c.sourceid, c.targetid
> &= nbsp; ->  Nested Loop  (cost=3D271.52..14151.51 rows=3D3467 width=3D48) (actual
> time=3D203.000..359.000 rows=3D346= 7 loops=3D1)
>         ->  Merge Join  (cost=3D271.52..3490.83 rows=3D3467 width=3D40)
> (actual time=3D203.000..218.000 rows=3D3467 loops=3D1)<= br>>           &n= bsp;   Merge Cond: ("outer".user_id =3D "inner".sourceid)
&= gt;            =    ->  Index Scan using lte_user_pkey on lte_user s
> (cost= =3D0.00..16837.71 rows=3D279395 width=3D16) (actual time=3D0.000..126.000> rows=3D50034 loops=3D1)
>      &= nbsp;        ->  Sort  (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actual
> time=3D15.000..30.000 rows=3D3467 loops=3D1)>           &nbs= p;         Sort Key: c.sourceid
>        = ;             ->  Seq Scan on candidates617004 c
> (cost=3D0.00..67.67= rows=3D3467 width=3D32) (actual time=3D0.000..0.000
> rows=3D3467 lo= ops=3D1)
>         -> = ; Index Scan using lte_user_pkey on lte_user t
> (cost=3D0.00..3= .03 rows=3D1 width=3D16) (actual time=3D0.031..0.036 rows=3D1
> loops=3D= 3467)
>          &n= bsp;    Index Cond: ("outer".targetid =3D t.user_id)
> Total runtim= e: 406.000 ms
>
> random_page_cost set to 1.5
> ---------= -------------------------------------
> QUERY PLAN
> Sort =  (cost=3D 12702.62..12711.29 rows=3D3467 width=3D48) (actual
> time=3D1407.000.= .1407.000 rows=3D3467 loops=3D1)
>   Sort Key: c.sourceid, = c.targetid
>   ->  Merge Join  (cost= =3D9912.07..12498.77 rows=3D3467 width=3D48) (actual
> time=3D1391.000..1407.000 rows=3D3467 loops=3D1)
> &nbs= p;       Merge Cond: ("outer".user_= id =3D "inner".sourceid)
>     &nb= sp;   ->  Index Scan using lte_user_pkey on lte_user= s
> (cost=3D0.00..12807.34 rows=3D279395 width=3D16) (actual time=3D0.000..46.000
> rows=3D5003= 4 loops=3D1)
>         ->  Sort  (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual
> time=3D1188.000..1188.000 rows=3D3467 loops=3D1= )
>           =     Sort Key: c.sourceid
>    &nbs= p;          ->  Hash Join  (cost=3D8710.44..9708.21 rows=3D3467 width=3D40)
> (actual time=3D1157.000..1188.000 rows=3D3467 loops=3D1= )
>           =           Hash Cond: ("outer".targetid =3D "inner".user_id)
&g= t;            &= nbsp;        ->  Seq Scan on candidates617004 c
> (cost=3D0.00..67.67= rows=3D3467 width=3D32) (actual time=3D0.000..15.000
> rows=3D3467 l= oops=3D1)
>         &nbs= p;           ->  Hash  (cost=3D8011.95..8011.95 rows=3D279395
= > width=3D16) (actual time=3D1157.000..1157.000 rows=3D0 loops=3D1)
&= gt;            =             &nb= sp;  ->  Seq Scan on lte_user t
> (cost=3D0.00..8011.95 rows= =3D279395 width=3D16) (actual time=3D0.000..750.000
> rows=3D279395 l= oops=3D1)
> Total runtime: 1422.000 ms
>
> random_page_co= st set to 1.5 and enable_hashjoin set to false
> ------------------------------------------------------------------= --------------------------------
> QUERY PLAN
> Sort  = ;(cost=3D13565.58..13574.25 rows=3D3467 width=3D48) (actual
> time=3D= 390.000..390.000 rows=3D3467 loops=3D1)
>   Sort Key: c.sourceid, c.targeti= d
>   ->  Nested Loop  (cost=3D271.5= 2..13361.73 rows=3D3467 width=3D48) (actual
> time=3D203.000..360.000= rows=3D3467 loops=3D1)
>       &n= bsp; ->  Merge Join  (cost=3D271.52..2762.88 rows=3D3467 width=3D40)
> (actual time=3D203.000..250.000 rows=3D3467 loops=3D1)<= br>>           &n= bsp;   Merge Cond: ("outer".user_id =3D "inner".sourceid)
&= gt;            =    ->  Index Scan using lte_user_pkey on lte_user s
> (cost= =3D0.00..12807.34 rows=3D279395 width=3D16) (actual time=3D0.000..48.000> rows=3D50034 loops=3D1)
>      &n= bsp;        ->  Sort  (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actual
> time=3D15.000..31.000 rows=3D3467 loops=3D1)>           &nbs= p;         Sort Key: c.sourceid
>        = ;             ->  Seq Scan on candidates617004 c
> (cost=3D0.00..67.67= rows=3D3467 width=3D32) (actual time=3D0.000..15.000
> rows=3D3467 l= oops=3D1)
>         ->&nbs= p; Index Scan using lte_user_pkey on lte_user t
> (cost=3D 0.00..3.02 rows=3D1 width=3D16) (actual time=3D0.023..0.023 rows=3D1
>= ; loops=3D3467)
>        &nbs= p;      Index Cond: ("outer".targetid =3D t.user_id)
> Total runtim= e: 406.000 ms
>
> Thanks,
> Meetesh




------=_Part_3573_25248839.1122940573051--