No index only scan on md5 index

Поиск
Список
Период
Сортировка
От Adam Brusselback
Тема No index only scan on md5 index
Дата
Msg-id CAMjNa7d7TZTu_CSvX2nS8xTJG+tvMTAA8R0L7fwMhiUWkaty2w@mail.gmail.com
обсуждение исходный текст
Ответы Re: No index only scan on md5 index
Re: No index only scan on md5 index
Список pgsql-performance
Дерево обсуждения
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;

В списке pgsql-performance по дате отправления: