Re: BUG #1393: Adding 'LIMIT 1' to the query halts forever

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" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Fahad G." <Fahad(dot)Gilani(at)anusf(dot)anu(dot)edu(dot)au> 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;}