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.Gilani@anusf.anu.edu.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.727rows=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)
(actualtime=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.064rows=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)
(actualtime=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/