Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C62301337B82 for ; Wed, 4 May 2011 10:41:15 -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 12901-03 for ; Wed, 4 May 2011 13:40:52 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm14-vm0.bullet.mail.sp2.yahoo.com (nm14-vm0.bullet.mail.sp2.yahoo.com [98.139.91.246]) by mail.postgresql.org (Postfix) with SMTP id 6250A1337B81 for ; Wed, 4 May 2011 10:40:51 -0300 (ADT) Received: from [98.139.91.64] by nm14.bullet.mail.sp2.yahoo.com with NNFMP; 04 May 2011 13:40:51 -0000 Received: from [98.139.91.39] by tm4.bullet.mail.sp2.yahoo.com with NNFMP; 04 May 2011 13:40:51 -0000 Received: from [127.0.0.1] by omp1039.mail.sp2.yahoo.com with NNFMP; 04 May 2011 13:40:51 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 179602.94521.bm@omp1039.mail.sp2.yahoo.com Received: (qmail 88266 invoked by uid 60001); 4 May 2011 13:40:50 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1304516450; bh=U0YehVd3fv+/R0gaUg4R1ebouyR7VPR7eMs2ZH8iQ40=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=YCwuaKl3keREzeUD+s5+OtA2Hvzz/63nNA24WAsBo9yqlpFHAsXiPspk5wshCha/uaLy7OQPRSoMuqSqvBABSnUZtCCD7MAvTuKUYLzWWZPoJYAhsYNxZzsBQS4i/8XIVReKwobHcXODq3d50Fr6VZMO8/z+PM40mUT9kkiQUFs= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=J1MQ/lL06uQxfDegjp2cmHS3diwvTZnKn9hTPiwf0un5FQyIhY87ycjd7QXfMmB6Mfznc8y1ogAtXEB88TbTS/lJhzBfHXPfVUrNI925EvmUZFEnxYey1Zi3052wnKIbo8lyMK+kn1KJ9HpGOS0NAsO8EpLi62vtEtzy2fkgl8c=; Message-ID: <625605.72736.qm@web112406.mail.gq1.yahoo.com> X-YMail-OSG: CsMeO0kVM1lTmDixU8Pbq0pemlXO2RAlyPTI56Fu4avODux ntQy6b799nXcTSpktf_uhGQl6xFpbqsYQU3ksLdX4nJe9QjieGvD5w04Ss_Q 28x9bS6OBSKP54lbdeR3KhJO66zmRf27VGpHmATjcLRaHDO2U7AO942YFDI0 KWW1nmejuCh9SqPNGSKZ7YG6sBXpqXwDc0qX8onKZThd6J_uE4gcYKGi90ml bEIvDSx01OF7RTtO.Yr1HzWBiM9DfJw38Z.HvdHcJsRmZABV_lUZ.ctAnQVG YJ4_R8kJG6ylwFevtkuvROaXS_yOpOZbr5Wy8mdnsVj2Az_UGJelC4Sb_EJB 1ZIcinC_hRBuDscdRa8gJuomPESZXyGVLok0Ej5425VtjLTp80hNFzammLie Nbgzlk609gbuvRQ-- Received: from [88.203.16.8] by web112406.mail.gq1.yahoo.com via HTTP; Wed, 04 May 2011 06:40:50 PDT X-Mailer: YahooMailWebService/0.8.110.299900 Date: Wed, 4 May 2011 06:40:50 -0700 (PDT) From: Denis de Bernardy Reply-To: Denis de Bernardy Subject: row estimate very wrong for array type To: "pgsql-performance@postgresql.org" 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/48 X-Sequence-Number: 43557 Hi,=0A=0AI'm running into erroneous row estimates when using an array type,= and I'm running out of ideas on how to steer postgres into the right direc= tion... I've tried setting statistics to 1000, vacuuming and analyzing over= and over, rewriting the query differently... to no avail.=0A=0AThe table l= ooks like this:=0A=0Acreate table test (=0Aid serial primary key,=0Asortcol= float unique,=0Aintarr1 int[] not null default '{}',=0Aintarr2 int[] not n= ull default '{}'=0A=0A);=0A=0AIt=A0contains 40k rows of random data which, = for the sake of the queries that follow, aren't too far from what they'd co= ntain in production.=0A=0A=0A# select=A0intarr1,=A0intarr2,=A0count(*) from= test group by=A0intarr1, intarr2;=0A=0A=0A=A0intarr1=A0|=A0intarr2=A0| cou= nt=A0=0A--------------+-------------+-------=0A=A0{0} =A0 =A0 =A0 =A0 =A0| = {2,3} =A0 =A0 =A0 | 40018=0A=0A=0AThe stats seem right:=0A=0A# select * fro= m pg_stats where tablename =3D 'test' and attname =3D 'intarr1';=0A=0A=A0sc= hemaname | tablename | =A0 attname =A0 =A0| inherited | null_frac | avg_wid= th | n_distinct | most_common_vals | most_common_freqs | histogram_bounds |= correlation=A0=0A------------+-----------+--------------+-----------+-----= ------+-----------+------------+------------------+-------------------+----= --------------+-------------=0A=A0test =A0 =A0 =A0 | test =A0 =A0 =A0|=A0in= tarr1=A0| f =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 0 | =A0 =A0 =A0 =A025 | =A0 = =A0 =A0 =A0 =A01 | {"{0}"} =A0 =A0 =A0 =A0 =A0| {1} =A0 =A0 =A0 =A0 =A0 =A0= =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 1=0A=0A=0AA= query without any condition on the array results in reasonable estimates a= nd the proper use of the index on the sort column:=0A=0A# explain analyze s= elect * from test order by=A0sortcol=A0limit 10;=0A=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 =A0 QUERY PLAN =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..3.00 rows=3D10 width=3D217) (actual time=3D0.0= 98..0.109 rows=3D10 loops=3D1)=0A=A0=A0 -> =A0Index Scan using test_sortcol= _key on test =A0(cost=3D0.00..12019.08 rows=3D40018 width=3D217) (actual ti= me=3D0.096..0.105 rows=3D10 loops=3D1)=0A=A0Total runtime: 0.200 ms=0A=0A= =0AAfter adding a condition on the array, however, the row estimate is comp= letely off:=0A=0A=0A# explain analyze select * from test where=A0intarr1=A0= && '{0,1}' order by=A0sortcol=A0limit 10;=0A=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 PLAN =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(c= ost=3D0.00..605.96 rows=3D10 width=3D217) (actual time=3D0.131..0.175 rows= =3D10 loops=3D1)=0A=A0=A0 -> =A0Index Scan using test_sortcol_key on test = =A0(cost=3D0.00..12119.13 rows=3D200 width=3D217) (actual time=3D0.129..0.1= 69 rows=3D10 loops=3D1)=0A=A0=A0 =A0 =A0 =A0 Filter: (intarr1=A0&& '{0,1}':= :integer[])=0A=0A=0AWhen there's a condition on both arrays, this then lead= s to a seq scan:=0A=0A# explain analyze select * from test where=A0intarr1= =A0&& '{0,1}' and=A0intarr2=A0&& '{2,4}' order by sortcol 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 QUERY PLAN =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=3D3241.28..324= 1.29 rows=3D1 width=3D217) (actual time=3D88.260..88.265 rows=3D10 loops=3D= 1)=0A=A0=A0 -> =A0Sort =A0(cost=3D3241.28..3241.29 rows=3D1 width=3D217) (a= ctual time=3D88.258..88.260 rows=3D10 loops=3D1)=0A=A0=A0 =A0 =A0 =A0 Sort = Key:=A0sortcol=0A=A0=A0 =A0 =A0 =A0 Sort Method: =A0top-N heapsort =A0Memor= y: 27kB=0A=A0=A0 =A0 =A0 =A0 -> =A0Seq Scan on test =A0(cost=3D0.00..3241.2= 7 rows=3D1 width=3D217) (actual time=3D0.169..68.785 rows=3D40018 loops=3D1= )=0A=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: ((intarr1 && '{0,1}'::integer[])= AND (intarr2=A0&& '{2,4}'::integer[]))=0A=A0Total runtime: 88.339 ms=0A=0A= =0AAdding a GIN index on the two arrays results in similar ugliness:=0A=0A#= explain analyze select * from test where=A0intarr1=A0&& '{0,1}' and=A0inta= rr2=A0&& '{2,4}' order by lft 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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN =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 =A0 =A0 =A0 =A0 =A0=A0=0A----------------------= ---------------------------------------------------------------------------= -----------------------------------------------------------=0A=A0Limit =A0(= cost=3D8.29..8.29 rows=3D1 width=3D217) (actual time=3D56.122..56.127 rows= =3D10 loops=3D1)=0A=A0=A0 -> =A0Sort =A0(cost=3D8.29..8.29 rows=3D1 width= =3D217) (actual time=3D56.120..56.122 rows=3D10 loops=3D1)=0A=A0=A0 =A0 =A0= =A0 Sort Key: sortcol=0A=A0=A0 =A0 =A0 =A0 Sort Method: =A0top-N heapsort = =A0Memory: 27kB=0A=A0=A0 =A0 =A0 =A0 -> =A0Bitmap Heap Scan on test =A0(cos= t=3D4.26..8.28 rows=3D1 width=3D217) (actual time=3D19.635..39.824 rows=3D4= 0018 loops=3D1)=0A=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 Recheck Cond: ((intarr1=A0= && '{0,1}'::integer[]) AND (intarr2=A0&& '{2,4}'::integer[]))=0A=A0=A0 =A0 = =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on test_intarr1_intarr2_idx =A0= (cost=3D0.00..4.26 rows=3D1 width=3D0) (actual time=3D19.387..19.387 rows= =3D40018 loops=3D1)=0A=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond= : ((intarr1=A0&& '{0,1}'::integer[]) AND (intarr2=A0&& '{2,4}'::integer[]))= =0A=A0Total runtime: 56.210 ms=0A=0A=0AMight this be a bug in the operator'= s selectivity, or am I doing something wrong?=0A=0AThanks in advance,=0ADen= is=0A