Re: [Sqlgrey-users] SQL Query optimization

[Michel B. <mi...@bo...>]

Le Lundi 21 F=E9vrier 2005 00:34, Lionel Bouton a =E9crit :
>
> If you do an explain on this :
> select * from from_awl where last_seen < timestamp '2005-02-21
> 00:12:46.22726+01' - INTERVAL '30 DAY';
>
> You'll find that PostgreSQL is doing the right thing.

Yes. But if we calculate the now() outside of the SELECT clause, we can a=
s=20
well perform the interval calculation at the same time, which will make t=
he=20
SQL query simpler.

> I considered doing the now() call outside the database earlier (in fact
> when SQLite support was added). I want to avoid this if I can because
> I'd like to allow for several SQLgrey instances accessing the same
> database.

What is the interest of having several SQLgrey servers access the same=20
database ? Wouldn't it be more logical to rather have several MTAs access=
 the=20
same SQLgrey server ?

First the network dialog between MTA <=3D> SQLgrey server will be much li=
ghter=20
than the network dialog SQLgrey server <=3D> SQL DB.

Second, if we have several SQLgrey servers using the same DB, then we wil=
l=20
have several SQLgrey servers that will in turn attempt to perform DB clea=
nup=20
which is a waste of ressource...

(If we can get good performance from the cleanup SQL resquests by allowin=
g=20
those requests to properly uses indexes, then cleanup will be real fast, =
and=20
it eliminates the need to put the cleanup process in a separate program,=20
which is currently mentioned in the TODO for 1.5.x...)

> One way to do this might be to replace the now() with the "timestamp
> '<value>'" and fetch the value with a simple "SELECT now()". Leaving
> "SELECT 1" aside, this should be one of the most light query we can do

So we could perform this "SELECT now();" in the "interval" sub, instead o=
f=20
calling "time" in Perl. This sounds logicial as we then always use the DB=
's=20
time.

Cheers.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E