Message-ID: <3B8679E4.F0FE7386@enseirb.fr>
Date: Fri, 24 Aug 2001 17:59:32 +0200
From: Richard Bayet <bayet@enseirb.fr>
MIME-Version: 1.0
To: Vida Luz Arista <vlal@ideay.net.ni>
CC: pgsql-admin@postgresql.org
Subject: Re: Query
References: <Pine.LNX.4.21.0108240843480.22004-100000@ideay.net.ni>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Vida Luz Arista a écrit :

> Hi All, I have two questions
>
> 1- I have a query but this is very slow, I need to acelerate the
> consult, this result sill be show in the web.
>
> My query is:
>
> select user, descripcion from client where login not in (select
> distinct(usr_cliente) from conexion where fecha between '$date_begin' and
> '$date_end') order by user"
>
> How can I to acelerate this consult?
>
> 2- I made this function:
>
> CREATE FUNCTION list(date,date) RETURNS user
>      AS 'select distinct(usr_cliente) from conexion where fecha between
>         $date_begin and $date_end' LANGUAGE 'sql';
>
> When I execute a query to this function like:
> select select list('08-10-2001', '08-13-2001') as user;
>
> the result is just 1 row, however If I to execute the query this return
> 300 rows, why the function only return one row?
>

The query's returning the exact opposite of your function, so if you have
301 rows in your table and the function returns you 1, the query'll get 300
:)
select user, descripcion from client where login not in (select
distinct(usr_cliente) from conexion where fecha between '$date_begin' and
'$date_end') order by user"
is equivalent to:
select user, descripcion from client where login not in (select
list('$date_begin', '$date_end'))

PS: a misused "not in" clause can result in very poor performances (it
depends actually of your table contents) : if "everything that is not ..."
is huge, it's quicker to get "everything that is ..." :)

>
> Thanks in advanced,
>
> Regards,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)