Hey all,
I have an attachment table in my database which stores a file in a bytea column, the file name, and the size of the file.
Schema:
CREATE TABLE attachment
(
attachment_id uuid NOT NULL DEFAULT gen_random_uuid(),
attachment_name character varying NOT NULL,
attachment_bytes_size integer NOT NULL,
attachment_bytes bytea NOT NULL,
CONSTRAINT attachment_pkey PRIMARY KEY (attachment_id)
);
I do lookups on this table based on the md5 of the attachment_bytes column, so I added an index:
CREATE INDEX idx_attachment_bytes_md5 ON attachment ((md5(attachment_bytes)::uuid));
Queries like this are sped up by the index no problem:
SELECT attachment_id
FROM attachment
WHERE md5(attachment_bytes)::uuid = 'b2ab855ece13a72a398096dfb6c832aa';
But if I wanted to return the md5 value, it seems to be totally unable to use an index only scan:
SELECT md5(attachment_bytes)::uuid
FROM attachment;