Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6UGKnf12987 for ; Mon, 30 Jul 2001 12:20:49 -0400 (EDT) (envelope-from chrup999@yahoo.com) Received: from proxy.compucom.com (HELO Z161842) (204.214.145.2) by smtp.mail.vip.sc5.yahoo.com with SMTP; 30 Jul 2001 16:20:47 -0000 X-Apparently-From: Message-ID: <006401c11913$9a563ae0$5dd26383@corp.compucom.com> From: "Chris Ruprecht" To: "pgsql" Subject: Big table - using wrong index - why? Date: Mon, 30 Jul 2001 11:20:51 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 1 X-MSMail-Priority: High X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/380 X-Sequence-Number: 4194 Hi all, I have a table with about 6 million records in it. I have 9 different indexes on the table (different people need to access it differently) If you look at the details below, you can see that it's selecting an index which doesn't have the fields I'm searching with - and it takes for ever. There is an index which does have the right fields but it's not being used. I have done a re-index but it didn't help. How can I force it to use index i_pl_pseq instead of i_pl_loadtimestamp? Here are the details: Table "phonelog" Attribute | Type | Modifier -------------+-----------------------+-------------------------------------- - cdate | date | not null ctime | time | countrycode | integer | areacodex | integer | success | boolean | carrier | integer | duration | integer | phonenumber | character varying(20) | areacode | character varying(30) | pseq | bigint | loadno | bigint | frline | integer | entity | character varying(3) | not null loaddate | date | loadtime | time | prefix | character varying(3) | toline | integer | dur306 | double precision | dur180180 | double precision | recno | bigint | default nextval('SEQ_phonelog'::text) Indices: i_pl_carrier, i_pl_date_country_carrier, i_pl_date_line, i_pl_entity_date, i_pl_loadtimestamp, i_pl_phoneno, i_pl_prefix, i_pl_pseq, i_pl_recno phones=# \d i_pl_pseq Index "i_pl_pseq" Attribute | Type -----------+---------------------- entity | character varying(3) pseq | bigint btree phones=# explain select * from phonelog where entity = '001' and pseq >= 9120 and pseq <= 9123; NOTICE: QUERY PLAN: Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 rows=607 width=137) EXPLAIN phones=# \d i_pl_loadtimestamp Index "i_pl_loadtimestamp" Attribute | Type -----------+---------------------- entity | character varying(3) loaddate | date loadtime | time btree Best regards, Chris _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com