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 A24ED52E45 for ; Mon, 1 Aug 2005 19:19:31 -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 85345-06 for ; Mon, 1 Aug 2005 22:19:26 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 1246952931 for ; Mon, 1 Aug 2005 19:19:25 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i4so1100741wra for ; Mon, 01 Aug 2005 15:19:28 -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:mime-version:content-type; b=S1ulFd5kkedaClcKR43dRSSo6OxcSgQUPkE3lhBTmCCkvYm9UBUcQSYLW2NuGwvveXY3W9EPNabq1FS0/oniPjGkLUyjwoZ1MXi8wJzd+aZ437sznaga3sxqsLrtNad8cw22RDtk31ffq9JojMptw5B5d0a3Z3yJP8MeMczFpbg= Received: by 10.54.8.60 with SMTP id 60mr3266886wrh; Mon, 01 Aug 2005 15:19:28 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Mon, 1 Aug 2005 15:19:27 -0700 (PDT) Message-ID: Date: Tue, 2 Aug 2005 00:19:27 +0200 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu To: pgsql-performance@postgresql.org Subject: Planner incorrectly choosing seq scan over index scan Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3517_6467213.1122934767947" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.231 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/12 X-Sequence-Number: 13759 ------=_Part_3517_6467213.1122934767947 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all, We're using 8.0.3 and we're seeing a problem where the planner is choosing = a=20 seq scan and hash join over an index scan. If I set enable_hashjoin to off,= =20 then I get the plan I'm expecting and the query runs a lot faster. I've als= o=20 tried lowering the random page cost (even to 1) but the planner still=20 chooses to use the hash join. Does anyone have any thoughts/suggestions? I saw that there was a thread=20 recently in which the planner wasn't correctly estimating the cost for=20 queries using LIMIT. Is it possible that something similar is happening her= e=20 (perhaps because of the sort) and that the patch Tom proposed would fix it? Thanks. Here are the various queries and plans: Normal settings ------------------------ explain analyze select c.sourceId, c.targetId, abs(c.tr - c.sr ) as xmy, (c.sr - s.ar ) * (c.tr - t.ar) as xy, (c.sr - s.ar ) * (c.sr - s.ar) as x2, (c.tr - t.ar ) * (c.tr - t.ar) as y2 from candidates617004 c, lte_user s, lte_user t where c.sourceId =3D s.user_id and c.targetId =3D t.user_id order by c.sourceId, c.targetId; QUERY PLAN Sort (cost=3D13430.57..13439.24 rows=3D3467 width=3D48) (actual time=3D 1390.000..1390.000 rows=3D3467 loops=3D1) Sort Key: c.sourceid, c.targetid -> Merge Join (cost=3D9912.07..13226.72 rows=3D3467 width=3D48) (actual tim= e=3D 1344.000..1375.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.71rows=3D279395 width=3D16) (actual time=3D 0.000..95.000 rows=3D50034 loops=3D1) -> Sort (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual time=3D 1156.000..1156.000 rows=3D3467 loops=3D1) Sort Key: c.sourceid -> Hash Join (cost=3D8710.44..9708.21 rows=3D3467 width=3D40) (actual time= =3D 1125.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=3D3= 2)=20 (actual time=3D0.000..0.000 rows=3D3467 loops=3D1) -> Hash (cost=3D8011.95..8011.95 rows=3D279395 width=3D16) (actual time=3D 1125.000..1125.000 rows=3D0 loops=3D1) -> Seq Scan on lte_user t (cost=3D0.00..8011.95 rows=3D279395 width=3D16) (= actual=20 time=3D0.000..670.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=3D 391.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 tim= e=3D 203.000..359.000 rows=3D3467 loops=3D1) -> Merge Join (cost=3D271.52..3490.83 rows=3D3467 width=3D40) (actual time= =3D 203.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.71rows=3D279395 width=3D16) (actual time=3D 0.000..126.000 rows=3D50034 loops=3D1) -> Sort (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actual time=3D15.000..30.000rows=3D3467 loops=3D1) Sort Key: c.sourceid -> Seq Scan on candidates617004 c (cost=3D0.00..67.67 rows=3D3467 width=3D3= 2)=20 (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= =20 width=3D16) (actual time=3D0.031..0.036 rows=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=3D 1407.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 tim= e=3D 1391.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.34rows=3D279395 width=3D16) (actual time=3D 0.000..46.000 rows=3D50034 loops=3D1) -> Sort (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual time=3D 1188.000..1188.000 rows=3D3467 loops=3D1) Sort Key: c.sourceid -> Hash Join (cost=3D8710.44..9708.21 rows=3D3467 width=3D40) (actual time= =3D 1157.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=3D3= 2)=20 (actual time=3D0.000..15.000 rows=3D3467 loops=3D1) -> Hash (cost=3D8011.95..8011.95 rows=3D279395 width=3D16) (actual time=3D 1157.000..1157.000 rows=3D0 loops=3D1) -> Seq Scan on lte_user t (cost=3D0.00..8011.95 rows=3D279395 width=3D16) (= actual=20 time=3D0.000..750.000 rows=3D279395 loops=3D1) Total runtime: 1422.000 ms random_page_cost 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.targetid -> Nested Loop (cost=3D271.52..13361.73 rows=3D3467 width=3D48) (actual tim= e=3D 203.000..360.000 rows=3D3467 loops=3D1) -> Merge Join (cost=3D271.52..2762.88 rows=3D3467 width=3D40) (actual time= =3D 203.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.34rows=3D279395 width=3D16) (actual time=3D 0.000..48.000 rows=3D50034 loops=3D1) -> Sort (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actual time=3D15.000..31.000rows=3D3467 loops=3D1) Sort Key: c.sourceid -> Seq Scan on candidates617004 c (cost=3D0.00..67.67 rows=3D3467 width=3D3= 2)=20 (actual time=3D0.000..15.000 rows=3D3467 loops=3D1) -> Index Scan using lte_user_pkey on lte_user t (cost=3D0.00..3.02 rows=3D1= =20 width=3D16) (actual time=3D0.023..0.023 rows=3D1 loops=3D3467) Index Cond: ("outer".targetid =3D t.user_id) Total runtime: 406.000 ms Thanks, Meetesh ------=_Part_3517_6467213.1122934767947 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all,

