Search Postgresql Archives

Sequential scan with composite type in primary key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux