Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0C6AC632A48 for ; Sat, 1 May 2010 09:01:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 42744-08 for ; Sat, 1 May 2010 12:00:53 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id BBC81632969 for ; Sat, 1 May 2010 09:00:53 -0300 (ADT) Received: by fxm10 with SMTP id 10so933497fxm.19 for ; Sat, 01 May 2010 05:00:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:received:in-reply-to :references:from:date:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=HOD04ooThsBPyrY5bg2Z2kwYtDL1rhX6KHH0UKoC5dQ=; b=ZmXEMUbQ6hRYQiQS6tXRJhvLtyJyokxfhqU5EUdvt2P2IISe5FpvNyhqiTv9QjpAIE RmAy1ehAm5ZtGRziKub5QI8OomEAQXsqxzVW+zrvDYiCI5u3II/doLjJ6G5fVac0/gcF 2nN3etXmotN0XX3ELniYiVpibXz1hTkviy6r0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=PSyIJlA/vUEDukfY7k7S6Y+lvk7zqzjirsr3VnAznh5vFbYKaTzRnLQ6e6dU3+p0+k AgI5IeZlr15lJgqdyltOuRQmFF7JuT08RHMkG89GdHapIEgDNN6D3WTDtdJjycs4fpYb G9yjQJBOZFV7cAYLTLG2dHhJJtDnAD97MDjLI= Received: by 10.239.183.74 with SMTP id t10mr1134833hbg.85.1272715252073; Sat, 01 May 2010 05:00:52 -0700 (PDT) MIME-Version: 1.0 Received: by 10.239.181.197 with HTTP; Sat, 1 May 2010 05:00:32 -0700 (PDT) In-Reply-To: References: <4BD0161A.4010908@dc.baikal.ru> <4BD0430C.1050308@2ndquadrant.com> <4BD10805.9060004@dc.baikal.ru> <4BD4FFE7.9090305@dc.baikal.ru> From: =?ISO-8859-1?Q?C=E9dric_Villemain?= Date: Sat, 1 May 2010 14:00:32 +0200 Message-ID: Subject: Re: Optimization idea To: Robert Haas Cc: Vlad Arkhipov , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.551 tagged_above=-10 required=5 tests=AWL=0.348, BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201005/4 X-Sequence-Number: 38588 2010/5/1 C=E9dric Villemain : > 2010/4/28 Robert Haas : >> On Mon, Apr 26, 2010 at 5:33 AM, C=E9dric Villemain >> wrote: >>> In the first query, the planner doesn't use the information of the 2,3,= 4. >>> It just does a : I'll bet I'll have 2 rows in t1 (I think it should >>> say 3, but it doesn't) >>> So it divide the estimated number of rows in the t2 table by 5 >>> (different values) and multiply by 2 (rows) : 40040. >> >> I think it's doing something more complicated. =A0See scalararraysel(). >> >>> In the second query the planner use a different behavior : it did >>> expand the value of t1.t to t2.t for each join relation and find a >>> costless plan. (than the one using seqscan on t2) >> >> I think the problem here is one we've discussed before: if the query >> planner knows that something is true of x (like, say, x =3D >> ANY('{2,3,4}')) and it also knows that x =3D y, it doesn't infer that >> the same thing holds of y (i.e. y =3D ANY('{2,3,4}') unless the thing >> that is known to be true of x is that x is equal to some constant. >> Tom doesn't think it would be worth the additional CPU time that it >> would take to make these sorts of deductions. =A0I'm not sure I believe >> that, but I haven't tried to write the code, either. > > Relative to this too : > http://archives.postgresql.org/pgsql-general/2010-05/msg00009.php =A0? not, sorry ,misread about prepared statement in the other thread ... > >> >> ...Robert >> > > > > -- > C=E9dric Villemain > --=20 C=E9dric Villemain