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 (
price NUMERIC(12, 2),
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
* 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 '
a_clientId ALIAS FOR $1;
a_branchId ALIAS FOR $1;
a_productId ALIAS FOR $1;
a_employeeId ALIAS FOR $1;
INTO r_result employeeId
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*/
clientId, branchId, productId, employeeId, saleDate, price, qty
' 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.
Description: This is a digitally signed message part