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.
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 |