The following bug has been logged online:

Bug reference: 5230
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.

Query Plan for S1:

QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------
Sort (cost=209341.40..209767.55 rows=170457 width=297) (actual
time=0.200..0.200 rows=1 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on "position" (cost=5050.22..146754.50 rows=170457
width=297) (actual time=0.188..0.189 rows=1 loops=1)
Recheck Cond: (target_id = 18)
Filter: (position_state_id = 2)
-> Bitmap Index Scan on position_target_fk (cost=0.00..5007.61
rows=170912 width=0) (actual time=0.134..0.134 rows=2 loops=1)
Index Cond: (target_id = 18)
Total runtime: 0.275 ms
(9 rows)

Query Plan for S2:

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---
Limit (cost=0.00..2.43 rows=1 width=297) (actual time=3053.220..3053.221
rows=1 loops=1)
-> Index Scan Backward using position_pkey on "position"
(cost=0.00..413713.28 rows=170459 width=297) (actual time=3053.216..3053.216
rows=1 loops=1)
Filter: ((position_state_id = 2) AND (target_id = 18))
Total runtime: 3053.297 ms
(4 rows)

Search Discussions

  • Robert Haas at Dec 3, 2009 at 3:08 pm

    On Thu, Dec 3, 2009 at 7:07 AM, aftab wrote:
    The following bug has been logged online:

    Bug reference:      5230
    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.

    Query Plan for S1:

    QUERY PLAN
    ----------------------------------------------------------------------------
    -------------------------------------------------------------
    Sort  (cost=209341.40..209767.55 rows=170457 width=297) (actual
    time=0.200..0.200 rows=1 loops=1)
    Sort Key: id
    Sort Method:  quicksort  Memory: 25kB
    ->  Bitmap Heap Scan on "position"  (cost=5050.22..146754.50 rows=170457
    width=297) (actual time=0.188..0.189 rows=1 loops=1)
    Recheck Cond: (target_id = 18)
    Filter: (position_state_id = 2)
    ->  Bitmap Index Scan on position_target_fk  (cost=0.00..5007.61
    rows=170912 width=0) (actual time=0.134..0.134 rows=2 loops=1)
    Index Cond: (target_id = 18)
    Total runtime: 0.275 ms
    (9 rows)

    Query Plan for S2:

    QUERY PLAN
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ---
    Limit  (cost=0.00..2.43 rows=1 width=297) (actual time=3053.220..3053.221
    rows=1 loops=1)
    ->  Index Scan Backward using position_pkey on "position"
    (cost=0.00..413713.28 rows=170459 width=297) (actual time=3053.216..3053.216
    rows=1 loops=1)
    Filter: ((position_state_id = 2) AND (target_id = 18))
    Total runtime: 3053.297 ms
    (4 rows)
    This is the same thing you posted yesterday....

    ...Robert
  • Robert Haas at Dec 4, 2009 at 2:01 am

    On Thu, Dec 3, 2009 at 10:45 AM, aftab khan wrote:
    This is not exactly the same what I posted yesterday, What I have posted
    today also include EXPLAIN ANALYZE OUTPUT .
    [ adding the list back to the CC line ]

    It looks like you have a problem very similar to one Laurence Laborde
    was complaining about recently on pgsql-performance. My discussion of
    the problem is here:

    http://archives.postgresql.org/pgsql-performance/2009-12/msg00022.php

    In your case, the reason why the estimates are off is because of the
    lack of multi-column statistics, rather than because of the choice of
    operator, but the issues are similar.

    ...Robert

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedDec 3, '09 at 12:07p
activeDec 4, '09 at 2:01a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Robert Haas: 2 posts Aftab: 1 post

People

Translate

site design / logo © 2022 Grokbase