Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E11761337B4E for ; Wed, 4 May 2011 11:57:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 25927-04 for ; Wed, 4 May 2011 14:56:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm21.bullet.mail.sp2.yahoo.com (nm21.bullet.mail.sp2.yahoo.com [98.139.91.91]) by mail.postgresql.org (Postfix) with SMTP id E4AE61337B47 for ; Wed, 4 May 2011 11:56:52 -0300 (ADT) Received: from [98.139.91.61] by nm21.bullet.mail.sp2.yahoo.com with NNFMP; 04 May 2011 14:56:53 -0000 Received: from [98.139.91.43] by tm1.bullet.mail.sp2.yahoo.com with NNFMP; 04 May 2011 14:56:53 -0000 Received: from [127.0.0.1] by omp1043.mail.sp2.yahoo.com with NNFMP; 04 May 2011 14:56:53 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 64706.37833.bm@omp1043.mail.sp2.yahoo.com Received: (qmail 66479 invoked by uid 60001); 4 May 2011 14:56:52 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1304521012; bh=OcBwT+ks1KgekI8+Iuv3Yw2FCiku3r81+4GjlSbtz6g=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=LDTtYIPVMR9H3mgMFlDWhYT/C98xeky87wt/ClJs34PYFaHjXnuPLGDsly2fMLO0nebX69eSgeJskDqhEQbz0jPd7iN613/z2gui6/Ovtdjkq88QKJmHahkH06YeY5gsK0QgPDa3a1vei2Ppd+gfmy7tFP0fpLwPrTPOz05nET4= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=K5ujCwYqrRUI6P1IoJhg/9Tl9cenpRCMJ6Y5iTIQAy5lswdgzt66Hbl/tFOpOCsyuC2bLsWvhpYwkSm7ipqTTzg2ULHdCm3/+UiZotRg1VP8pipPsazi4z1E9PKfGEXUGrBRCp8WzylbhBsSl9GJjjlg9z9YZs7J0hWrUdjHTIg=; Message-ID: <600933.65939.qm@web112414.mail.gq1.yahoo.com> X-YMail-OSG: gG8ROEgVM1kZHIoSJ8VzQUkcrC4e_SY4HE2.r48TkA0JM92 UQFpTHSXEr6JPr3QRRfilAlcW2E9WJAHRndok6fVoifan6JpdR_hk2uXo4u0 ayoFGEG73SGScVf3.R97Xn0Q7903Mi63pvWKL84LZ8Tc1.b_VDzL0tWk30UX gIDkPjm9Xw5Ypjq8jC1VrxJsu3qoqPzlzpomvVqUeM1_aMMmNqxXVeJrYai8 HefGImcl3b81a7zZluXWc8bIFfT9xTobQ1f0vJCCmM2k6.XLHm6btYlBngQ0 aaW9Wh2nQz0kEqqa0sv.4qyci_8Z3DZzPDyDtKmtXEJ0El_oS2gX48DIrWCP YT8OZKJK1lJNhUOGqF8Cutwx6qcatIU87errH5tJ0f4fSsVzsP2hq8yIBTMo pwNLRTBoiABW_hQ-- Received: from [92.251.93.59] by web112414.mail.gq1.yahoo.com via HTTP; Wed, 04 May 2011 07:56:52 PDT X-Mailer: YahooMailWebService/0.8.110.299900 References: <625605.72736.qm@web112406.mail.gq1.yahoo.com> <10497.1304518369@sss.pgh.pa.us> <186532.42226.qm@web112405.mail.gq1.yahoo.com> Date: Wed, 4 May 2011 07:56:52 -0700 (PDT) From: Denis de Bernardy Reply-To: Denis de Bernardy Subject: Re: row estimate very wrong for array type To: PG Performance In-Reply-To: <186532.42226.qm@web112405.mail.gq1.yahoo.com> MIME-Version: 1.0 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.908 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/52 X-Sequence-Number: 43561 > ----- Original Message -----=0A=0A>> From: Tom Lane = =0A>> To: Denis de Bernardy =0A>> Cc: "pgsql-perfo= rmance@postgresql.org" =0A> =0A>> Sent: = Wednesday, May 4, 2011 4:12 PM=0A>> Subject: Re: [PERFORM] row estimate ve= ry wrong for array type =0A>> =0A>> Array && uses areasel() which is only = a stub :-(=0A=0A=0AOn a separate note,=A0in case this ever gets found via g= oogle,=A0I managed to force the use of the correct index in the meanwhile:= =0A=0A# explain analyze select * from test where (0 =3D any(intarr1) or 1 = =3D any(intarr1)) and (2 =3D any(intarr2) or 4 =3D any(intarr2)) order by s= ortcol limit 10;=0A=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PL= AN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=0A----------------------= ---------------------------------------------------------------------------= ----------------------------------=0A=A0Limit =A0(cost=3D0.00..385.16 rows= =3D10 width=3D217) (actual time=3D0.107..0.151 rows=3D10 loops=3D1)=0A=A0= =A0 -> =A0Index Scan using test_sortcol_key on test =A0(cost=3D0.00..14019.= 98 rows=3D364 width=3D217) (actual time=3D0.106..0.146 rows=3D10 loops=3D1)= =0A=A0=A0 =A0 =A0 =A0 Filter: (((0 =3D ANY (intarr1)) OR (1 =3D ANY (intarr= 1))) AND ((2 =3D ANY (intarr2)) OR (4 =3D ANY (intarr2))))=0A=A0Total runti= me: 0.214 ms=0A=0A=0AI guess I'm in for maintaining counts and rewriting qu= eries as needed. :-(=0A=0AD