Received: from the_tower.pictorius.com (cpx.pictorius.com [192.219.29.90] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id PAA75132 for ; Wed, 26 Jan 2000 15:28:12 -0500 (EST) (envelope-from pbojanic@pictorius.com) Received: from bojanic [192.219.29.11] by the_tower.pictorius.com (SMTPD32-5.05) id A90127503E8; Wed, 26 Jan 2000 16:28:49 -0400 From: "Peter Bojanic" To: Subject: Help understanding how indexes are used by the query optimizer Date: Wed, 26 Jan 2000 16:28:04 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 Importance: Normal Hi, We're having difficulty understanding why PostgreSQL (6.5.3 on Red Hat Linux 6.0) won't use a primary key index. Here's the SQL statement: select users.userid, phone.phone, phone.phonetype, phone.phonenumber from users, phone where users.person = personphone.person AND personphone.phone = phone.phone Here's the phone table: +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | phone | int4 not null | 4 | | phonetype | int4 not null | 4 | | phonenumber | varchar() not null | 35 | | lastuser | varchar() | 30 | | lastmodified | datetime | 8 | | lastaction | varchar() | 30 | +----------------------------------+----------------------------------+----- --+ Here's the index for PHONE: +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | phone | int4 | 4 | +----------------------------------+----------------------------------+----- --+ Here's the output from EXPLAIN Hash Join (cost=1247.17 rows=14779 width=36) -> Seq Scan on phone (cost=668.41 rows=14770 width=20) -> Hash (cost=23.58 rows=1811 width=16) -> Nested Loop (cost=23.58 rows=1811 width=16) -> Seq Scan on users (cost=1.36 rows=11 width=8) -> Index Scan using personphone_fkey on personphone (cost=2.02 rows=1810 width=8) The question is, why will PostgreSQL not use the primary key index that is defined for the PHONE table. It appears from this output that it is doing a full-table sequential scan of 14,770 records.