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

Поиск
Список
Период
Сортировка
От Frank Joerdens
Тема Query w empty result set with LIMIT orders of magnitude slower than without
Дата
Msg-id 7d10d2df0808260937v3103e216h764bbb74ca597d6f@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query w empty result set with LIMIT orders of magnitude slower than without  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: Autovacuum does not stay turned off
Следующее
От: henk de wit
Дата:
Сообщение: select on 22 GB table causes "An I/O error occured while sending to the backend." exception