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 D511AD1C921 for ; Fri, 25 Jul 2003 16:49:20 +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 55676-03 for ; Fri, 25 Jul 2003 13:48:33 -0300 (ADT) Received: from lucifer.akyasociados.com.ar (unknown [200.69.203.237]) by svr1.postgresql.org (Postfix) with ESMTP id 9DA88D1C91D for ; Fri, 25 Jul 2003 13:49:18 -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 h6PGrLso031523 for ; Fri, 25 Jul 2003 13:53:22 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: index questions From: Franco Bruno Borghesi To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-t96Net/9du4gesUrepoA" Organization: AK y Asociados S.R.L. Message-Id: <1059151963.1183.14.camel@taz> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 25 Jul 2003 13:52:43 -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/418 X-Sequence-Number: 2711 --=-t96Net/9du4gesUrepoA Content-Type: multipart/alternative; boundary="=-sIlLrYTowgWsSvWGel50" --=-sIlLrYTowgWsSvWGel50 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hi everyone. I have created a simplified example of a real case, to show you what I'm tryng to do. I have a table, like this: CREATE TABLE sales ( saleId SERIAL, clientId INTEGER, branchId INTEGER, productId INTEGER, employeeId INTEGER, saleDate DATE, price NUMERIC(12, 2), qty INTEGER, PRIMARY KEY(saleId) ); CREATE INDEX sales_k1 ON sales(clientId, branchId, productId, employeeId, saleDate, price, qty); This table will grow to *many* rows in the future. 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). The fetched resultset shoud prioritize the passed arguments, and after that, the saleDate, price and quantity. /** * Finds the first sale that matches the conditions received. * @param $1 Client Id. * @param $2 Preferred Branch Id. * @param $3 Preferred Product Id. * @param $4 Preferred Employee Id. * @return Sale Id if found, NULL if not. */ 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; r_result INTEGER; BEGIN SELECT INTO r_result employeeId FROM sales WHERE clientId=3Da_clientId AND 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*/ employeeId=3Dcoalesce(a_employeeId, employeeId) /*employeeId is null? anything will be ok*/ ORDER BY clientId, branchId, productId, employeeId, saleDate, price, qty LIMIT 1; RETURN r_result; END; ' LANGUAGE 'plpgsql'; Will findSale() in the future, when I have *many* rows still use the index when only the first couple of arguments are passed to the function? If not, should I create more indexes (and functions) for each possible argument combination? (of course, with the given order) The thing here is that I don't understand how postgreSQL solves the query when the COALESCEs are used... it uses the index now, with a few thowsand records, but what will happen in a few months? Thanks in advance. --=-sIlLrYTowgWsSvWGel50 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi everyone.

I have created a simplified example of a real case, to show you what I'm tr= yng to do. I have
a table, like this:

CREATE TABLE sales (
   saleId SERIAL,
   clientId INTEGER,
   branchId INTEGER,
   productId INTEGER,
   employeeId INTEGER,
   saleDate DATE,
   price NUMERIC(12, 2),
   qty INTEGER,
   PRIMARY KEY(saleId)
);
CREATE INDEX sales_k1 ON sales(clientId, branchId, productId, employeeId, s= aleDate, price, qty);

This table will grow to *many* rows in the future.

I want to make a function that returns the FIRS saleId of the sale that mat= ches some conditions. I will
always receive the Client Id, but not always the other arguments (sent as N= ULLs).

The fetched resultset shoud prioritize the passed arguments, and after that= , the saleDate, price
and quantity.


/**
* Finds the first sale that matches the conditions received.
* @param $1 Client Id.
* @param $2 Preferred Branch Id.
* @param $3 Preferred Product Id.
* @param $4 Preferred Employee Id.
* @return Sale Id if found, NULL if not.
*/
CREATE OR REPLACE FUNCTION findSale(INTEGER, INTEGER, INTEGER, INTEGER) RET= URNS INTEGER AS '
DECLARE
   a_clientId ALIAS FOR $1;
   a_branchId ALIAS FOR $1;
   a_productId ALIAS FOR $1;
   a_employeeId ALIAS FOR $1;
   r_result INTEGER;
BEGIN
   SELECT
      INTO r_result employeeId
   FROM
      sales
   WHERE
      clientId=3Da_clientId AND
      branchId=3Dcoalesce(a_branchId, branchId) AN= D /*branchId is null? anything will be ok*/
      productId=3Dcoalesce(a_productId, productId)= AND /*productId is null? anything will be ok*/
      employeeId=3Dcoalesce(a_employeeId, employee= Id) /*employeeId is null? anything will be ok*/
   ORDER BY
      clientId, branchId, productId, employeeId, s= aleDate, price, qty
   LIMIT 1;

   RETURN r_result;
END;
' LANGUAGE 'plpgsql';


Will findSale() in the future, when I have *many* rows still use the index = when only the first couple of
arguments are passed to the function?
If not, should I create more indexes (and functions) for each possible argu= ment combination? (of course, with
the given order)

The thing here is that I don't understand how postgreSQL solves the query w= hen the COALESCEs are used... it uses
the index now, with a few thowsand records, but what will happen in a few m= onths?

Thanks in advance. --=-sIlLrYTowgWsSvWGel50-- --=-t96Net/9du4gesUrepoA 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/IWBb21dVnhLsBV0RAp5uAJ493JzXwtvu4eO1zKBFW9ZAtMMBEwCghIxZ lis+TGLkVIi1XMhTXEq0pBY= =ADDY -----END PGP SIGNATURE----- --=-t96Net/9du4gesUrepoA--