Tom,
You're right. Here's what explain says:
hpc=> explain SELECT fetchtime, curr_walltime FROM jobstat_lc_q4_2004 WHERE
jobid = 213213 ORDER BY fetchtime DESC;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
Sort (cost=107726.01..107801.53 rows=30205 width=12)
Sort Key: fetchtime
-> Index Scan using jobstat_lc_q4_2004_jobid on jobstat_lc_q4_2004
(cost=0.00..105478.38 rows=30205 width=12)
Index Cond: (jobid = 213213)
(4 rows)
And with LIMIT 1, I get:
hpc=> explain SELECT fetchtime, curr_walltime FROM jobstat_lc_q4_2004 WHERE
jobid = 213213 ORDER BY fetchtime DESC LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Limit (cost=0.00..600.14 rows=1 width=12)
-> Index Scan Backward using jobstat_lc_q4_2004_fetchtime on
jobstat_lc_q4_2004 (cost=0.00..18127339.29 rows=30205 width=12)
Filter: (jobid = 213213)
(3 rows)
Is there some way to fix this problem? I don't see why adding LIMIT 1 should
choose the wrong index. Thanks,
Fahad
On 15/1/05 3:31 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> "Fahad G." <[EMAIL PROTECTED]> writes:
>> -- Indexes
>> CREATE INDEX jobstat_lc_q4_2004_jobid ON jobstat_lc_q4_2004 USING btree
>> (jobid);
>> CREATE INDEX jobstat_lc_q4_2004_fetchtime ON jobstat_lc_q4_2004 USING btree
>> (fetchtime);
>> CREATE UNIQUE INDEX jobstat_lc_q4_2004_walltime ON
>> unq_jobstat_lc_q4_2004_jobid_fetch USING btree (jobid, fetchtime);
>
> I bet it's choosing the wrong index. What does EXPLAIN show in each
> case?
>
> regards, tom lane
--
main(){int j=12345;char t[]=":aAbcdefFgGhijklmnNopqrsStuUvwyz \n",
*i="dUGScUiAbpmwqbmgduAvpmmlzce\nlmGGUbFbzjdb";while(*i){j+=
strchr(t,*i++)-t;j%=sizeof t-1;putchar(t[j]);}return 0;}
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