We're using 8.0.3 and we're seeing a problem where the planner is choosing a seq scan and hash join over an index scan.  If I set enable_hashjoin to off, then I get the plan I'm expecting and the query runs a lot faster.  I've also tried lowering the random page cost (even to 1) but the planner still chooses to use the hash join.

Does anyone have any thoughts/suggestions?  I saw that there was a thread recently in which the planner wasn't correctly estimating the cost for queries using LIMIT.  Is it possible that something similar is happening here (perhaps because of the sort) and that the patch Tom proposed would fix it?

Thanks.  Here are the various queries and plans:

Normal settings
------------------------
explain analyze
    select
        c.sourceId,
        c.targetId,
        abs(c.tr= - c.sr) as xmy,
        (c.sr - s.ar) * (c.tr -= t.ar) as xy,
        (c.sr - s.ar) * (c.sr -= s.ar) as x2,
        (c.tr - t.ar) * (c.tr -= t.ar) as y2
    from
        candidates617004 c,
        lte_user s,
        lte_user t
    where
        c.sourceId =3D s.user_id
        and c.targetId =3D t.user_id
    order by
        c.sourceId,
        c.targetId;

QUERY PLAN
Sort  (cost=3D13430.57..13439.24 rows=3D3467 width=3D48) (actual time= =3D1390.000..1390.000 rows=3D3467 loops=3D1)
  Sort Key: c.sourceid, c.targetid
  ->  Merge Join  (cost=3D9912.07..13226.72 rows=3D3467 width=3D48) (actual time=3D1344.000..1375.000 rows=3D3467 loops=3D1)
        Merge Cond: ("outer".u= ser_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..95.000 rows=3D50034 loops=3D1)
        ->  Sort  (cost=3D9912.07..9920.73 rows=3D3467 width=3D40) (actual time=3D1156.000..1156.000 rows=3D3467 loops=3D1)
            &nb= sp; Sort Key: c.sourceid
            &nb= sp; ->  Hash Join  (cost=3D8710.44..9708.21 rows=3D3467 width=3D40= ) (actual time=3D1125.000..1156.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..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..0.000 rows=3D3467 loops=3D1)<= br>             &nb= sp;       ->  Hash  (cost=3D8011.95..8011.95 rows=3D279395 width=3D16) (actual time=3D1125.000..1125.000 rows=3D0 loops=3D1)
            &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 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 w= idth=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)
            &nb= sp; Merge Cond: ("outer".user_id =3D "inner".sourceid)<= br>             &nb= sp; ->  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)
            &nb= sp; ->  Sort  (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actu= al time=3D15.000..30.000 rows=3D3467 loops=3D1)
            &nb= sp;       Sort Key: c.sourceid
            &nb= sp;       ->  Seq Scan on candidates617004 c  (cost=3D0.00..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..0.000 rows=3D3467 loops=3D1)<= br>         ->  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=3D3467)
            &nb= sp; 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".u= ser_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)
            &nb= sp; Sort Key: c.sourceid
            &nb= sp; ->  Hash Join  (cost=3D8710.44..9708.21 rows=3D3467 width=3D40= ) (actual time=3D1157.000..1188.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..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..15.000 rows=3D3467 loops=3D1)=
            &nb= sp;       ->  Hash  (cost=3D8011.95..8011.95 rows=3D279395 width=3D16) (actual time=3D1157.000..1157.000 rows=3D0 loops=3D1)
            &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 loops= =3D1)
Total runtime: 1422.000 ms

random_page_cost set to 1.5 and enable_hashjoin set to false
---------------------------------------------------------------------------= -----------------------
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 w= idth=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)
            &nb= sp; Merge Cond: ("outer".user_id =3D "inner".sourceid)<= br>             &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..48.0= 00 rows=3D50034 loops=3D1)
            &nb= sp; ->  Sort  (cost=3D271.52..280.19 rows=3D3467 width=3D32) (actu= al time=3D15.000..31.000 rows=3D3467 loops=3D1)
            &nb= sp;       Sort Key: c.sourceid
            &nb= sp;       ->  Seq Scan on candidates617004 c  (cost=3D0.00..67.67 rows=3D3467 width=3D32) (actual time=3D0.000..15.000 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 rows=3D1 loops=3D3467)
            &nb= sp; Index Cond: ("outer".targetid =3D t.user_id)
Total runtime: 406.000 ms

Thanks,
Meetesh
------=_Part_3517_6467213.1122934767947--