Query w empty result set with LIMIT orders of magnitude slower than without

From: "Frank Joerdens" <frank(at)joerdens(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Cc: Nic <nferrier(at)tapsellferrier(dot)co(dot)uk>, "Seb Potter" <seb(at)woome(dot)com>
Subject: Query w empty result set with LIMIT orders of magnitude slower than without
Date: 2008-08-26 16:37:32
Message-ID: 7d10d2df0808260937v3103e216h764bbb74ca597d6f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

It seems to me that the planner makes a very poor decision with this
particular query:

--- snip ---
woome=> explain analyze SELECT "webapp_invite"."id",
"webapp_invite"."person_id", "webapp_invite"."session_id",
"webapp_invite"."created", "webapp_invite"."text",
"webapp_invite"."subject", "webapp_invite"."email",
"webapp_invite"."batch_seen", "webapp_invite"."woouser",
"webapp_invite"."accepted", "webapp_invite"."declined",
"webapp_invite"."deleted", "webapp_invite"."local_start_time" FROM
"webapp_invite" INNER JOIN "webapp_person" ON
("webapp_invite"."person_id" = "webapp_person"."id") INNER JOIN
"webapp_person" T3 ON ("webapp_invite"."person_id" = T3."id") WHERE
"webapp_person"."is_suspended" = false AND T3."is_banned" = false
AND "webapp_invite"."woouser" = 'suggus' ORDER BY
"webapp_invite"."id" DESC LIMIT 5;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3324.29 rows=5 width=44) (actual
time=2545.137..2545.137 rows=0 loops=1)
-> Nested Loop (cost=0.00..207435.61 rows=312 width=44) (actual
time=2545.135..2545.135 rows=0 loops=1)
-> Nested Loop (cost=0.00..204803.04 rows=322 width=48)
(actual time=2545.133..2545.133 rows=0 loops=1)
-> Index Scan Backward using webapp_invite_pkey on
webapp_invite (cost=0.00..201698.51 rows=382 width=44) (actual
time=2545.131..2545.131 rows=0 loops=1)
Filter: ((woouser)::text = 'suggus'::text)
-> Index Scan using webapp_person_pkey on
webapp_person t3 (cost=0.00..8.11 rows=1 width=4) (never executed)
Index Cond: (t3.id = webapp_invite.person_id)
Filter: (NOT t3.is_banned)
-> Index Scan using webapp_person_pkey on webapp_person
(cost=0.00..8.16 rows=1 width=4) (never executed)
Index Cond: (webapp_person.id = webapp_invite.person_id)
Filter: (NOT webapp_person.is_suspended)
Total runtime: 2545.284 ms
(12 rows)
--- snap ---

because if I just remove the LIMIT, it runs like the wind:

--- snip ---
woome=> explain analyze SELECT "webapp_invite"."id",
"webapp_invite"."person_id", "webapp_invite"."session_id",
"webapp_invite"."created", "webapp_invite"."text",
"webapp_invite"."subject", "webapp_invite"."email",
"webapp_invite"."batch_seen", "webapp_invite"."woouser",
"webapp_invite"."accepted", "webapp_invite"."declined",
"webapp_invite"."deleted", "webapp_invite"."local_start_time" FROM
"webapp_invite" INNER JOIN "webapp_person" ON
("webapp_invite"."person_id" = "webapp_person"."id") INNER JOIN
"webapp_person" T3 ON ("webapp_invite"."person_id" = T3."id") WHERE
"webapp_person"."is_suspended" = false AND T3."is_banned" = false
AND "webapp_invite"."woouser" = 'suggus' ORDER BY
"webapp_invite"."id" DESC;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7194.46..7195.24 rows=312 width=44) (actual
time=0.141..0.141 rows=0 loops=1)
Sort Key: webapp_invite.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=12.20..7181.53 rows=312 width=44) (actual
time=0.087..0.087 rows=0 loops=1)
-> Nested Loop (cost=12.20..4548.96 rows=322 width=48)
(actual time=0.085..0.085 rows=0 loops=1)
-> Bitmap Heap Scan on webapp_invite
(cost=12.20..1444.44 rows=382 width=44) (actual time=0.084..0.084
rows=0 loops=1)
Recheck Cond: ((woouser)::text = 'suggus'::text)
-> Bitmap Index Scan on
webapp_invite_woouser_idx (cost=0.00..12.10 rows=382 width=0) (actual
time=0.081..0.081 rows=0 loops=1)
Index Cond: ((woouser)::text = 'suggus'::text)
-> Index Scan using webapp_person_pkey on
webapp_person t3 (cost=0.00..8.11 rows=1 width=4) (never executed)
Index Cond: (t3.id = webapp_invite.person_id)
Filter: (NOT t3.is_banned)
-> Index Scan using webapp_person_pkey on webapp_person
(cost=0.00..8.16 rows=1 width=4) (never executed)
Index Cond: (webapp_person.id = webapp_invite.person_id)
Filter: (NOT webapp_person.is_suspended)
Total runtime: 0.295 ms
(16 rows)
--- snap ---

And for this particular filter, the result set is empty to boot, so
the LIMIT doesn't even do anything.

Does this behaviour make sense to anyone? Can I force the planner
somehow to be smarter about it?

Thanks!

Frank

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message henk de wit 2008-08-26 16:44:02 select on 22 GB table causes "An I/O error occured while sending to the backend." exception
Previous Message Andrew Sullivan 2008-08-26 16:21:44 Re: Autovacuum does not stay turned off