I am not sure if this is a bug or another problem …
CREATE TABLE georg.texttest (textfield TEXT); -- INSERT 5 million textvalues (100 distinct); INSERT INTO georg.texttest ("textfield") values ('VALUE00'),('VALUE01'), ... , ... ,('VALUE98'),('VALUE99');
Selecting (100 out of 5 million) distinct values takes less than a second
SELECT DISTINCT textfield FROM georg.texttest;
However inserting does distinct values in a new table, takes much longer (25 sec) …
-- Create a 2nd table CREATE TABLE georg.texttestdistinct (textfield TEXT); -- Insert distinct values into new table INSERT INTO georg.texttestdistinct (textfield) SELECT distinct textfield from georg.texttest;
With bigger data sets (several 100 million rows), the distinct select is still done in a few seconds, but inserting those 50 values won’t even finish after 10 minutes.