Keep in mind, that LIMIT (and OFFSET, SAMPLE, etc) are pagination operators. That is, they are used to define the presentation of a result of a query and not to alter the evaluation. Therefore, although some DBMS architectures make it easy to take advantage of such operators to reduce computation, it is not "correct" to consider a query fast with limit and slow without a limit. The query is what it is. If you need to make queries that will run faster then for example you will have to increase the selectivity, i.e., the SELECT operator is what is part of the query evaluation and not part of the presentation of the result.On Thu, Jul 31, 2014 at 2:08 PM, Dennis Pallett <dennis@pallett.nl> wrote:
Hi Lefteris,
Thank you for your fast reply. At least that explains why my query is still quite slow, since MonetDB is probably joining millions of records, even though I only want 100. Not sure how I'm going to solve that for now but at least I know exactly why it is so slow.
Best regards,
Dennis
On 31-7-2014 11:25, Lefteris wrote:
Hi Dennis,
MonetDB will first compute the entire result and then will print only the first 100. This is because MonetDB execution model is not tuple-at-a-time with a pipeline of operators, instead each operator will consume the entire input before giving the result to the next operator. Therefore, you can not possibly know how many values to select on the first column to produce exactly 100 results after a join for example, becuase you dont know how many values will actually join with the next column.
Hope this helps.
Lefteris
On Thu, Jul 31, 2014 at 10:47 AM, Dennis Pallett <dennis@pallett.nl> wrote:
Hi all,
Just wanted to provide an update to this thread.
With help from Martin Kersten and one of his colleagues at CWI a fix has been added to the stable branch of MonetDB which has resulted in a better optimized query plan for my multi-range query. This has indeed improved the performance of my query somewhat but not as much as I would've liked.
Which leads me to believe that perhaps MonetDB is not applying the LIMIT clause as expected. The range predicates of my query cover approximately 4+ million rows (about 1/3 of my total database) but I'm only interested in the first 100 rows, hence the LIMIT clause. Is it possible that MonetDB is first computing the full result set (i.e. 4+ million rows) and then only returning 100 rows?
I've once again attached a trace of my (optimized) query.
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
_______________________________________________ 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