Skip site navigation (1) Skip section navigation (2)

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

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Fahad G(dot)" <Fahad(dot)Gilani(at)anusf(dot)anu(dot)edu(dot)au>,pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1393: Adding 'LIMIT 1' to the query halts forever
Date: 2005-01-15 13:48:13
Message-ID: 20050115134813.GB77855@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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/

In response to

Responses

pgsql-bugs by date

Next:From: PhilDate: 2005-01-15 14:01:52
Subject: BUG #1403: Failed to create process: 2
Previous:From: raxfarDate: 2005-01-15 13:13:55
Subject: BUG #1402: getPrecision don't work

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group