pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery w empty result set with LIMIT orders of magnitude slower than without
4+ messages / 2 participants
[nested] [flat]
* Query w empty result set with LIMIT orders of magnitude slower than without
@ 2008-08-26 16:37 Frank Joerdens <frank@joerdens.de>
0 siblings, 1 reply; 4+ messages in thread
From: Frank Joerdens @ 2008-08-26 16:37 UTC (permalink / raw)
To: pgsql-performance; +Cc: Nic <nferrier@tapsellferrier.co.uk>; Seb Potter <seb@woome.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query w empty result set with LIMIT orders of magnitude slower than without
@ 2008-08-26 16:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Frank Joerdens <frank@joerdens.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-08-26 16:53 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-performance@postgresql.org, Nic <nferrier@tapsellferrier.co.uk>; Seb Potter <seb@woome.com>
"Frank Joerdens" <frank@joerdens.de> writes:
> It seems to me that the planner makes a very poor decision with this
> particular query:
Try increasing the stats on woouser. You need it to make a smaller
estimate of the number of matching rows here:
> -> 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)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query w empty result set with LIMIT orders of magnitude slower than without (SOLVED, pls disregard)
@ 2008-08-26 16:59 Frank Joerdens <frank@joerdens.de>
0 siblings, 0 replies; 4+ messages in thread
From: Frank Joerdens @ 2008-08-26 16:59 UTC (permalink / raw)
To: pgsql-performance; +Cc: Nic <nferrier@tapsellferrier.co.uk>; Seb Potter <seb@woome.com>
Eh, there was a spurious join in that query which was created by an
ORM which messed things up apparently. Sorry for the noise. This
abstracted version of the original query that does the same is fast:
woome=> EXPLAIN ANALYZE
SELECT *
FROM webapp_invite i
INNER JOIN webapp_person p ON (i.id = p.id)
WHERE p.is_suspended = false
AND p.is_banned = false
AND i.woouser = 'suggus'
ORDER BY i.id DESC LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4549.51..4549.52 rows=5 width=238) (actual
time=0.071..0.071 rows=0 loops=1)
-> Sort (cost=4549.51..4549.58 rows=31 width=238) (actual
time=0.070..0.070 rows=0 loops=1)
Sort Key: i.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=12.20..4548.99 rows=31 width=238)
(actual time=0.036..0.036 rows=0 loops=1)
-> Bitmap Heap Scan on webapp_invite i
(cost=12.20..1444.45 rows=382 width=44) (actual time=0.034..0.034
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.032..0.032 rows=0 loops=1)
Index Cond: ((woouser)::text = 'suggus'::text)
-> Index Scan using webapp_person_pkey on
webapp_person p (cost=0.00..8.11 rows=1 width=194) (never executed)
Index Cond: (p.id = i.id)
Filter: ((NOT p.is_suspended) AND (NOT p.is_banned))
Total runtime: 0.183 ms
(13 rows)
Time: 1.114 ms
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query w empty result set with LIMIT orders of magnitude slower than without
@ 2008-08-26 18:41 Frank Joerdens <frank@joerdens.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Frank Joerdens @ 2008-08-26 18:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@postgresql.org, Nic <nferrier@tapsellferrier.co.uk>; Seb Potter <seb@woome.com>
On Tue, Aug 26, 2008 at 5:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Frank Joerdens" <frank@joerdens.de> writes:
>> It seems to me that the planner makes a very poor decision with this
>> particular query:
>
> Try increasing the stats on woouser. You need it to make a smaller
> estimate of the number of matching rows here:
Way cool, this
alter table webapp_invite alter column woouser set statistics 50;
did the trick, without cleaning up the spurious join there even.
Thanks!
Frank
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-08-26 18:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-26 16:37 Query w empty result set with LIMIT orders of magnitude slower than without Frank Joerdens <frank@joerdens.de>
2008-08-26 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-26 18:41 ` Frank Joerdens <frank@joerdens.de>
2008-08-26 16:59 Re: Query w empty result set with LIMIT orders of magnitude slower than without (SOLVED, pls disregard) Frank Joerdens <frank@joerdens.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox