Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id EC69363775B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 04:31:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 43644-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 07:31:41 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mailrelay7-out.ornis.com (mailrelay7-out.ornis.com
 [81.93.0.90])
	by mail.postgresql.org (Postfix) with ESMTP id 2FCEF63309C
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 04:31:41 -0300 (ADT)
Received: from fsecure2.ornis.com ([192.168.162.2])
	by mailrelay7-out.ornis.com (8.12.11/8.12.11) with ESMTP id
	o6L7Vddw004525
	for <pgsql-performance@postgresql.org>; Wed, 21 Jul 2010 09:31:39 +0200
Received: from unknown (HELO relay-ext.ornis.com) ([192.168.160.6])
	by mailgate402.ornis.com with ESMTP; 21 Jul 2010 09:31:39 +0200
Received: from [192.168.0.71] ([80.79.155.178])
	by relay-ext.ornis.com (8.12.9/8.12.3) with ESMTP id o6L7VGlu021448
	for <pgsql-performance@postgresql.org>; Wed, 21 Jul 2010 09:31:27 +0200
Message-ID: <4C46A23B.2090606@acteos.com>
Date: Wed, 21 Jul 2010 10:31:07 +0300
From: Elias Ghanem <e.ghanem@acteos.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Using more tha one index per table
Content-Type: multipart/alternative;
	boundary="------------030004050601090303030405"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201007/295
X-Sequence-Number: 39684

This is a multi-part message in MIME format.
--------------030004050601090303030405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
I have a question concerning the uses of indexes in Postgresql.
I red that in PG a query can not use more than one index per table: "a 
query or data manipulation command can use at most one index per table".
Actually I found this a little weird and unfortunately I could not find 
further explanation with my Google searches. But the tests I made proved 
that this is true:

If we have table :

*create table test_index(col_1 integer, col_2 integer, col_3 integer, 
col_4 integer)*


and we have 2 indexes created on this table:

*create index idx_col_1 on test_index (col_1)*

*create index idx_col_2 on test_index (col_2)*

A query like : *select * from test_index where col_1 = 15 and col_2 = 
30* would never use both the indexes. The query plan is:

*"Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1 
width=16) (actual time=0.092..0.092 rows=0 loops=1)"*

*" Index Cond: (col_2 = 30)"*

*" Filter: (col_1 = 15)"*

*"Total runtime: 0.127 ms"*

The query will use *idx_col_2 *only and apply the other condition 
ignoring the other index(*idx_col_1*).


So please can you give some more details about this point. Is the above 
citation true or I misunderstood it?

A next step is what if a query made a join on two tables table1 and 
table2 (for ex: where table1.id = table2.id and table2.col_2 = 3 and 
table2.col_3 = 4)?
Will it use, for table2, the index of the join column (table2.id) only 
and neglect the indexes of the other two columns(col_2 and col_3) 
although they are present in the where clause.

Thanks for your response,

Elias


--------------030004050601090303030405
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=ISO-8859-1">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.2  (Win32)">
<style type="text/css">
	<!--
		@page { margin: 0.79in }
		P { margin-bottom: 0.08in }
	-->
	</style>
<p style="margin-bottom: 0in;">Hi,<br>
I have a question concerning the
uses of indexes in Postgresql.<br>
I red that in PG a query can not
use more than one index per table: "a query or data manipulation
command can use at most one index per table".<br>
Actually I
found this a little weird and unfortunately I could not find further
explanation with my Google searches. But the tests I made proved that
this is true:</p>
<p style="margin-bottom: 0in;">If we have table :</p>
<p style="margin-bottom: 0in;"><b>create table test_index(col_1
integer, col_2 integer, col_3 integer, col_4 integer)</b></p>
<br>
and we have 2 indexes created on this
table:
<p style="margin-bottom: 0in;"><b>create index idx_col_1 on test_index
(col_1)</b></p>
<p style="margin-bottom: 0in;"><b>create index idx_col_2 on test_index
(col_2)</b></p>
<p style="margin-bottom: 0in;">A query like : <b>select * from
test_index where col_1 = 15 and col_2 = 30</b> would never use both
the indexes. The query plan is:</p>
<p style="margin-bottom: 0in;"><b>"Index Scan using idx_col_2 on
test_index (cost=0.00..8.27 rows=1 width=16) (actual
time=0.092..0.092 rows=0 loops=1)"</b></p>
<p style="margin-bottom: 0in;"><b>" Index Cond: (col_2 = 30)"</b></p>
<p style="margin-bottom: 0in;"><b>" Filter: (col_1 = 15)"</b></p>
<p style="margin-bottom: 0in;"><b>"Total runtime: 0.127 ms"</b></p>
<p style="margin-bottom: 0in; font-weight: normal;">The query will use <b>idx_col_2
</b>only and apply the other condition ignoring the
other index(<b>idx_col_1</b>).</p>
<br>
So please can you
give some more details about this point. Is the above citation true
or I misunderstood it?<br>
<br>
A next step is
what if a query made a join on two tables table1 and table2 (for ex:
where table1.id = table2.id and table2.col_2 = 3 and table2.col_3 =
4)?<br>
Will it use, for
table2, the index of the join column (table2.id) only and neglect the
indexes of the other two columns(col_2 and col_3) although they are
present in the where clause.
<p style="margin-bottom: 0in; font-weight: normal;">Thanks for your
response,</p>
<p style="margin-bottom: 0in; font-weight: normal;">Elias</p>
</body>
</html>

--------------030004050601090303030405--