[Info-ingres] Performance of Primary Key v Index

[Darren Harvey]

Thanks Roy.

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  
 
-----Original Message-----
From: Roy Hann <roy.hann at rationalcommerce.com> 
Sent: Monday, 29 July 2019 5:53 PM
To: Laframboise, André (BAC/LAC) <andre.laframboise at canada.ca>; Darren Harvey <Darren.Harvey at fusion5.com.au>; info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Performance of Primary Key v Index

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"

--
Message  protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.http://www.mailguard.com.au/mg
Click here to report this message as spam:
https://console.mailguard.com.au/ras/1WcQBYApyh/6oqr94AhSCWEYoK9XKbbqx/0