Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #5226: Limit operator slows down

BUG #5226: Limit operator slows down

From
"aftab"
Date:
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.

Re: BUG #5226: Limit operator slows down

From
Craig Ringer
Date:
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

Re: BUG #5226: Limit operator slows down

From
Robert Haas
Date:
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: =A0 =A0 =A05226
>> Logged by: =A0 =A0 =A0 =A0 =A0aftab
>> Email address: =A0 =A0 =A0akhangd@hotmail.co.uk
>> PostgreSQL version: 8.3.8
>> Operating system: =A0 Centos 5
>> Description: =A0 =A0 =A0 =A0Limit operator slows down
>> Details:
>>
>> S1=3D"SELECT *
>> FROM position WHERE
>> position.POSITION_STATE_ID=3D2 AND
>> position.TARGET_ID=3D18
>> ORDER BY position.ID DESC
>> ";
>> S2=3D"SELECT *
>> FROM position WHERE
>> position.POSITION_STATE_ID=3D2 AND
>> position.TARGET_ID=3D18
>> ORDER BY position.ID DESC
>> LIMIT 1
>> ";
>>
>> S1 takes 0.16ms compared to S2 which takes 5 secs. Both S1 and S2 are sa=
me
>> except "LIMIT 1 " is added to S2.
>
> Please read this:
>
> =A0http://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