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

On Fri, Jan 14, 2005 at 11:31:05PM -0500, Tom Lane 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);

The last index is created on a different table -- should it be
created on the table we're working with? And if so, are the columns
(jobid, fetchtime) correct? The index name suggests otherwise.

> I bet it's choosing the wrong index. What does EXPLAIN show in each
> case?

I created the table and the two indexes (the third is on a different
table; creating it on this table didn't change anything), populated
the table with random data, and ANALYZEd it. Below are several
tests run on 8.0.0rc5; notice how case 4 is much slower than the
others. My random data probably doesn't have the same distribution
as Fahad's, but I appear to have duplicated the problem.

Case 1: jobid exists, no LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 500 AND curr_walltime != 0 ORDER BY fetchtime;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=189.80..190.05 rows=98 width=149) (actual time=2.768..3.189 rows=94 loops=1)
Sort Key: fetchtime
-> Index Scan using jobstat_lc_q4_2004_jobid on jobstat_lc_q4_2004 (cost=0.00..186.56 rows=98 width=149) (actual time=0.099..1.727 rows=94 loops=1)
Index Cond: (jobid = 500)
Filter: (curr_walltime <> 0)
Total runtime: 3.851 ms
(6 rows)

Case 2: jobid exists, LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 500 AND curr_walltime != 0 ORDER BY fetchtime LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..33.22 rows=1 width=149) (actual time=6.659..6.664 rows=1 loops=1)
-> Index Scan using jobstat_lc_q4_2004_fetchtime on jobstat_lc_q4_2004 (cost=0.00..3255.97 rows=98 width=149) (actual time=6.644..6.644 rows=1 loops=1)
Filter: ((jobid = 500) AND (curr_walltime <> 0))
Total runtime: 6.900 ms
(4 rows)

Case 3: jobid doesn't exist, no LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 9999 AND curr_walltime != 0 ORDER BY fetchtime;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=189.80..190.05 rows=98 width=149) (actual time=0.103..0.103 rows=0 loops=1)
Sort Key: fetchtime
-> Index Scan using jobstat_lc_q4_2004_jobid on jobstat_lc_q4_2004 (cost=0.00..186.56 rows=98 width=149) (actual time=0.064..0.064 rows=0 loops=1)
Index Cond: (jobid = 9999)
Filter: (curr_walltime <> 0)
Total runtime: 0.325 ms
(6 rows)

Case 4: jobid doesn't exist, LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 9999 AND curr_walltime != 0 ORDER BY fetchtime LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..33.22 rows=1 width=149) (actual time=684.957..684.957 rows=0 loops=1)
-> Index Scan using jobstat_lc_q4_2004_fetchtime on jobstat_lc_q4_2004 (cost=0.00..3255.97 rows=98 width=149) (actual time=684.937..684.937 rows=0 loops=1)
Filter: ((jobid = 9999) AND (curr_walltime <> 0))
Total runtime: 685.197 ms
(4 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/