Re: BUG #3824: Query hangs when result set empty using sort and limit - Mailing list pgsql-bugs

From Gregory Stark
Subject Re: BUG #3824: Query hangs when result set empty using sort and limit
Date
Msg-id 87prx33ntb.fsf@oxford.xeocode.com
Whole thread Raw
In response to Re: BUG #3824: Query hangs when result set empty using sort and limit  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
"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

pgsql-bugs by date:

Previous
From: Zdenek Kotala
Date:
Subject: Re: Bug (#3484) - Invalid page header again
Next
From: Gregory Stark
Date:
Subject: Re: Bug (#3484) - Invalid page header again