DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:sender:to:subject:cc:mime-version:content-type
	:content-transfer-encoding:content-disposition:x-google-sender-auth;
	b=BlF3DUY/Z8ouHghpg7Z9z6Q+bz67f1R24LwYl77smEYfhmmo9mUmNfSq11GI5D+c9y
	gsEkizPTrTEIlcKfUbqYVUjRZtsalyZWleixUlXW+HllxqVjDYinz4eVC7+WE+Uu2QO8
	pNQgDN3oej9NpvWcWopeI3pnuikVO3Ibn+Trs=
Message-ID: <7d10d2df0808260937v3103e216h764bbb74ca597d6f@mail.gmail.com>
Date: Tue, 26 Aug 2008 17:37:32 +0100
From: "Frank Joerdens" <frank@joerdens.de>
To: pgsql-performance@postgresql.org
Subject: Query w empty result set with LIMIT orders of magnitude slower than
	without
Cc: Nic <nferrier@tapsellferrier.co.uk>, "Seb Potter" <seb@woome.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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