-----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-----