[Info-ingres] Performance of Primary Key v Index

[Paul White]

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