BUG #5226: Limit operator slows down

On 2/12/2009 10:35 PM, aftab wrote:

The following bug has been logged online:

Bug reference: 5226
Logged by: aftab
Email address: akhangd@hotmail.co.uk
PostgreSQL version: 8.3.8
Operating system: Centos 5
Description: Limit operator slows down
Details:

S1="SELECT *
FROM position WHERE
position.POSITION_STATE_ID=2 AND
position.TARGET_ID=18
ORDER BY position.ID DESC
";
S2="SELECT *
FROM position WHERE
position.POSITION_STATE_ID=2 AND
position.TARGET_ID=18
ORDER BY position.ID DESC
LIMIT 1
";

S1 takes 0.16ms compared to S2 which takes 5 secs. Both S1 and S2 are same
except "LIMIT 1 " is added to S2.

Please read this:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

then re-post your question to the pgsql-general mailing list, including
appropriate EXPLAIN ANALYZE output for both queries, etc.

--
Craig Ringer

On Wed, Dec 2, 2009 at 11:42 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

On 2/12/2009 10:35 PM, aftab wrote:

The following bug has been logged online:

Bug reference:      5226
Logged by:          aftab
Email address:      akhangd@hotmail.co.uk
PostgreSQL version: 8.3.8
Operating system:   Centos 5
Description:        Limit operator slows down
Details:

S1="SELECT *
FROM position WHERE
position.POSITION_STATE_ID=2 AND
position.TARGET_ID=18
ORDER BY position.ID DESC
";
S2="SELECT *
FROM position WHERE
position.POSITION_STATE_ID=2 AND
position.TARGET_ID=18
ORDER BY position.ID DESC
LIMIT 1
";

S1 takes 0.16ms compared to S2 which takes 5 secs. Both S1 and S2 are same
except "LIMIT 1 " is added to S2.

Please read this:

 http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

then re-post your question to the pgsql-general mailing list, including
appropriate EXPLAIN ANALYZE output for both queries, etc.

Or pgsql-performance.

...Robert