[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Primary Key index with Include
From:       Adrian Klaver <adrian.klaver () aklaver ! com>
Date:       2018-11-28 20:34:52
Message-ID: ce74af6f-6b6d-d4e7-7b5c-82c19c7911e6 () aklaver ! com
[Download RAW message or body]

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.klaver@aklaver.com

[prev in list] [next in list] [prev in thread] [next in thread]