[Info-ingres] Performance of Primary Key v Index

[Darren Harvey]

Thanks Paul


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: Paul White <paul.white at shift7solutions.com.au> 
Sent: Tuesday, 30 July 2019 1:21 PM
To: Darren Harvey <Darren.Harvey at fusion5.com.au>; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] Performance of Primary Key v Index

Hi Darren,

In some situations I found creating a secondary index with required data for a particular SQL has changed the QEP with significantly reduced I/O and CPU.
Usually it is in the case of complicated multi table joins where an unwanted table scan is featured.


For example, say this SQL hits primary key only I found there is not much saving creating an index (a,b) unless the statement turns into a table scan due to an expression in the WHERE clause.

	SELECT a,b FROM mytable WHERE a=... AND b=...

A new index (a,b,c) can save disk I/O and also reduce cache activity if I reference a column not in primary key.  The savings depend on the size and width of the table and if a table scan occurs.

	SELECT a,b,c FROM mytable WHERE a=... AND b=... 
or
	SELECT a,b FROM mytable WHERE a=... AND b=... AND c=...


Paul


--
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/1WdbehBsuR/2b58emcn33vFcFNWUALbWK/0