pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: pgsql-performance@postgresql.org
Subject: index questions
Date: 25 Jul 2003 13:52:43 -0300
Message-ID: <1059151963.1183.14.camel@taz> (raw)

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=a_clientId AND
      branchId=coalesce(a_branchId, branchId) AND /*branchId is null?
anything will be ok*/
      productId=coalesce(a_productId, productId) AND /*productId is
null? anything will be ok*/
      employeeId=coalesce(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.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

view thread (4+ messages)  latest in thread

Message-ID: <1059151963.1183.14.camel@taz>
Permalink:  ../1059151963.1183.14.camel@taz/
Also on:    postgresql.org/message-id/1059151963.1183.14.camel@taz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: franco@akyasociados.com.ar
  Subject: Re: index questions
  In-Reply-To: <1059151963.1183.14.camel@taz>
  References: <1059151963.1183.14.camel@taz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox