From: Michel B. <mi...@bo...> - 2005-02-19 10:43:36
|
Le Samedi 19 F=E9vrier 2005 10:13, Michel Bouissou a =E9crit : > > It seems that what prevents the index from being used, is having the "n= ow() > - INTERVAL '65 DAY'" calculation performed as an expression in the WHER= E > clause comparison. > > So I wonder if it wouldn't be better to have SQLgrey calculate in Perl = the > wanted date boundary, then build an SQL query using this calculated > boundary rather than asking the SQL engine to calculate it itself on th= e > fly... > > Furthemore, it could help with different SQL DB systems using different > INTERVAL syntaxes, as SQLgrey already needs to have an interval() > subroutine adaptating the query syntax to each kind of SQL DB system. > > Probably, having this routine calculate the date boundary by itself, an= d > spit a result that would be used as a constant in the queries would mak= e a > more universal solution, AND it would allow PostgreSQL to use indexes f= or > performing this query, rather to have to process the DB sequentially... I've written a patch (attached) that I'm currently testing. It seems to w= ork=20 OK in PostgreSQL so far. I don't know if it would be OK for other SQL DB systems as well, but I su= ppose=20 that probably yes... Comments welcome. --=20 Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E |