Hi
If you look at the trace you'll notice that the intermediate is half your database size; Both expressions should have been turned into a simple subselect. It might be an SQL optimizer issue.
FYI the latest multidimensional table we played with had 640B x,y,z colum.
regards, Martin
On 07/30/2014 09:32 AM, Dennis Pallett wrote:
Hi Martin,
Thank you for your reply.
I have attached a trace of a hot run of a query against my original table, which is *not* sorted on x or y (but on an unrelated column). All I'm really trying to achieve is a multi-dimensional range-search, currently in two dimensions (x,y) and in the future in more dimensions (e.g. x, y and time). Is this something that MonetDB is just not suitable for?
Best regards, Dennis
On 29-7-2014 16:39, Martin Kersten wrote:
Hi Dennis
If you compare the two traces you will notice that the fast one exploits the fact that the column is sorted on x. No further actions are required, so query is done.
In the second query, although it is fast to access the x-candidates, that is not necessarily true for the corresponding y-candidates. To make it fast, first question to answer is you are running hot/cold queries. MonetDB may decide to build a hash index on y, the first time you use it in your session.
So, what are the traces of a cold/hot run?
If you use a b-tree with compound (x,y) key in Postgres you benefit from the multidimensional sort.
regards, Martin
On 07/29/2014 11:43 AM, 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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list