Hello,
With this schema:
CREATE TYPE item AS (
name text,
date date
);
CREATE TABLE item_comment (
item item,
user_id text,
comment text,
CONSTRAINT item_comment_pk PRIMARY KEY (item, user_id)
);
And this query:
EXPLAIN SELECT * FROM item_comment WHERE item = ('', '2019-07-24');
-- OK: Bitmap Index Scan
Postgresql uses the primary key index.
But with this query:
EXPLAIN SELECT * FROM item_comment WHERE (item).name = '';
-- KO: Seq Scan
Postgresql does a full table scan.
Should I inline the composite type ?
CREATE TABLE item_comment (
name text,
date date,
user_id text,
comment text,
CONSTRAINT item_comment_pk PRIMARY KEY (name, date, user_id)
);
Or is there a better way (without creating another index) ?
Thanks.