query plan wierdness? - Mailing list pgsql-performance

Can someone explain what I'm missing here?   This query does what I
expect--it uses the "foo" index on the openeddatetime, callstatus,
calltype, callkey fields:

elon2=# explain analyse select * from call where aspid='123C' and
OpenedDateTime between '2000-01-01 00:00:00.0' and '2004-06-24
23:59:59.999' order by openeddatetime desc, callstatus desc, calltype
desc, callkey desc limit 26;

QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------------
 Limit  (cost=0.00..103.76 rows=26 width=297) (actual time=0.07..0.58
rows=26 loops=1)
   ->  Index Scan Backward using foo on call  (cost=0.00..1882805.77
rows=471781 width=297) (actual time=0.06..0.54 rows=27 loops=1)
         Index Cond: ((openeddatetime >= '2000-01-01
00:00:00-07'::timestamp with time zone) AND (openeddatetime <=
'2004-06-24 23:59:59.999-07'::timestamp with time zone))
         Filter: (aspid = '123C'::bpchar)
 Total runtime: 0.66 msec
(5 rows)


However, this query performs a sequence scan on the table, ignoring the
call_idx13 index (the only difference is the addition of the aspid field
in the order by clause):

elon2=# explain analyse select * from call where aspid='123C' and
OpenedDateTime between '2000-01-01 00:00:00.0' and '2004-06-24
23:59:59.999' order by aspid, openeddatetime desc, callstatus desc,
calltype desc, callkey desc limit 26;

QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------
 Limit  (cost=349379.41..349379.48 rows=26 width=297) (actual
time=32943.52..32943.61 rows=26 loops=1)
   ->  Sort  (cost=349379.41..350558.87 rows=471781 width=297) (actual
time=32943.52..32943.56 rows=27 loops=1)
         Sort Key: aspid, openeddatetime, callstatus, calltype, callkey
         ->  Seq Scan on call  (cost=0.00..31019.36 rows=471781
width=297) (actual time=1.81..7318.13 rows=461973 loops=1)
               Filter: ((aspid = '123C'::bpchar) AND (openeddatetime >=
'2000-01-01 00:00:00-07'::timestamp with time zone) AND (openeddatetime
<= '2004-06-24 23:59:59.999-07'::timestamp with time zone))
 Total runtime: 39353.86 msec
(6 rows)


Here's the structure of the table in question:


                   Table "public.call"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 aspid            | character(4)             |
 lastmodifiedtime | timestamp with time zone |
 moduser          | character(13)            |
 callkey          | character(13)            |
 calltype         | text                     |
 callqueueid      | text                     |
 openeddatetime   | timestamp with time zone |
 assigneddatetime | timestamp with time zone |
 closeddatetime   | timestamp with time zone |
 reopeneddatetime | timestamp with time zone |
 openedby         | text                     |
 callstatus       | character(1)             |
 callpriority     | text                     |
 callreasontext   | text                     |
 keyword1         | text                     |
 keyword2         | text                     |
 callername       | text                     |
 custfirstname    | text                     |
 custlastname     | text                     |
 custssntin       | character(9)             |
custssnseq       | text                     |
 custdbccode      | character(9)             |
 custlongname     | text                     |
 custtypecode     | character(2)             |
 custphone        | text                     |
 custid           | character(9)             |
 assigneduserid   | character varying(30)    |
 historyitemcount | integer                  |
 callertype       | text                     |
 callerphoneext   | text                     |
 followupdate     | text                     |
 hpjobnumber      | character(11)            |
Indexes: call_idx1 unique btree (aspid, callkey),
         call_aspid btree (aspid),
         call_aspid_opendedatetime btree (aspid, openeddatetime),
         call_idx10 btree (aspid, keyword1, openeddatetime, callstatus,
calltype
, custtypecode, custid, callkey),
         call_idx11 btree (aspid, keyword2, openeddatetime, callstatus,
calltype
, custtypecode, custid, callkey),
         call_idx12 btree (aspid, custtypecode, custid, openeddatetime,
callstat
us, calltype, callkey),
         call_idx13 btree (aspid, openeddatetime, callstatus, calltype,
callkey),
         call_idx14 btree (aspid, callqueueid, callstatus, callkey),
         call_idx2 btree (aspid, callqueueid, openeddatetime,
custtypecode, call
status, callkey),
         call_idx3 btree (aspid, assigneduserid, openeddatetime,
custtypecode, c
allstatus, callkey),
         call_idx4 btree (aspid, custid, custtypecode, callkey,
callstatus),
         call_idx7 btree (aspid, calltype, custtypecode, custid,
callstatus, cal
lkey),
         call_idx9 btree (aspid, assigneduserid, callstatus,
followupdate),
         foo btree (openeddatetime, callstatus, calltype, callkey)




TIA,

-Joel

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.