Thread: BUG #3824: Query hangs when result set empty using sort and limit

The following bug has been logged online:

Bug reference:      3824
Logged by:          Gregor Roessle
Email address:      g.roessle@osram.de
PostgreSQL version: 8.2.5
Operating system:   Linux - Kernel 2.4.33
Description:        Query hangs when result set empty using sort and limit
Details:

The Server hangs when executing a query like this:

select * from messwerte where pknr = 28315178 and isproducing = 't' order by
timestamp desc limit 1;

leaving out the "order by" and "limit" the result set is empty and returned
in 32ms from the remote installed server (i.e. very fast).

Adding the "order by" causes no trouble, also does "limit". But if i add
_both_ - "limit" and "order by" as shown above the query hangs.

I personally consider this a bug.
But maybe my query is not sophisticated enough...

"Gregor Roessle" <g.roessle@osram.de> writes:
> The Server hangs when executing a query like this:

> select * from messwerte where pknr = 28315178 and isproducing = 't' order by
> timestamp desc limit 1;

I seriously doubt that it's hung.  Takes a long time, maybe so.  Is it
trying to use an indexscan on timestamp to substitute for a sort step?

            regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> "Gregor Roessle" <g.roessle@osram.de> writes:
>> The Server hangs when executing a query like this:
>
>> select * from messwerte where pknr = 28315178 and isproducing = 't' order by
>> timestamp desc limit 1;
>
> I seriously doubt that it's hung.  Takes a long time, maybe so.  Is it
> trying to use an indexscan on timestamp to substitute for a sort step?

Surely it wouldn't make 32ms become long enough to qualify as "hung".

Unless perhaps it's otherwise using an index on pknr and/or isproducing and
with the "ORDER BY timestamp desc LIMIT 1" it's using a different large index.

I think we have to see

EXPLAIN ANALYZE select * from messwerte where pknr = 28315178 and isproducing = 't' order by timestamp;
EXPLAIN ANALYZE select * from messwerte where pknr = 28315178 and isproducing = 't' limit 1;
EXPLAIN select * from messwerte where pknr = 28315178 and isproducing = 't' order by timestamp limit 1;

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning