X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3C2FCD1C4BB for ; Fri, 25 Jul 2003 18:30:41 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 64033-01 for ; Fri, 25 Jul 2003 15:29:54 -0300 (ADT) Received: from lucifer.akyasociados.com.ar (unknown [200.69.203.237]) by svr1.postgresql.org (Postfix) with ESMTP id 94CC4D1C4E1 for ; Fri, 25 Jul 2003 15:30:39 -0300 (ADT) Received: from taz.oficina (taz.oficina [192.168.1.99]) (authenticated bits=0) by lucifer.akyasociados.com.ar (8.12.9/8.12.9) with ESMTP id h6PIYVso033750; Fri, 25 Jul 2003 15:34:32 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: Re: index questions From: Franco Bruno Borghesi To: josh@agliodbs.com Cc: pgsql-performance@postgresql.org In-Reply-To: <200307251028.27261.josh@agliodbs.com> References: <1059151963.1183.14.camel@taz> <200307251028.27261.josh@agliodbs.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-Cs5rownaEIGVqUgz1wmU" Organization: AK y Asociados S.R.L. Message-Id: <1059158034.1183.43.camel@taz> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 25 Jul 2003 15:33:54 -0300 X-MailScanner-Information: Please contact the ISP for more information X-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/422 X-Sequence-Number: 2715 --=-Cs5rownaEIGVqUgz1wmU Content-Type: multipart/alternative; boundary="=-VDkwkZh9iPNnZ25QZjzB" --=-VDkwkZh9iPNnZ25QZjzB Content-Type: text/plain Content-Transfer-Encoding: quoted-printable what you say is that the index is not effective because of its size, but it would still be used *if* the conditions are right... In this case, I care about performance, not space.=20 But what you say about the index not being good because 100% of rows match the condition confirms what I suspected.=20 Thanks for your help. On Fri, 2003-07-25 at 14:28, Josh Berkus wrote: > Franco, >=20 > > CREATE INDEX sales_k1 ON sales(clientId, branchId, productId, > > employeeId, saleDate, price, qty); >=20 > A 7-column index is unlikely to be effective -- the index will be almost = as=20 > large as the table. Try indexing only the first 3-4 columns instead.=20= =20 >=20 > > I want to make a function that returns the FIRS saleId of the sale that > > matches some conditions. I will > > always receive the Client Id, but not always the other arguments (sent > > as NULLs). >=20 > Well, keep in mind that your multi-column index will only be useful if al= l=20 > columns are queried starting from the left. That is, the index will be= =20 > ignored if you have a "where productId =3D x" without a "where branchid = =3D y". >=20 > > CREATE OR REPLACE FUNCTION findSale(INTEGER, INTEGER, INTEGER, INTEGER) > > RETURNS INTEGER AS ' > > DECLARE > > a_clientId ALIAS FOR $1; > > a_branchId ALIAS FOR $1; > > a_productId ALIAS FOR $1; > > a_employeeId ALIAS FOR $1; >=20 > Your aliases are wrong here. >=20 > > branchId=3Dcoalesce(a_branchId, branchId) AND /*branchId is null? > > anything will be ok*/ > > productId=3Dcoalesce(a_productId, productId) AND /*productId is > > null? anything will be ok*/ >=20 > On a very large table this will be very inefficient. you'll be comparin= g the=20 > productid, for example, even if no productid is passed ... and the index= =20 > won't do you any good because the planner should figure out that 100% of = rows=20 > match the condition.=20=20 >=20 > Instead, I recommend that you build up a dynamic query as a string and th= en=20 > pass only the conditions sent by the user. You can then EXECUTE the quer= y=20 > and loop through it for a result. >=20 > Of course, YMMV. My approach will require you to create more indexes wh= ich=20 > could be a problem if you have limited disk space. --=-VDkwkZh9iPNnZ25QZjzB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable what you say is that the index is not effective because of its size, but it= would still be used *if* the conditions are right... In this case, I care = about performance, not space.

But what you say about the index not being good because 100% of rows match = the condition confirms what I suspected.

Thanks for your help.


On Fri, 2003-07-25 at 14:28, Josh Berkus wrote:
Franco,

> CREATE INDEX sales_k1 ON sales(clientId, branchId, productId,
> employeeId, saleDate, price, qty);

A 7-column index is unlikely to be effective -- the index will be almost as=
=20
large as the table.   Try indexing only the first 3-4 columns instead.=20=
=20

> I want to make a function that returns the FIRS saleId of the sale that
> matches some conditions. I will
> always receive the Client Id, but not always the other arguments (sent
> as NULLs).

Well, keep in mind that your multi-column index will only be useful if all=
=20
columns are queried starting from the left.  That is, the index will be=20
ignored if you have a "where productId =3D x" without a "whe=
re branchid =3D y".

> CREATE OR REPLACE FUNCTION findSale(INTEGER, INTEGER, INTEGER, INTEGER)
> RETURNS INTEGER AS '
> DECLARE
>    a_clientId ALIAS FOR $1;
>    a_branchId ALIAS FOR $1;
>    a_productId ALIAS FOR $1;
>    a_employeeId ALIAS FOR $1;

Your aliases are wrong here.

>       branchId=3Dcoalesce(a_branchId, branchId) AND /*branchId is null?
> anything will be ok*/
>       productId=3Dcoalesce(a_productId, productId) AND /*productId is
> null? anything will be ok*/

On a very large table this will be very inefficient.   you'll be comparing =
the=20
productid, for example, even if no productid is passed ... and the index=20
won't do you any good because the planner should figure out that 100% of ro=
ws=20
match the condition.=20=20

Instead, I recommend that you build up a dynamic query as a string and then=
=20
pass only the conditions sent by the user.  You can then EXECUTE the query=
=20
and loop through it for a result.

Of course, YMMV.   My approach will require you to create more indexes whic=
h=20
could be a problem if you have limited disk space.
--=-VDkwkZh9iPNnZ25QZjzB-- --=-Cs5rownaEIGVqUgz1wmU Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/IXgR21dVnhLsBV0RAh9iAJ9f6869W0UsR4gRjo7MERpdqOqCigCeIeMk ZnnnUO88Ea8jw30pA704Vk0= =mOkO -----END PGP SIGNATURE----- --=-Cs5rownaEIGVqUgz1wmU--