Re: No index only scan on md5 index

On Wednesday, November 25, 2015, Adam Brusselback <adambrusselback@gmail.com> wrote:

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;

Ok.

Any reason not to add the uuid column to the table?

AFAIK The system is designed to return data from the heap, not an index.  While it possibly can in some instances if you need to return data you should store it directly in the table.

David J.