Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id DE5F29ED582 for ; Wed, 7 Dec 2011 11:16:13 -0400 (AST) Received: from mail.technocon.com ([62.132.195.121]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RYJE8-0006d0-Ls for pgsql-performance@postgresql.org; Wed, 07 Dec 2011 15:16:13 +0000 Received: from meel (localhost [127.0.0.1]) by mail.technocon.com (Postfix) with ESMTP id 8E33733ABE1 for ; Wed, 7 Dec 2011 16:16:02 +0100 (CET) Received: from localhost ([127.0.0.1]) by meel with SMTP ID 146 for ; Wed, 7 Dec 2011 16:16:02 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by mail.technocon.com (Postfix) with ESMTP id 4981133ABE1 for ; Wed, 7 Dec 2011 16:16:02 +0100 (CET) Received: from mail.technocon.com ([127.0.0.1]) by localhost (meel.technocon.local [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id iwYlEXDF7A5Y for ; Wed, 7 Dec 2011 16:15:52 +0100 (CET) Received: from meel.technocon.local (localhost [127.0.0.1]) by mail.technocon.com (Postfix) with ESMTP id 876F233AC1A for ; Wed, 7 Dec 2011 16:15:52 +0100 (CET) Subject: Partitions and joins lead to index lookups on all partitions From: =?utf-8?Q?Christiaan_Willemsen?= To: =?utf-8?Q?pgsql-performance=40postgresql=2Eorg?= Date: Wed, 7 Dec 2011 16:15:52 +0100 Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=_EcUTosX6EyoApSIEfv+p9szLv+O3WBZ3myJzr+lbtb2YIIdw" X-Priority: 3 (Normal) X-Mailer: Zarafa 7.0.1-28479 Message-Id: X-Pg-Spam-Score: -3.1 (---) X-Archive-Number: 201112/60 X-Sequence-Number: 45859 This is a multi-part message in MIME format. Your mail reader does not understand MIME message format. --=_EcUTosX6EyoApSIEfv+p9szLv+O3WBZ3myJzr+lbtb2YIIdw Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi there,=0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0ACurrently,= we are running into serious performance problems with our paritioning se= tup, because index lookups are mostly done on allpartions, in stead of th= e one partition it should know that it can find the needed row.=0D=0A=0D=0A= =20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0ASimple example, were we have a = partitioned tables named part_table. So here it goes:=0D=0A=0D=0A=20=0D=0A= =0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0Aselect * from part_table where id =3D 12= 123231=0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0AWill do an in= dex lookup only in the partition that it knows it can find the id there. = However:=0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0Aselect * fr= om part_table where id =3D (select 12123231)=0D=0A=0D=0A=20=0D=0A=0D=0A=C2= =A0=0D=0A=20=0D=0A=0D=0AWill do an index lookup in ALL partitions, meanin= g it is significantly slower, even more since the database will not fit i= nto memory.=0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0ASo okay,= we could just not use parameterized queries... Well.. not so fast. Consi= der a second table referencing to the first:=0D=0A=0D=0A=20=0D=0A=0D=0A=C2= =A0=0D=0A=20=0D=0A=0D=0Aref_table:=0D=0A=0D=0A=20=0D=0A=0D=0Agroup_id big= int=0D=0A=0D=0A=20=0D=0A=0D=0Apart_table_id bigint=0D=0A=0D=0A=20=0D=0A=0D= =0A=C2=A0=0D=0A=20=0D=0A=0D=0ANow when I join the two:=0D=0A=0D=0A=20=0D=0A= =0D=0Aselect part_table.* from part_table=0D=0A=0D=0A=20=0D=0A=0D=0Ajoin = ref_table on (ref_table.part_table_id =3D=C2=A0part_table.id and=C2=A0gro= up_id =3D 12321)=0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0AIt = will also do index loopups on ALL partitions.=C2=A0=0D=0A=0D=0A=20=0D=0A=0D= =0A=C2=A0=0D=0A=20=0D=0A=0D=0AHow do we handle this=3F Above queries are = simplified versions of the things gooing on but the idea is clear. I trie= d dooing this in 9.1 (we are currently using 9.0), but this does not matt= er. So what is actually the practicial use of partitioning if you can't e= ven use it effectively for simple joins=3F=0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0= =0D=0A=20=0D=0A=0D=0Aconstraint_exclusion is enabled correctly, and as fa= r as I can see, this behaviour is according to the book.=0D=0A=0D=0A=20=0D= =0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0AAre there any progresses in maybe 9.2= to make this any better=3F If not, how schould we handle this=3F We can = also not choose to parition, but how will that perform on a 100 GB table=3F= =0D=0A=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0AKind regards,=0D=0A= =0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0AChristiaan Willemsen=0D=0A= =0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0A= =C2=A0=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A=20=0D=0A=0D=0A=C2=A0=0D=0A --=_EcUTosX6EyoApSIEfv+p9szLv+O3WBZ3myJzr+lbtb2YIIdw Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable =0A=0A =0A =0A Partitions an= d joins lead to index lookups on all partitions=0A =0A=0A=0A<= p>Hi there,

 

Currently, we are running into serious p= erformance problems with our paritioning setup, because index lookups are= mostly done on allpartions, in stead of the one partition it should know= that it can find the needed row.

 

Simple example, we= re we have a partitioned tables named part_table. So here it goes:

 

select * from part_table where id =3D 12123231

&nbs= p;

Will do an index lookup only in the partition that it knows it = can find the id there. However:

 

select * from part_t= able where id =3D (select 12123231)

 

Will do an index= lookup in ALL partitions, meaning it is significantly slower, even more = since the database will not fit into memory.

 

So okay= , we could just not use parameterized queries... Well.. not so fast. Cons= ider a second table referencing to the first:

 

ref_ta= ble:

group_id bigint

part_table_id bigint

 

=

Now when I join the two:

select part_table.* from part_table

join ref_table on (ref_table.part_table_id =3D part_table.id a= nd group_id =3D 12321)

 

It will also do index lo= opups on ALL partitions. 

 

How do we handle this= =3F Above queries are simplified versions of the things gooing on but the= idea is clear. I tried dooing this in 9.1 (we are currently using 9.0), = but this does not matter. So what is actually the practicial use of parti= tioning if you can't even use it effectively for simple joins=3F

=

 

constraint_exclusion is enabled correctly, and as far as= I can see, this behaviour is according to the book.

 

Are there any progresses in maybe 9.2 to make this any better=3F If not,= how schould we handle this=3F We can also not choose to parition, but ho= w will that perform on a 100 GB table=3F

 

Kind regard= s,

 

Christiaan Willemsen

 

 <= /p>

 

 

 

=0A=0A --=_EcUTosX6EyoApSIEfv+p9szLv+O3WBZ3myJzr+lbtb2YIIdw--