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
