pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Ruprecht <chrup999@yahoo.com>
To: pgsql <pgsql-sql@postgresql.org>
Subject: Big table - using wrong index - why?
Date: Mon, 30 Jul 2001 11:20:51 -0500
Message-ID: <006401c11913$9a563ae0$5dd26383@corp.compucom.com> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <006401c11913$9a563ae0$5dd26383@corp.compucom.com>
Permalink:  ../006401c11913$9a563ae0$5dd26383@corp.compucom.com/
Also on:    postgresql.org/message-id/006401c11913$9a563ae0$5dd26383@corp.compucom.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: chrup999@yahoo.com
  Subject: Re: Big table - using wrong index - why?
  In-Reply-To: <006401c11913$9a563ae0$5dd26383@corp.compucom.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox