Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XGvbG-0002Iq-Mc for pgsql-sql@arkaria.postgresql.org; Mon, 11 Aug 2014 19:49:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XGvbF-0004f0-T1 for pgsql-sql@arkaria.postgresql.org; Mon, 11 Aug 2014 19:49:45 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XGvbA-0004Ux-5S for pgsql-sql@postgresql.org; Mon, 11 Aug 2014 19:49:40 +0000 Received: from nm31-vm1.bullet.mail.ir2.yahoo.com ([212.82.97.88]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XGvb6-00020y-1t for pgsql-sql@postgresql.org; Mon, 11 Aug 2014 19:49:38 +0000 Received: from [212.82.98.51] by nm31.bullet.mail.ir2.yahoo.com with NNFMP; 11 Aug 2014 19:49:33 -0000 Received: from [212.82.98.94] by tm4.bullet.mail.ir2.yahoo.com with NNFMP; 11 Aug 2014 19:49:33 -0000 Received: from [127.0.0.1] by omp1031.mail.ir2.yahoo.com with NNFMP; 11 Aug 2014 19:49:33 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 212795.89602.bm@omp1031.mail.ir2.yahoo.com Received: (qmail 9083 invoked by uid 60001); 11 Aug 2014 19:49:33 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.fr; s=s1024; t=1407786573; bh=sLyk51OsRGwebiZ8SvmauCydQqxsEJl2tGswLU4Z6HY=; h=References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=a0+kNJcNWaRmCN6WmgqzGTAZnG7wZ3QEbTuF3WJf54QhRu8CYqJ2jmhCeJYg1rqcvmoTsVX14pgtWBI4p6z/HwF5sfge+srE2jscX+J4u25rncCuZKW3KktnnKsx5YQ9N8sfJ2MbJLTAISEN7J7wAnRcJnwwuNnHr8b/Gruw0hk= X-YMail-OSG: 8debELwVM1mXybqREdVjM1YbI31fi.BcikR7nRMPk9KNKmK knGmqiSshJrmodCzpkqllhsyueEpEmJanzYzKxZPpp00kb6Z9LEFJhT0r8oh LnN4FV91.pJyavLJG1ceSrwdag4MWNQHL6jfYk2xGXH_8RQgBkmau1SJRvGn hyOJAJVcW5.0M3zKqWOrj_HfNrbCQIVoZRfAIUyAHkETOBQiKjvBNhZy1TY. UHCq8SLegiAbvVnbn.qlQnl46bIhpoR7XP_cGwhpTBQDkjUWnbtkFRonFgDl vxePJnJT2tiWflYBsh977VWsvN3GnCRRFxPwCtf1et9ejEdMeU3p4DR5bu8s VXs7_Q3V1JegxtcmTb1h9b23TjPe74Xpi7fXDgZyvvqD7SoBVGFYO7qyAQU2 fXPaI9H0csrC9wwKehumo2AHG_nWIMDKAQss.thz0_0sUTHI4rB2gqPrRPic yKvAU9Ft0.34q5HUSllbfBQzPH4O5Z0Q7W4vG1YyBL9FA0MmyRkH_iX7Qvh5 IWcftToEEsT6iCNDaR4m5N1bWH_J8xp5aAj2UMrSDW2zASntghSOdryE8Eg2 1iEU4KwS6.1UCG1QL9VpA3y3GSKSZ1OkbrtctO7a_5FlUO3aRz9lxU5Zcn5G kkzMLIIADkoCFeSmqoK9tIvmyYgf4ChF3dPe_iBnKk.jSgL01UIj0PK8Oxjy 4J3VUWHN5ORVGdWIaDtvuYWIuMOzKA0S__iy3.VAvCieQ0nYYch8Vv4wUNFG IQs.eazZgt_soQxJ6pvQ8WVgQoTxoEEVXhe7hexUKEMckhPjU Received: from [132.203.171.115] by web172205.mail.ir2.yahoo.com via HTTP; Mon, 11 Aug 2014 20:49:32 BST X-Rocket-MIMEInfo: 002.001, SGVsbG8KCkkgYW0gbG9va2luZyBmb3IgYSBmdW50aW9uIHRvIGNhbGN1bGF0ZSB0aGUgc2l6ZSBvZiBhIHJlY29yZCBpbiBhIHRhYmxlIGluIGEgZGF0YWJhc2UuIFRoYW5rIHlvdQpBbm5lZAoKCgpMZSBMdW5kaSAxMSBhb8O7dCAyMDE0IDE0aDM3LCBQYXZlbCBTdGVodWxlIDxwYXZlbC5zdGVodWxlQGdtYWlsLmNvbT4gYSDDqWNyaXQgOgogCgoKSGkKCgoKCjIwMTQtMDgtMTEgMTg6MDEgR01UKzAyOjAwIEFuZHJlYXMgSm9zZXBoIEtyb2doIDxhbmRyZWFzQHZpc2VuYS5jb20.OgoKSGkgZm9sa3MsCj7CoAoBMAEBAQE- X-Mailer: YahooMailWebService/0.8.198.689 References: Message-ID: <1407786572.35943.YahooMailNeo@web172205.mail.ir2.yahoo.com> Date: Mon, 11 Aug 2014 20:49:32 +0100 From: SENADIN Reply-To: SENADIN Subject: Re: How to optimize WHERE column_a IS NOT NULL OR column_b = 'value' To: Pavel Stehule , Andreas Joseph Krogh Cc: postgres list In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-97308854-2138609197-1407786572=:35943" X-Pg-Spam-Score: -1.5 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ---97308854-2138609197-1407786572=:35943 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello=0A=0AI am looking for a funtion to calculate the size of a record in = a table in a database. Thank you=0AAnned=0A=0A=0A=0ALe Lundi 11 ao=C3=BBt 2= 014 14h37, Pavel Stehule a =C3=A9crit :=0A =0A=0A= =0AHi=0A=0A=0A=0A=0A2014-08-11 18:01 GMT+02:00 Andreas Joseph Krogh :=0A=0AHi folks,=0A>=C2=A0=0A>I have the following schema (sim= plified for this example).=0A>=C2=A0=0A>create table folder(id integer prim= ary key, name varchar not null);=0A>=C2=A0=0A>create table document(id seri= al primary key, name varchar not null, owner_id integer not null, folder_id= integer references folder(id));=0A>create index document_owner_idx ON docu= ment(owner_id);=0A>create index document_folder_idx ON document(folder_id);= =0A>=C2=A0=0A>insert into folder(id, name) values(1, 'Folder A');=0A>insert= into folder(id, name) values(2, 'Folder B');=0A>insert into document(name,= owner_id, folder_id) values('Document A',=C2=A0 1, 1);=0A>insert into docu= ment(name, owner_id, folder_id) values('Document B',=C2=A0 1, NULL);=0A>ins= ert into document(name, owner_id, folder_id) values('Document C',=C2=A0 2, = 2);=0A>insert into document(name, owner_id, folder_id) values('Document D',= =C2=A0 2, NULL);=0A>=C2=A0=0A>select f.id, f.name, doc.id, doc.owner_id, do= c.name=0A>FROM document doc left outer join folder f ON doc.folder_id =3D f= .id=0A>WHERE doc.folder_id is not null OR doc.owner_id =3D 1;=0A>=C2=A0=0A>= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN=0A>------------------------= ---------------------------------------------------------------------------= --------------------------=0A>=C2=A0Nested Loop Left Join=C2=A0 (cost=3D0.1= 5..13.77 rows=3D4 width=3D76) (actual time=3D0.031..0.045 rows=3D3 loops=3D= 1)=0A>=C2=A0=C2=A0 ->=C2=A0 Seq Scan on document doc=C2=A0 (cost=3D0.00..1.= 05 rows=3D4 width=3D44) (actual time=3D0.012..0.018 rows=3D3 loops=3D1)=0A>= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: ((folder_id IS NOT= NULL) OR (owner_id =3D 1))=0A>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Rows Removed by Filter: 1=0A>=C2=A0=C2=A0 ->=C2=A0 Index Scan using = folder_pkey on folder f=C2=A0 (cost=3D0.15..3.17 rows=3D1 width=3D36) (actu= al time=3D0.005..0.006 rows=3D1 loops=3D3)=0A>=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Index Cond: (doc.folder_id =3D id)=0A>=C2=A0Planning = time: 0.267 ms=0A>=C2=A0Execution time: 0.094 ms=0A>(8 rows)=0A>=C2=A0=0A>= =C2=A0=0A>Is the a way to write a query which uses an index efficiently for= such a schema?=0A>=C2=A0=0A>I'd like to eliminate the Filter: ((folder_id = IS NOT NULL) OR (owner_id =3D 1)) and rather have "index cond" insted, is t= hat possible?=0A=0Ayour example is partially broken - ANALYZE and hashjoin = and seqscan penalization are missing - index scan is not used due too small= table sizes=0A=0A=0AI tested 9.5, probably same as 9.4 and there indexes a= re used=0A=0Apostgres=3D# set enable_hashjoin to off;=0ASET=0ATime: 0.473 m= s=0Apostgres=3D# set enable_seqscan to off;=0ASET=0ATime: 0.904 ms=0Apostgr= es=3D# explain select f.id, f.name, doc.id, doc.owner_id, doc.name=0AFROM d= ocument doc left outer join folder f ON doc.folder_id =3D f.id=0AWHERE doc.= folder_id is not null OR doc.owner_id =3D 1;=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= QUERY PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =0A=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=0A=C2=A0Merge Left Join=C2=A0 (cost=3D0.26..24.38 rows=3D3 width= =3D32)=0A=C2=A0=C2=A0 Merge Cond: (doc.folder_id =3D f.id)=0A=C2=A0=C2=A0 -= >=C2=A0 Index Scan using document_folder_idx on document doc=C2=A0 (cost=3D= 0.13..12.20 rows=3D3 width=3D23)=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Filter: ((folder_id IS NOT NULL) OR (owner_id =3D 1))=0A=C2=A0=C2= =A0 ->=C2=A0 Index Scan using folder_pkey on folder f=C2=A0 (cost=3D0.13..1= 2.16 rows=3D2 width=3D13)=0A=C2=A0Planning time: 0.663 ms=0A(6 rows)=0A=0A= =0Adefault 9.2, 9.3, ...=0A=0Apostgres=3D# explain select f.id, f.name, doc= .id, doc.owner_id, doc.name=0AFROM document doc left outer join folder f ON= doc.folder_id =3D f.id=0AWHERE doc.folder_id is not null OR doc.owner_id = =3D 1;=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =0A=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=0A=C2=A0Hash Left Join=C2=A0 (cost=3D1.04..2.12 rows=3D3 wi= dth=3D32)=0A=C2=A0=C2=A0 Hash Cond: (doc.folder_id =3D f.id)=0A=C2=A0=C2=A0= ->=C2=A0 Seq Scan on document doc=C2=A0 (cost=3D0.00..1.05 rows=3D3 width= =3D23)=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: ((folder_= id IS NOT NULL) OR (owner_id =3D 1))=0A=C2=A0=C2=A0 ->=C2=A0 Hash=C2=A0 (co= st=3D1.02..1.02 rows=3D2 width=3D13)=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 ->=C2=A0 Seq Scan on folder f=C2=A0 (cost=3D0.00..1.02 rows=3D= 2 width=3D13)=0A(6 rows)=0A=0A=0Aand 9.2 after hashjoin and indexscan penal= ization=0A=0Apostgres=3D# explain select f.id, f.name, doc.id, doc.owner_id= , doc.name=0AFROM document doc left outer join folder f ON doc.folder_id = =3D f.id=0AWHERE doc.folder_id is not null OR doc.owner_id =3D 1;=0A=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 QUERY PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =0A=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=0A=C2=A0Merge Left Join=C2=A0 (cost=3D0.00..= 24.62 rows=3D3 width=3D32)=0A=C2=A0=C2=A0 Merge Cond: (doc.folder_id =3D f.= id)=0A=C2=A0=C2=A0 ->=C2=A0 Index Scan using document_folder_idx on documen= t doc=C2=A0 (cost=3D0.00..12.32 rows=3D3 width=3D23)=0A=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: ((folder_id IS NOT NULL) OR (owner_i= d =3D 1))=0A=C2=A0=C2=A0 ->=C2=A0 Index Scan using folder_pkey on folder f= =C2=A0 (cost=3D0.00..12.28 rows=3D2 width=3D13)=0A(5 rows)=0A=0ATime: 2.258= ms=0A=0A=0AWhat is your PostgreSQL?=0A=0A=0ARegards=0A=0A=0APavel=0A=0A=0A= P.S. ten years ago I had a similar issue - "OR" predikates can be replaced = by UNION=0A=0A=0Ayou can try:=0A=0A=0ASELECT * FROM=0A=0A(SELECT * FROM doc= =0A=C2=A0 WHERE folder_id IS NOT NULL=0A=0AUNION=0A=0A=C2=A0 SELECT * FROM= doc=0A=0A=C2=A0 WHERE owner_id =3D 1) s=0A=0A=C2=A0 LEFT JOIN folder ON s.= folder_id =3D folder.id=0A=0A=0Aor some similar magic=0A=0A=0A=0Aselect f.i= d, f.name, doc.id, doc.owner_id, doc.name=0AFROM document doc left outer jo= in folder f ON doc.folder_id =3D f.id=0AWHERE doc.folder_id is not null OR = doc.owner_id =3D 1;=0A=0A=0A=C2=A0=0A=C2=A0=0A>-- =0A>Andreas Joseph Krogh= =0A>CTO / Partner - Visena AS=0A>Mobile: +47 909 56 963=0A>andreas@visena.c= om=0A>www.visena.com ---97308854-2138609197-1407786572=:35943 Content-Type: multipart/related; boundary="-97308854-161707522-1407786572=:35943" ---97308854-161707522-1407786572=:35943 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hello
<= /span>
I am looking for a funtion to calculate the siz= e of a record in a table in a database. Thank you
Anned


Le Lundi 11 ao=C3=BBt 2014 14h37, Pavel Stehule <pavel.stehule@gmail.com&g= t; a =C3=A9crit :


Hi


=
2014-08-11 18:01 GMT+02:00 Andreas = Joseph Krogh <andreas@visena.com>:
=0A=0A
Hi folks,=0A=0A
 
=0A=0A
I have the following schema (simplifi= ed for this example).
=0A=0A
 
=0A=0A
create table f= older(id integer primary key, name varchar not null);
=0A=0A
 = ;
=0A=0A
create table document(id serial primary key, name varchar= not null, owner_id integer not null, folder_id integer references folder(i= d));
=0A=0A
create index document_owner_idx ON document(owner_id);=
=0A=0A
create index document_folder_idx ON document(folder_id);=0A=0A
 
=0A=0A
insert into folder(id, name) values(1= , 'Folder A');
=0A=0A
insert into folder(id, name) values(2, 'Fold= er B');
=0A=0A
insert into document(name, owner_id, folder_id) val= ues('Document A',  1, 1);
=0A=0A
insert into document(name, o= wner_id, folder_id) values('Document B',  1, NULL);
=0A=0A
in= sert into document(name, owner_id, folder_id) values('Document C',  2,= 2);
=0A=0A
insert into document(name, owner_id, folder_id) values= ('Document D',  2, NULL);
=0A=0A
 
=0A=0A
selec= t f.id, f.name, doc.id, doc.owner_id, doc.name=0A=0A=0A=0A
FROM document doc left outer join folder f ON doc.folder_= id =3D f.id
=0A=0A
WHERE doc.folder_id is not null OR doc.owne= r_id =3D 1;
=0A=0A
 
=0A=0A
=0A
  &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;    QUERY PLAN
=0A----------------------= ---------------------------------------------------------------------------= ----------------------------
=0A Nested Loop Left Jo= in  (cost=3D0.15..13.77 rows=3D4 width=3D76) (actual time=3D0.031..0.0= 45 rows=3D3 loops=3D1)
=0A   ->  Seq Sc= an on document doc  (cost=3D0.00..1.05 rows=3D4 width=3D44) (actual ti= me=3D0.012..0.018 rows=3D3 loops=3D1)
=0A  &nbs= p;      Filter: ((folder_id IS NOT NULL) OR (owner= _id =3D 1))
=0A       =   Rows Removed by Filter: 1
=0A   ->&nb= sp; Index Scan using folder_pkey on folder f  (cost=3D0.15..3.17 rows= =3D1 width=3D36) (actual time=3D0.005..0.006 rows=3D1 loops=3D3)
=0A         Index Cond: (= doc.folder_id =3D id)
=0A Planning time: 0.267 ms=0A Execution time: 0.094 ms
=0A(8 r= ows)
=0A
=0A=0A
 
=0A=0A
 
=0A=0AIs the a way to write a query which uses an index efficiently for such a s= chema?
=0A=0A
 
=0A=0A
I'd like to eliminate the Fil= ter: ((folder_id IS NOT NULL) OR (owner_id =3D 1)) and rather have "index c= ond" insted, is that possible?

your example is partially broken - ANALYZE and hashjoin and seqsca= n penalization are missing - index scan is not used due too small table siz= es
=0A=0A
I tested 9.5, prob= ably same as 9.4 and there indexes are used

postgres=3D# set enable_hashjoin to off;
SET
Time: 0.473 ms
postgres=3D# set enable_seqsca= n to off;
SET
Time: 0.904 ms
postgres=3D# explain select f.id, f.name, doc.id, doc.owner_id, doc.name
=0A=0AFROM document do= c left outer join folder f ON doc.folder_id =3D f.id
WHERE doc.folder_id is not null OR doc.owner_id =3D 1;
=             &nb= sp;            =             &nb= sp;     QUERY PLAN      &= nbsp;           &nbs= p;            &= nbsp;          
=0A=0A=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80
 Merge Left Join  (cost=3D0.26..24.38 rows=3D3 width=3D32)
   Merge Cond: (doc.folder_id =3D f.id)
=0A=0A   ->  Index Scan using document_folder_i= dx on document doc  (cost=3D0.13..12.20 rows=3D3 width=3D23)
         Filter: ((folder= _id IS NOT NULL) OR (owner_id =3D 1))
   ->&= nbsp; Index Scan using folder_pkey on folder f  (cost=3D0.13..12.16 ro= ws=3D2 width=3D13)
=0A=0A Planning time: 0.663 ms(6 rows)

de= fault 9.2, 9.3, ...

postgres=3D# expla= in select f.id, f.name, doc.id, doc.owner_id, doc.name<= /a>
=0A=0AFROM document doc left outer join folder f ON d= oc.folder_id =3D f.id
WHERE doc.folder_id is not nul= l OR doc.owner_id =3D 1;
     &n= bsp;            = ;           QUERY PLAN&nb= sp;            =             &nb= sp;   
=0A=0A=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80
 Hash Left Join  (cost=3D1.04..2.12 rows=3D3 width= =3D32)
   Hash Cond: (doc.folder_id =3D f.id)
   ->  Seq Scan on document doc&nbs= p; (cost=3D0.00..1.05 rows=3D3 width=3D23)
=0A=0A &n= bsp;       Filter: ((folder_id IS NOT NULL) O= R (owner_id =3D 1))
   ->  Hash  (= cost=3D1.02..1.02 rows=3D2 width=3D13)
   =       ->  Seq Scan on folder f  (cost= =3D0.00..1.02 rows=3D2 width=3D13)
(6 rows)

and 9.2 after hashjoin and indexscan pen= alization
=0A=0A
postgres=3D# explain s= elect f.id, f.name, doc.id, doc.owner_id, doc.name<= /a>
FROM document doc left outer join folder f ON doc.fol= der_id =3D f.id
=0A=0AWHERE doc.folder_id is not null= OR doc.owner_id =3D 1;
     &nb= sp;            =             &nb= sp;            QUERY= PLAN           &nbs= p;            &= nbsp;           &nbs= p;     
=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80
=0A=0A Merge Left Join  (cos= t=3D0.00..24.62 rows=3D3 width=3D32)
   Merge C= ond: (doc.folder_id =3D f.id)
   -> = ; Index Scan using document_folder_idx on document doc  (cost=3D0.00..= 12.32 rows=3D3 width=3D23)
=0A=0A    =      Filter: ((folder_id IS NOT NULL) OR (owner_id =3D = 1))
   ->  Index Scan using folder_pkey= on folder f  (cost=3D0.00..12.28 rows=3D2 width=3D13)
(5 rows)

Time: 2.258 ms

What is your PostgreSQL?
=0A=0A
Regards

Pavel

P.S. ten years ago I had a similar issue - "OR" predikates can be replaced= by UNION

you can try:

SELECT * FROM
=0A=0A
(SELECT * FROM doc
  WHERE folder= _id IS NOT NULL
UNION
=
  SELECT * FROM doc
  WHERE own= er_id =3D 1) s
  LEFT JOIN folder ON s.fo= lder_id =3D folder.id
=0A=0A
<= /div>
or some similar magic


select f.id, f.name, doc.id, doc.owner_id, = doc.name
=0A=0A=0A=0A
FROM document doc left outer join folder= f ON doc.folder_id =3D f.id
=0A=0A
WHERE doc.folder_id is not= null OR doc.owner_id =3D 1;


 
=0A=0A=0A=0A
 
=0A=0A
=0A
--=0A=
Andreas J= oseph Krogh
=0A=0A
CTO / Partner - Visena AS
=0A=0A=0A=0A
<= a rel=3D"nofollow" shape=3D"rect" ymailto=3D"mailto:andreas@visena.com" tar= get=3D"_blank" href=3D"mailto:andreas@visena.com">andreas@visena.com=0A=0A=0A=0A
3D""
=0A
=0A

=0A


---97308854-161707522-1407786572=:35943 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Id: <1.4103022183@web172205.mail.ir2.yahoo.com> Content-Disposition: inline iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ---97308854-161707522-1407786572=:35943-- ---97308854-2138609197-1407786572=:35943--