what's the difference between the next codes?

Question

the first code can not be executed in MonetDB:

   SELECT rc0.id as runcat, x0.id as xrtcat
    FROM extractedcatalog x0 
    INNER JOIN image i0
          ON i0.id = 1 AND i0.id = x0.image
    INNER JOIN runningcatalog rc0
                 ON rc0.zone BETWEEN x0.zone-1
                                  AND x0.zone+1
                 AND rc0.wm_decl BETWEEN x0.decl - i0.rb_smaj
                                     AND x0.decl + i0.rb_smaj
                 AND rc0.x*x0.x +rc0.y*x0.y +rc0.z*x0.z >cos(radians(i0.rb_smaj));

connection terminated!

But the following code can be executed very quickly,why????

SELECT rc0.id as runcat, x0.id as xrtcat
FROM extractedcatalog x0 
INNER JOIN image i0
      ON i0.id = 1 AND i0.id = x0.image
INNER JOIN runningcatalog rc0
             ON rc0.zone BETWEEN x0.zone-1
                              AND x0.zone+1
             AND rc0.wm_decl BETWEEN x0.decl - i0.rb_smaj
                                 AND x0.decl + i0.rb_smaj
             AND rc0.x*x0.x +rc0.y*x0.y +rc0.z*x0.z >cos(radians(0.0055));

in table image, there is only one row: id=1, rb_smaj=0.0055. Thanks very much!

Answer 1

If the only difference is the cos, save this number as a database field and put an index on it.

Answer 2

The difference is that the first query executes cos() on every row joined (that could be millions of executions depending on how many rows qualify), but the second one executes `cos() just once for the whole query, so it's much faster.

If you want the first query to execute quickly, try simply moving the condition to a WHERE clause, so that cos() is called just once per row of image:

SELECT rc0.id as runcat, x0.id as xrtcat
FROM extractedcatalog x0 
INNER JOIN image i0
      ON i0.id = 1 AND i0.id = x0.image
INNER JOIN runningcatalog rc0
    ON rc0.zone BETWEEN x0.zone-1 AND x0.zone+1
    AND rc0.wm_decl BETWEEN x0.decl - i0.rb_smaj AND x0.decl + i0.rb_smaj
WHERE rc0.x*x0.x +rc0.y*x0.y +rc0.z*x0.z > cos(radians(i0.rb_smaj))

If that doesn't work, try creating an inner query to selects the columns involved in the condition plus the cos() result, and put a where clause over that.