From: | "Chris Ruprecht" <chrup999(at)yahoo(dot)com> |
---|---|
To: | "pgsql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Big table - using wrong index - why? |
Date: | 2001-07-30 16:20:51 |
Message-ID: | 006401c11913$9a563ae0$5dd26383@corp.compucom.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2001-07-30 16:43:09 | Re: Big table - using wrong index - why? |
Previous Message | Jan Wieck | 2001-07-30 14:26:51 | Re: Re: Inserts in triggers Follow Up |