Обсуждение: BUG #1393: Adding 'LIMIT 1' to the query halts forever if no data present
The following bug has been logged online: Bug reference: 1393 Logged by: Fahad Email address: Fahad.Gilani@anusf.anu.edu.au PostgreSQL version: 8.0.0rc2 Operating system: Linux Description: Adding 'LIMIT 1' to the query halts forever if no data present Details: Hi, In an empty table, if I do this: "SELECT some_field WHERE some_field = 45 ORDER BY time"; This returns instantly with no results. However, doing the following takes forever to return: "SELECT some_field WHERE some_field = 45 ORDER BY time LIMIT 1"; It's a pain because my application is pretty generic and does not know beforehand whether 'some_field' with ID 45 exists or not OR whether the table has been wiped out clean. Why does it take so long for this query to return? Fahad
On Wed, Jan 12, 2005 at 11:08:55PM +0000, Fahad wrote: > PostgreSQL version: 8.0.0rc2 8.0.0rc5 is the latest -- it would be best to test with that to get the most recent bug fixes. > In an empty table, if I do this: > > "SELECT some_field WHERE some_field = 45 ORDER BY time"; ERROR: column "some_field" does not exist This isn't a valid query. > This returns instantly with no results. However, doing the following takes > forever to return: > > "SELECT some_field WHERE some_field = 45 ORDER BY time LIMIT 1"; This isn't a valid query either. Please post an actual test case -- the exact series of steps we can take to reproduce the problem. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Jan 14, 2005 at 10:47:52PM +1100, Fahad G. wrote: > I'm sorry, the query, as you would have thought, was: > > SELECT some_field FROM some_table WHERE some_field = 45 ORDER BY time LIMIT 1; Yes, I assumed the query looked like that, and I couldn't reproduce the problem with it. My point was that you haven't given us a self-contained test case that we can use to reproduce the problem, so we have to guess at what the missing parts are. Solving this would be a lot easier if you'd just tell us what you're doing so we don't have to spend unnecessary time guessing. As I requested before, please provide the exact steps we can take to reproduce the problem. Show the CREATE TABLE statement and any other statements that occur before the SELECT statement. Show everything that we can copy and paste into an empty database to make the problem happen. Since you say the query takes forever to return, it might be useful to see the output of pg_locks. Run the SELECT query that locks up in one session, then open another session and run the following query: SELECT relation::regclass, * FROM pg_locks; Include the output of that query in your message. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi Michael,
Here you go:
This is the create table statement:
CREATE TABLE jobstat_lc_q4_2004 (
jobstatid_q4_2004 serial NOT NULL,
jobid integer,
fetchtime timestamp without time zone NOT NULL,
stime timestamp without time zone,
mtime timestamp without time zone,
status character(1),
cpu_time integer,
req_jobfs bigint,
used_jobfs bigint,
req_walltime integer,
curr_walltime integer,
sys_time time without time zone,
vmemlim bigint,
used_vmem bigint,
used_mem bigint,
nodesdown text,
sys_timeint integer,
curr_cpu bigint,
curr_sys bigint
);
-- Grants
REVOKE ALL ON TABLE jobstat_lc_q3_2004 FROM PUBLIC;
GRANT SELECT ON TABLE jobstat_lc_q3_2004 TO apache;
-- 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);
When I wrote the email, I tried this on a new empty table and I was able to
reproduce it, but I can't seem to reproduce it again (I don't remember the
exact CREATE statement I used + the indexes I created). Any way, since this
is the actual 'production' table in the database, and this is where the
problem happens every time, I'll give you a bit more information about the
table. The table has around 6040854 rows:
hpc=> SELECT COUNT(*) FROM jobstat_lc_q4_2004;
count
---------
6040854
(1 row)
I'm usually doing a search on a particular 'jobid' in my application, which
has no idea whether a jobid actually exists or not (as the id is provided by
the user). Running the following query with a bogus (non-existing) jobid
gets stuck forever (or for a very very very long time):
hpc=> SELECT jobid, curr_walltime, status, fetchtime FROM jobstat_lc_q4_2004
WHERE jobid = 123123 AND curr_walltime != 0 ORDER BY fetchtime DESC LIMIT 1;
If I run the same command *without* 'LIMIT 1', it returns instantly. I've
attached the log from pg_lock, as you requested, as well when I ran the
above query. Hope something can be sorted out.
Thanks for your time.
Regards,
Fahad
On 15/1/05 5:18 AM, "Michael Fuhr" <mike@fuhr.org> wrote:
> On Fri, Jan 14, 2005 at 10:47:52PM +1100, Fahad G. wrote:
>
>> I'm sorry, the query, as you would have thought, was:
>>
>> SELECT some_field FROM some_table WHERE some_field = 45 ORDER BY time LIMIT
>> 1;
>
> Yes, I assumed the query looked like that, and I couldn't reproduce
> the problem with it. My point was that you haven't given us a
> self-contained test case that we can use to reproduce the problem,
> so we have to guess at what the missing parts are. Solving this
> would be a lot easier if you'd just tell us what you're doing so
> we don't have to spend unnecessary time guessing.
>
> As I requested before, please provide the exact steps we can take
> to reproduce the problem. Show the CREATE TABLE statement and any
> other statements that occur before the SELECT statement. Show
> everything that we can copy and paste into an empty database to
> make the problem happen.
>
> Since you say the query takes forever to return, it might be useful
> to see the output of pg_locks. Run the SELECT query that locks up
> in one session, then open another session and run the following
> query:
>
> SELECT relation::regclass, * FROM pg_locks;
>
> Include the output of that query in your message.
--
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;}
Вложения
On 15/1/05 9:55 AM, "Fahad G." <Fahad.Gilani@anusf.anu.edu.au> wrote: .... > -- Grants > REVOKE ALL ON TABLE jobstat_lc_q3_2004 FROM PUBLIC; > GRANT SELECT ON TABLE jobstat_lc_q3_2004 TO apache; ... Sorry about that, I pasted the wrong 'grants' from the log. This should be ..._q4_... Where it says q3. Also, the following 'fails' as well: hpc=> SELECT jobid, curr_walltime, status, fetchtime FROM jobstat_lc_q4_2004 WHERE jobid = 123123 ORDER BY fetchtime DESC LIMIT 1; Or hpc=> SELECT * FROM jobstat_lc_q4_2004 WHERE jobid = 123123 ORDER BY fetchtime DESC LIMIT 1; Cheers, Fahad
"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);
I bet it's choosing the wrong index. What does EXPLAIN show in each
case?
regards, tom lane
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/
On Fri, Jan 14, 2005 at 11:18:15AM -0700, Michael Fuhr wrote: > > Since you say the query takes forever to return, it might be useful > to see the output of pg_locks. Reminder to self: "takes forever to return" might mean "eventually returns, but takes a long time". Should have asked for clarification and EXPLAIN or EXPLAIN ANALYZE output. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I've simplified the test case to the following:
CREATE TABLE foo (
id integer NOT NULL,
value integer NOT NULL
);
INSERT INTO foo (id, value)
SELECT random() * 1000, random() * 1000
FROM generate_series(1, 100000);
CREATE INDEX foo_id_idx ON foo (id);
CREATE INDEX foo_value_idx ON foo (value);
VACUUM ANALYZE foo;
EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.101..0.101 rows=0 loops=1)
Sort Key: value
-> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8) (actual time=0.067..0.067 rows=0
loops=1)
Index Cond: (id = -1)
Total runtime: 0.259 ms
(5 rows)
EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..25.79 rows=1 width=8) (actual time=631.964..631.964 rows=0 loops=1)
-> Index Scan using foo_value_idx on foo (cost=0.00..2552.75 rows=99 width=8) (actual time=631.942..631.942 rows=0
loops=1)
Filter: (id = -1)
Total runtime: 632.135 ms
(4 rows)
Maybe I don't understand something about what EXPLAIN is showing,
but why does Limit have an estimated cost of 0.00..25.79 when the
thing it's limiting has a cost of 0.00..2552.75? Is that the cost
of just the limit operation? Is it supposed to be the cumulative
cost of everything up to that point? Is the planner preferring
this plan because of the 25.79 cost?
A workaround appears to be:
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM foo WHERE id = -1 ORDER BY value) AS s LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=186.46..186.48 rows=1 width=8) (actual time=0.124..0.124 rows=0 loops=1)
-> Subquery Scan s (cost=186.46..187.70 rows=99 width=8) (actual time=0.110..0.110 rows=0 loops=1)
-> Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.099..0.099 rows=0 loops=1)
Sort Key: value
-> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8) (actual time=0.064..0.064
rows=0loops=1)
Index Cond: (id = -1)
Total runtime: 0.313 ms
(7 rows)
I see that the Limit in this query has an estimated cost of
186.46..186.48, so I'm still wondering why the Limit in the previous
query had a cost of 0.00..25.79. Is that my ignorance about how
the planner works, or is it a bug?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> Limit (cost=0.00..25.79 rows=1 width=8) (actual time=631.964..631.964 rows=0 loops=1)
> -> Index Scan using foo_value_idx on foo (cost=0.00..2552.75 rows=99 width=8) (actual time=631.942..631.942
rows=0loops=1)
> Filter: (id = -1)
> Total runtime: 632.135 ms
> (4 rows)
> Maybe I don't understand something about what EXPLAIN is showing,
> but why does Limit have an estimated cost of 0.00..25.79 when the
> thing it's limiting has a cost of 0.00..2552.75?
This represents the planner assuming that the indexscan will only need
to be run 1/99th of the way to completion. That is, having estimated
that there were 99 matching rows to be found, it assumes those are
uniformly distributed in the index-by-value, and that the scan can stop
as soon as the first one is found.
Since in reality there aren't *any* matching rows, the index scan has to
go all the way to the end :-(. Even if there were matching rows, they
might be much further out in the index order than the
uniform-distribution hypothesis predicts, because the id and value
columns might have been correlated.
Basically, what you're looking at here is that the planner is thinking
it should go for a fast-start plan in a scenario where that bet loses.
It's still a good bet though. I'm not sure how to formulate the notion
that there's too much risk of a slow result in this scenario.
regards, tom lane
On Sun, Jan 16, 2005 at 02:56:11PM -0500, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > > Maybe I don't understand something about what EXPLAIN is showing, > > but why does Limit have an estimated cost of 0.00..25.79 when the > > thing it's limiting has a cost of 0.00..2552.75? > > This represents the planner assuming that the indexscan will only need > to be run 1/99th of the way to completion. Thanks -- I understood the rationale for considering a scan on this index but not why that plan was preferred. Your explanation provides the piece I was missing. > Basically, what you're looking at here is that the planner is thinking > it should go for a fast-start plan in a scenario where that bet loses. > It's still a good bet though. I'm not sure how to formulate the notion > that there's too much risk of a slow result in this scenario. Would it be accurate to say that the planner makes the bet most likely to win without regard to how badly it might lose? Is taking the downside into consideration a tough problem to solve, or is it simply not worthwhile in the large? Thanks again. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> Would it be accurate to say that the planner makes the bet most
> likely to win without regard to how badly it might lose?
Yes, I think that's a fair summary.
> Is taking the downside into consideration a tough problem to solve, or
> is it simply not worthwhile in the large?
I don't know how to solve it, and whether it would be worthwhile would
depend considerably on how expensive the proposed solution is ...
regards, tom lane
On Sun, Jan 16, 2005 at 04:08:35PM -0500, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > > Is taking the downside into consideration a tough problem to solve, or > > is it simply not worthwhile in the large? > > I don't know how to solve it, and whether it would be worthwhile would > depend considerably on how expensive the proposed solution is ... Would the topic merit discussion in pgsql-hackers after the dust from the 8.0 release settles down? I know little of the theory behind query planning; I'd hate to waste the developers' time on a topic that's already been debated or that has little merit. If the topic is worthwhile, then I was thinking of a configuration setting that would allow the user to request either "the plan most likely to be the fastest" or "the plan least likely to be the slowest," or maybe something in between. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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@sss.pgh.pa.us> 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);
>
> 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;}
Michael,
On 16/1/05 12:48 AM, "Michael Fuhr" <mike@fuhr.org> wrote:
> 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'm sorry. My mistake again while copying indexes from the log. The index
is:
CREATE UNIQUE INDEX unq_jobstat_lc_q4_2004_jobid_fetch ON jobstat_lc_q4_2004
USING btree( jobid, fetchtime);
So basically the unique index is on the same table.
Regards,
Fahad
--
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;}