Also, for what it's worth, the same query (with both x and y conditions) takes about 30 ms with PostgreSQL and makes use of a btree index on the x and y columns. Clearly this isn't happening with MonetDB. Any ideas on how I can force this?
Best regards,
Dennis
On 29-7-2014 11:43, Dennis Pallett wrote:
Hi all,
When I run the following query the results are computed extremely fast (within 5 ms):
SELECT id_str, len,coordinates_x,coordinates_y FROM uk_neogeo_sorted
WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977
LIMIT 100;
However if I add additional conditions to the query so that it becomes the following:
SELECT id_str,len,coordinates_x,coordinates_y FROM uk_neogeo_sorted
WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977
AND coordinates_y >= 0.0 AND coordinates_y <= 61.60639636617352
LIMIT 100;
The time it takes to compute the results is approximately 1000x bigger (i.e. 5 seconds). Clearly the additional conditions on the coordinates_y column is forcing MonetDB to take a different query strategy but I don't know how I can solve this. In Postgres I would make sure there is an index on the (coordinates_x, coordinates_y) column but this doesn't seem to have any effect with MonetDB.
I've attached traces of both queries. There are approximately 11 million rows in the table. Can anyone tell me why there is such a huge difference in query execution time and how I can prevent it?
Best regards,
Dennis Pallett
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list