SELECT very slow when BLOB(2G)

[Grégoire Dubois]

Hi all,

Here is the select I do on the following table. If "file" is a BLOB(2G), the request is very very slow (30-60s), even if there is only one line for the table. But if I replace BLOB(2G) by BLOB(5M) or BLOB(1G), the request becomes very fast.
Is there a reason ? Is there a workaround ?

Thank you.
Best regards.



SELECT DISTINCT db_file.ID,db_file.name,db_file.reference,db_file.hash
FROM db_file
ORDER BY db_file.name



CREATE TABLE db_file (
                                       ID            INT    GENERATED ALWAYS AS IDENTITY (START WITH 1,INCREMENT BY 1),
                                       file          BLOB   ( 2G)  NOT NULL,
                                       name          VARCHAR(256) NOT NULL,
                                       hash          VARCHAR( 40) NOT NULL,
                                       size          INT          NOT NULL,
                                       reference     VARCHAR( 32) NOT NULL,
                                       PRIMARY KEY (ID));
CREATE        INDEX db_file_name_index      ON db_file (name)"
CREATE UNIQUE INDEX db_file_hash_index      ON db_file (hash)"
CREATE        INDEX db_file_reference_index ON db_file (reference)"