Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id ED4A71337B4E for ; Wed, 4 May 2011 11:43:03 -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 13677-08 for ; Wed, 4 May 2011 14:42:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm17-vm0.bullet.mail.sp2.yahoo.com (nm17-vm0.bullet.mail.sp2.yahoo.com [98.139.91.212]) by mail.postgresql.org (Postfix) with SMTP id B3EB51337B47 for ; Wed, 4 May 2011 11:42:54 -0300 (ADT) Received: from [98.139.91.66] by nm17.bullet.mail.sp2.yahoo.com with NNFMP; 04 May 2011 14:42:54 -0000 Received: from [98.139.91.33] by tm6.bullet.mail.sp2.yahoo.com with NNFMP; 04 May 2011 14:42:54 -0000 Received: from [127.0.0.1] by omp1033.mail.sp2.yahoo.com with NNFMP; 04 May 2011 14:42:54 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 577959.93522.bm@omp1033.mail.sp2.yahoo.com Received: (qmail 49066 invoked by uid 60001); 4 May 2011 14:42:54 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1304520174; bh=dZvI42sBjpaC80nP9WOX6E9uCgvc8+gi673ELM8n9/A=; 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=Gvbo/Mpb6pIxwRvbqJiUOvye0FrXS5cT4gFV+narX7uyBa2+bRzHpXjC+o/zY/LVxM0wCMFpfwcoMEUtO0u/zyY4PafZEKWcfztim5vK1YsNw4GHKgOifdhRXyxAQKrQZX2Yf6oAplFPGZs/rxBOA122vjbkwdqcN5KaSC4I6pc= 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=EwU8sisUfUajyVzv0Vy5gqkhzobvJwuehkrjgaqBS0EH5/5AhyETjqorEJWXsBFPnsDC3E6zoKhS186uJ0WdJsTNP2adMyVhT12XPQtMg1SnUBGIwdvZgZu2ys8DWfh3WLCKs1sGPe2R0aHG+/q99ium6npNshOGKEtJnr8jdE4=; Message-ID: <186532.42226.qm@web112405.mail.gq1.yahoo.com> X-YMail-OSG: bCC7SrsVM1ntneehoR2slkeRfUfz_iff3jeJrMDzQNdL06S AZGw- Received: from [92.251.93.59] by web112405.mail.gq1.yahoo.com via HTTP; Wed, 04 May 2011 07:42:53 PDT X-Mailer: YahooMailWebService/0.8.110.299900 References: <625605.72736.qm@web112406.mail.gq1.yahoo.com> <10497.1304518369@sss.pgh.pa.us> Date: Wed, 4 May 2011 07:42:53 -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: <10497.1304518369@sss.pgh.pa.us> 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/51 X-Sequence-Number: 43560 That kind of limits the usefulness of aggregating hierarchical dependencies= into array columns to avoid enormous join statements. :-|=0A=0A=0ARe your = todo item you mention in this thread:=0A=0Ahttp://archives.postgresql.org/p= gsql-hackers/2010-05/msg01864.php=0A=0AMy C is rusty, but I might have enou= gh understanding of the PG internals to massage pre-existing code... Feel f= ree to message me off list with pointers if you think I might be able to he= lp.=0A=0A=0A=0A----- Original Message -----=0A> From: Tom Lane =0A> To: Denis de Bernardy =0A> Cc: "pgsql-pe= rformance@postgresql.org" =0A> Sent: Wedn= esday, May 4, 2011 4:12 PM=0A> Subject: Re: [PERFORM] row estimate very wro= ng for array type =0A> =0A> Denis de Bernardy write= s:=0A>> [ estimates for array && suck ]=0A>> Might this be a bug in the o= perator's selectivity, or am I doing =0A> something wrong?=0A> =0A> Array &= & uses areasel() which is only a stub :-(=0A> =0A> In the particular case h= ere it'd be possible to get decent answers=0A> just by trying the operator = against all the MCV-list entries, but it's=0A> unlikely that that would fix= things for enough people to be worth the=0A> trouble.=A0 Really you'd need= to maintain statistics about the element=0A> values appearing in the array= column in order to get useful estimates=0A> for && queries.=A0 Jan Urbansk= i did something similar for tsvector columns=0A> a year or two ago, but nob= ody's gotten around to doing it for array=0A> columns.=0A> =0A> =A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 regards, tom lane=0A> =0A> -- =0A> Sent via pgsql-perfo= rmance mailing list (pgsql-performance@postgresql.org)=0A> To make changes = to your subscription:=0A> http://www.postgresql.org/mailpref/pgsql-performa= nce=0A>