[Info-ingres] Performance of Primary Key v Index

[Roy Hann]

André's short answer is correct, but there is more.

PRIMARY KEY and UNIQUE are constraints not indexes, though they require
indexes to implement them. Ingres can create the secondary
index for you, in which case performance is identical to what you
would see if you created the index explicitly.

The twist is that you can physically organize the base table using the
same primary or unique key columns and then use ALTER TABLE to declare
the PRIMARY KEY constraint uses the physical organization:

  CREATE TABLE foo
  ( orderid integer not null,
    lineitem integer not null, ... );

  MODIFY foo TO BTREE UNIQUE ON orderid, lineitem
  WITH UNIQUE_SCOPE = STATEMENT;

  ALTER TABLE foo
  ADD CONSTRAINT fookey
  PRIMARY KEY (orderid,lineitem)
  WITH (INDEX=BASE TABLE STRUCTURE);

Using the base table structure instead of a secondary index will
always perform better.

I don't want to over-sell the benefit though. In transaction
processing you won't notice any benefit unless your system is already
on its knees, but bulk queries for analytical reports or batch
processing on tables of many millions of rows will get a worthwhile
benefit.

Roy



Monday, July 29, 2019, 5:17:38 AM, you wrote:

> Short answer is yes, or better. If you’re using the primary key,
> you’re avoiding the join the index table does to get the table data you’re extracting.
>  
> (unless you added the data in the index table).
>  
>  
>  
> André Laframboise
>  
>  Conseiller Principal Base de Données, Direction générale de
> l'Innovation et du Dirigeant principal de l'information
>  Bibliothèque et Archives Canada  / Gouvernement du Canada 
>  andre.laframboise at canada.ca /  Tél. : 613-298-1346
>  
>  Senior Database Advisor, Innovation and Chief Information Officer Branch
>  Library and Archives Canada  / Government of Canada
>  andre.laframboise at canada.ca /  Tel: 613-298-1346
>  
>  
>   
>  
>  
>  
> From: info-ingres-bounces at lists.planetingres.org
> <info-ingres-bounces at lists.planetingres.org> On Behalf Of Darren Harvey
>  Sent: Sunday, July 28, 2019 7:29 PM
>  To: info-ingres at lists.planetingres.org
>  Subject: [Info-ingres] Performance of Primary Key v Index
>    
>  
>  
> Hi All
>  
>  
>  
> If I have a table that has a Primary Key with 2 columns in the key,
> does the Primary Key provide the same performance for selecting data
> on those 2 columns as an Index would with the same 2 columns?  
>  
>  
>  
> Thanks
>  
>  
>  
> Darren
>  
>  
>          
> Darren Harvey 
>      
> Senior Developer 
>      
> M +61 400 398 188
>  P +61 3 9922 5519 
>  Skype for Business ID darren.harvey at fusion5.com.au 
>      
> www.fusion5.com.au 
>      
>   
>      
> Fusion5 | Level 27, 570 Bourke Street, Melbourne, 3000, Australia
>      
> This email and any attachments are confidential and intended
> exclusively for the person to whom the email is addressed. Please see our Privacy Policy
>          
>  
>    



Roy Hann
Rational Commerce Ltd.
T +44 20 8691 2089
"Ingres development, tuning and training experts"