Re: index questions

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
> large as the table. Try indexing only the first 3-4 columns instead.
>
> > 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
> columns are queried starting from the left. That is, the index will be
> ignored if you have a "where productId = x" without a "where branchid = 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=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*/
>
> On a very large table this will be very inefficient. you'll be comparing the
> productid, for example, even if no productid is passed ... and the index
> won't do you any good because the planner should figure out that 100% of rows
> match the condition.
>
> Instead, I recommend that you build up a dynamic query as a string and then
> pass only the conditions sent by the user. You can then EXECUTE the query
> and loop through it for a result.
>
> Of course, YMMV. My approach will require you to create more indexes which
> could be a problem if you have limited disk space.