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;

Reply via email to