Sequential scan with composite type in primary key - Mailing list pgsql-general

From gtreguier@free.fr
Subject Sequential scan with composite type in primary key
Date
Msg-id 494986266.-1717350093.1563987893717.JavaMail.root@zimbra61-e11.priv.proxad.net
Whole thread Raw
List 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.



pgsql-general by date:

Previous
From: Souvik Bhattacherjee
Date:
Subject: Running concurrent txns and measuring the timings in Postgres
Next
From: Adrian Klaver
Date:
Subject: Re: Running concurrent txns and measuring the timings in Postgres