Sequential scan with composite type in primary key

From: gtreguier(at)free(dot)fr
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Sequential scan with composite type in primary key
Date: 2019-07-24 17:04:53
Message-ID: 494986266.-1717350093.1563987893717.JavaMail.root@zimbra61-e11.priv.proxad.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-07-24 17:58:27 Re: Running concurrent txns and measuring the timings in Postgres
Previous Message Souvik Bhattacherjee 2019-07-24 16:54:00 Running concurrent txns and measuring the timings in Postgres