Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Urdbl-0007q8-LO for pgsql-performance@arkaria.postgresql.org; Wed, 26 Jun 2013 00:29:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Urdbk-0002Dj-SL for pgsql-performance@arkaria.postgresql.org; Wed, 26 Jun 2013 00:29:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Urdbj-0002Dc-Ml for pgsql-performance@postgresql.org; Wed, 26 Jun 2013 00:29:11 +0000 Received: from mail-pb0-x22d.google.com ([2607:f8b0:400e:c01::22d]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Urdbf-0007bI-Kh for pgsql-performance@postgresql.org; Wed, 26 Jun 2013 00:29:10 +0000 Received: by mail-pb0-f45.google.com with SMTP id mc8so13423584pbc.4 for ; Tue, 25 Jun 2013 17:29:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; bh=3iDUiNQoj2yZSMVAr/U9kKJXPngVbKWmXUj5xOI+RpI=; b=xFsakzBGMi+/9YDVfohbqd+K8tn4bA/ZHPjvgYshYAZkcWAZJz26qCcIrJEVduXbsl uoiiCWLuqad0EwPZJc4bIXvSM8F6AWNqWWtLjZTIIJ27S4yWdqmh3JzZ7X95atC/PuIK +qminliH/k2yGFzKAep2n8CVa91plAhxdEggfXW4onyoJNsc0bob4dXHiKptS6Lbdrpq ES357wQg3zo648At19wSYwV2kPq+j1e2vUUgGyobsZT2kRJbpQoVPmLoWyz3NIK+31MB uqi6YTcYD7eqDQgWU3+fy2CbL10+VwwloMmP3J5w74A/vP8Zxepj7W+EAJeqlxK6+fHt MHiw== X-Received: by 10.66.118.104 with SMTP id kl8mr2098483pab.182.1372206545478; Tue, 25 Jun 2013 17:29:05 -0700 (PDT) Received: from [192.168.1.106] (70-36-157-176.static.sonic.net. [70.36.157.176]) by mx.google.com with ESMTPSA id jv10sm9824470pbc.28.2013.06.25.17.29.02 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 25 Jun 2013 17:29:03 -0700 (PDT) Subject: Re: incorrect row estimates for primary key join Mime-Version: 1.0 (Apple Message framework v1283) Content-Type: text/plain; charset=us-ascii From: Ben In-Reply-To: <19479.1372203377@sss.pgh.pa.us> Date: Tue, 25 Jun 2013 17:29:01 -0700 Cc: Kevin Grittner , bricklen , "pgsql-performance@postgresql.org list" Content-Transfer-Encoding: quoted-printable Message-Id: References: <7D0DD8B1-D23C-4BBC-9AB2-20D81F0B4BB8@gmail.com> <9A75BF4B-0458-43E3-803B-E25BB1984A39@gmail.com> <1372166429.65996.YahooMailNeo@web162901.mail.bf1.yahoo.com> <1372198395.32951.YahooMailNeo@web162904.mail.bf1.yahoo.com> <19479.1372203377@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.1283) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Jun 25, 2013, at 4:36 PM, Tom Lane wrote: >> That seems intuitive, but some of the estimates need to be made >> before all such information is available. Maybe we can do >> something about that some day.... >> Maybe someone else will jump in here with more details than I can >> provide (at least without hours digging in the source code). >=20 > It does not attempt to match up query WHERE clauses with indexes during > selectivity estimation, so the existence of a multi-column unique > constraint wouldn't help it improve the estimate. thanks tom, that answered my question. > In the case at hand, I doubt that a better result rowcount estimate > would have changed the planner's opinion of how to do the join. The OP > seems to be imagining that 2 million index probes into a large table > would be cheap, but that's hardly true. It's quite likely that the > mergejoin actually is the best way to do the query. If it isn't really > best on his hardware, I would think that indicates a need for some > tuning of the cost parameters. Another thing that might be helpful for > working with such large tables is increasing work_mem, to make hashes > and sorts run faster. i apologize if i seemed like i was presuming to know what the best query pl= an is. i fully understand that the query planner sometimes makes unintuiti= ve decisions which turn out to be for the best, having experienced it first= hand many times. since i've nudged my company to use postgresql (instead = of mysql/sqlite), we've been very happy with it. also, having tried my han= d (and failing) at making good gist selectivity estimators, i think i've go= t a not-completely-ignorant 10,000 ft view of the trade-offs it tries to ma= ke, when sequential scans are better than repeated index lookups, et cetera= . i'm writing because i found this example, which shows yet another thing = i don't understand about the query planner, and i am trying to learn better= about it. you've already answered my main question (whether or not unique constraints= are used to help row estimation.) there's a couple more issues which i do= n't quite understand : 1) when i give a hint to the query planner to not expect more than number-o= f-rows-in-jointable (via a limit), switches to a nested loop + index scan, = but with the same row estimates. i'll show the plan i had in the first ema= il : Limit (cost=3D0.00..178452647.11 rows=3D2500000 width=3D28) -> Nested Loop (cost=3D0.00..1306127545.35 rows=3D18297957 width=3D28) -> Seq Scan on jointable (cost=3D0.00..35867.11 rows=3D2066911 wi= dth=3D24) -> Index Scan using bigtable_pkey on bigtable (cost=3D0.00..631.8= 8 rows=3D1 width=3D28) Index Cond: ((id1 =3D jointable.id1) AND (id2 =3D jointable.i= d2) AND (id3 =3D jointable.id3) AND (id4 =3D jointable.id4) AND (id5 =3D jo= intable.id5)) (5 rows) before, i was misreading this as saying the planner was going to execute th= e nested loop fully (rows=3D18 million), and then limit the results. i am = now reading it as saying that the inner nested loop will be short-circuited= after it generates enough rows. if this is true, it seems to imply that, = in query plan with deeply nested inner nested loops, one should read the in= ner loop row estimates with a grain of salt, as there might be limits (pote= ntially many levels outwards) which can short-circuit them. am i wrong abo= ut this? 2) when doing the sort+merge join, it choses to sort bigtable rather than u= se an index scan. i've tried to give hints by requesting the results come = in primary key order, but it keeps sorting by a permutation of the primary = key and then resorting the join results at the end. so obviously the rando= m seek cost dominates the sequential read + sort (which i find surprising, = but again i am happy to be surprised.) that seems fine for a query which i= s going to touch the whole table. but i can't seem to come up with a query= which would ever favor using an index scan. for example this : explain select * from bigtable order by (id1, id2, id3, id4, id5) limit 1; QUERY PLAN=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ----------------- Limit (cost=3D91923132.04..91923132.04 rows=3D1 width=3D28) -> Sort (cost=3D91923132.04..99842047.88 rows=3D3167566336 width=3D28) Sort Key: (ROW(id1, id2, id3, id4, id5)) -> Seq Scan on bigtable (cost=3D0.00..76085300.36 rows=3D3167566= 336 width=3D28) (4 rows) (apologies bigtable has grown since i've first started this thread.) shoul= dn't an index scan definitely be fastest here? you don't need to touch the= whole table or index. maybe there something i have misconfigured here? best regards, ben --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance