От: Franco Bruno Borghesi
Тема: Re: index questions
Дата: ,
Msg-id: 1059158034.1183.43.camel@taz
(см: обсуждение, исходный текст)
Ответ на: Re: index questions  (Josh Berkus)
Список: pgsql-performance

Скрыть дерево обсуждения

index questions  (Franco Bruno Borghesi, )
 Re: index questions  (Ron Johnson, )
 Re: index questions  (Josh Berkus, )
  Re: index questions  (Franco Bruno Borghesi, )

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.
Вложения

В списке pgsql-performance по дате сообщения:

От: Franco Bruno Borghesi
Дата:
Сообщение: Re: index questions
От: "Mendola Gaetano"
Дата:
Сообщение: Wrong rows number expected