From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Primary Key index with Include |
Date: | 2018-11-28 20:34:52 |
Message-ID: | ce74af6f-6b6d-d4e7-7b5c-82c19c7911e6@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/28/18 11:55 AM, PegoraroF10 wrote:
> As you know version 11 gives us the possibility to include aditional columns
> to an index.
> So, if I recreate all my primary key indexes to contain a description
> column, like ...
>
> CREATE UNIQUE INDEX CONCURRENTLY Products_pk ON Products(Product_ID)
> INCLUDE(Name);
> ALTER TABLE Products ADD CONSTRAINT Products_pk PRIMARY KEY(Product_ID)
> USING INDEX Products_pk
>
> Then, when I do
> select Item_ID, Product_ID, Name from Items inner join Products
> using(Product_ID)
>
> I thought It would do a index scan only for Products table, but it does not,
> why ?
Because an index is overhead and depending on the situation the planner
may determine it is better not to use it.
Post the results of:
EXPLAIN ANALYZE select Item_ID, Product_ID, Name from Items inner join
Products using(Product_ID);
That will help understand what the planner is doing.
> What do I need do to use index scan only when searching Product_ID and Name
> on table Products ?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Withers | 2018-11-28 22:26:48 | surprising query optimisation |
Previous Message | PegoraroF10 | 2018-11-28 19:55:17 | Primary Key index with Include |