Thank you for your fast reply. Unfortunately the IN(...) variant is not any faster. I used EXPLAIN to inspect the query and that returns hundreds of lines of the following: | X_1137 := bat.mergecand(X_1133,X_1135); | X_1139 := algebra.subselect(X_297,A198,A198,true,true,false); | X_1141 := bat.mergecand(X_1137,X_1139); | X_1143 := algebra.subselect(X_297,A199,A199,true,true,false); Is this an indication that for each equality test the database engine is doing some kind of subselect? Best regards, Dennis On 15-7-2014 14:26, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On 2014-07-15 14:13, Dennis Pallett wrote:
Hi all,
I'm executing a very large query with MonetDB and it takes very long before the result is calculated (e.g. several minutes) whereas PostgreSQL does it in less than 100 ms.
The query itself is a very simple select + sum query but the WHERE condition has more than a thousand equality tests on the primary key (ckey) of the table. It basically looks like this:
SELECT sum(countAggr) FROM sys.london_hav_neogeo_pa WHERE ckey=16947611436 OR ckey=16947611437 OR ckey=16947611438 OR ckey=16947611439 OR ckey=16947611440 OR ckey=16947617908 OR ckey=16947617909 OR ckey=16947617910 OR ckey=16947617911 OR ckey=16947617912 [.. several hundreds of lines removed ..] OR ckey=289411309830 OR ckey=289411309831 OR ckey=289411309832 OR ckey=289411309833 OR ckey=289411309834 OR ckey=289411309835 OR ckey=289411309836 OR ckey=289411309837;
Does anyone know why it's taking so long with MonetDB and if there is a way to significantly improve performance?
Best regards, Dennis I don't know why it is so slow, but you can check using EXPLAIN and TRACE (prepend one or the other to your SQL query) what's going on.
Is it any faster when you use: SELECT sum(countAggr) FROM sys.london_hav_neogeo_pa WHERE ckey in (16947611436, 16947611437, 16947611438, 16947611439, 16947611440, ...);
- -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQCVAwUBU8Ud3T7g04AjvIQpAQLP/AP+MfiYffOeyNXcydj9CDOaZ9ocRnnoIxbm 2+dvK3aoIBjo10u/BXkWAfhhSO9/hxrVADDZn1uccU2lGpzDa1a+f9qxt1EJSIzc MCMXpWr0IRhU1w3wrr2zfBLkcFcSYnqEXMkMq7PqYo/iC+UeAEcSdjjWpE3GrhZO 9tHHfklL81E= =eVlM -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list