| 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: | Whole Thread | Raw Message | 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 |