X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 26D9B9F9B01 for ; Thu, 25 May 2006 13:22:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 91664-01 for ; Thu, 25 May 2006 13:21:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.238]) by postgresql.org (Postfix) with ESMTP id 7965E9F94EA for ; Thu, 25 May 2006 13:21:55 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 67so164163wri for ; Thu, 25 May 2006 09:21:54 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=TuDp8v+vPQqA23WhtiB+07E0gHTSjb0t/KDMTOQlVVlyCktwTbDcCWQ5GPHMgRHoBk0qUQ8oXLUFupqPLEUGs2zRQBaH2kWRSJYuIs/Ii4KIAgMPyYU+C6RJyTJRcibJnz6LOwbiexIhhL5dE8+u0l5NqogIwftzdMruefxO0U0= Received: by 10.54.143.10 with SMTP id q10mr814350wrd; Thu, 25 May 2006 09:21:53 -0700 (PDT) Received: by 10.54.101.2 with HTTP; Thu, 25 May 2006 09:21:53 -0700 (PDT) Message-ID: Date: Thu, 25 May 2006 12:21:53 -0400 From: "Kynn Jones" To: pgsql-performance@postgresql.org Subject: Re: Optimizing a huge_table/tiny_table join Cc: "Tom Lane" In-Reply-To: <7082.1148521319@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19611_32327247.1148574113949" References: <200605250052.k4P0qrT11965@panix3.panix.com> <7082.1148521319@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/542 X-Sequence-Number: 19329 ------=_Part_19611_32327247.1148574113949 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 5/24/06, Tom Lane wrote: > > writes: > > Limit (cost=3D19676.75..21327.99 rows=3D6000 width=3D84) > > -> Hash Join (cost=3D19676.75..1062244.81 rows=3D3788315 width=3D8= 4) > > Hash Cond: (upper(("outer".id)::text) =3D > upper(("inner".id)::text)) > > -> Seq Scan on huge_table h (cost=3D0.00..51292.43 rows=3D25= 25543 > width=3D46) > > -> Hash (cost=3D19676.00..19676.00 rows=3D300 width=3D38) > > -> Seq Scan on tiny_table t (cost=3D0.00..19676.00rows= =3D300 width=3D38) > > Um, if huge_table is so much bigger than tiny_table, why are the cost > estimates for seqscanning them only about 2.5x different? There's > something wacko about your statistics, methinks. You mean there's a bug in explain? I agree that it makes no sense that the costs don't differ as much as one would expect, but you can see right there the numbers of rows for the two tables, which are exactly as I described. At any rate, how would one go about finding an explanation for these strang= e stats? More bewildering still (and infuriating as hell--because it means that all of my work for yesterday has been wasted) is that I can no longer reproduce the best query plan, even though the tables have not changed at all. (Henc= e I can't post the explain analyze for the best query plan.) No matter what value I use for LIMIT, the query planner now insists on sequentially scanning huge_table and ignoring the available index. (If I turn off enable_seqscan, I get the second worst query plan I posted yesterday.) Anyway, I take it that there is no way to bypass the optimizer and instruct PostgreSQL exactly how one wants the search performed? Thanks! kj ------=_Part_19611_32327247.1148574113949 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 5/24/06, = Tom Lane <tgl@sss.pgh.pa.us= > wrote:=20
<kynn@panix.com> writes:
>  Limit  (c= ost=3D19676.75..21327.99 rows=3D6000 width=3D84)
>    ->  Ha= sh Join  (cost=3D19676.75..1062244.81 rows=3D3788315 width=3D84)<= br>>          Hash Con= d: (upper(("outer".id)::text) =3D upper(("inner".id)::t= ext))
>          -&= gt;  Seq Scan on huge_table h  (cost=3D 0.00..51292.43 rows=3D2525543 width=3D46)
>    &n= bsp;     ->  Hash  (cost=3D= 19676.00..19676.00 rows=3D300 width=3D38)
>    &n= bsp;           ->=   Seq Scan on tiny_table t  (cost=3D0.00..19676.00 rows= =3D300 width=3D38)

Um, if huge_table is so much bigger than tiny_tab= le, why are the cost
estimates for seqscanning them only about 2.5x different?  Th= ere's
something wacko about your statistics, methinks.
 
 
You mean there's a bug in explain?  I agree that it makes no sens= e that the costs don't differ as much as one would expect, but you can see = right there the numbers of rows for the two tables, which are exactly as I = described.  At any rate, how would one go about finding an explan= ation for these strange stats?
 
More bewildering still (and infuriating as hell--because it means that=  all of my work for yesterday has been wasted) is that I can no l= onger reproduce the best query plan, even though the tables have not c= hanged at all.  (Hence I can't post the explain analyze for the b= est query plan.)  No matter what value I use for LIMIT, the query plan= ner now insists on sequentially scanning huge_table and ignoring the a= vailable index.  (If I turn off enable_seqscan, I get the second worst= query plan I posted yesterday.)
 
Anyway, I take it that there is no way to bypass the optimizer and ins= truct PostgreSQL exactly how one wants the search performed?
 
Thanks!
 
kj
 
------=_Part_19611_32327247.1148574113949--