Re: indexes across joins not used for count

Поиск
Список
Период
Сортировка
От Jeremy Wells
Тема Re: indexes across joins not used for count
Дата
Msg-id 5082FD8F.9070504@gmail.com
обсуждение исходный текст
Ответ на Re: indexes across joins not used for count  (Jeff Davis <pgsql@j-davis.com>)
Ответы Re: indexes across joins not used for count  (Jeff Davis <pgsql@j-davis.com>)
Список pgsql-novice
Дерево обсуждения
Jeff Davis wrote:
Can you post the output of EXPLAIN ANALYZE? Did you do an ANALYZE of the
tables already?
EXPLAIN ANALYZE SELECT COUNT(*) FROM "invites" INNER JOIN "sms_requests" ON "sms_requests"."invoker_id" = "invites"."id" AND "sms_requests"."invoker_type" = 'Invite' INNER JOIN "sms_messages" ON "sms_messages"."id" = "sms_requests"."sms_message_id" WHERE "sms_messages"."sent_at" IS NOT NULL;

                                                                   QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=129556.14..129556.14 rows=1 width=0) (actual time=21530.146..21530.147 rows=1 loops=1)
   ->  Hash Join  (cost=81261.64..128882.20 rows=1347872 width=0) (actual time=6961.957..20437.245 rows=1340639 loops=1)
         Hash Cond: (sms_requests.sms_message_id = sms_messages.id)
         ->  Hash Join  (cost=22033.10..62240.37 rows=1347872 width=4) (actual time=3618.615..11410.322 rows=1340639 loops=1)
               Hash Cond: (invites.id = sms_requests.invoker_id)
               ->  Seq Scan on invites  (cost=0.00..33216.78 rows=1683927 width=4) (actual time=0.009..1983.440 rows=1683927 loops=1)
               ->  Hash  (cost=17315.55..17315.55 rows=1347872 width=8) (actual time=3617.847..3617.847 rows=1347872 loops=1)
                     Buckets: 262144  Batches: 1  Memory Usage: 52652kB
                     ->  Seq Scan on sms_requests  (cost=0.00..17315.55 rows=1347872 width=8) (actual time=0.027..1651.786 rows=1347872 loops=1)
                           Filter: ((invoker_type)::text = 'Invite'::text)
         ->  Hash  (cost=55408.40..55408.40 rows=1091467 width=4) (actual time=3342.029..3342.029 rows=1107628 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 38941kB
               ->  Seq Scan on sms_messages  (cost=0.00..55408.40 rows=1091467 width=4) (actual time=0.009..1737.765 rows=1107628 loops=1)
                     Filter: (sent_at IS NOT NULL)
 Total runtime: 21530.247 ms

You can often force an index scan by doing:
  SET enable_seqscan=false;

So also try setting that, and run EXPLAIN ANALYZE again, and see if it
uses the indexes, and if so, if it's faster.
=> SET enable_seqscan=false;
SET
=> EXPLAIN ANALYZE SELECT COUNT(*) FROM "invites" INNER JOIN "sms_requests" ON "sms_requests"."invoker_id" = "invites"."id" AND "sms_requests"."invoker_type" = 'Invite' INNER JOIN "sms_messages" ON "sms_messages"."id" = "sms_requests"."sms_message_id" WHERE "sms_messages"."sent_at" IS NOT NULL;

                                                   QUERY PLAN                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=198896.67..198896.67 rows=1 width=0) (actual time=21923.378..21923.378 rows=1 loops=1)
   ->  Hash Join  (cost=90165.47..198222.73 rows=1347872 width=0) (actual time=5465.116..20552.881 rows=1340639 loops=1)
         Hash Cond: (sms_requests.invoker_id = invites.id)
         ->  Merge Join  (cost=7.29..101325.20 rows=1347872 width=4) (actual time=0.035..9630.580 rows=1347872 loops=1)
               Merge Cond: (sms_messages.id = sms_requests.sms_message_id)
               ->  Index Scan using sms_messages_pkey on sms_messages  (cost=0.00..69875.42 rows=1091467 width=4) (actual time=0.011..3009.455 rows=1107628 loops=1)
                     Filter: (sent_at IS NOT NULL)
               ->  Index Scan using index_sms_requests_on_sms_message_id on sms_requests  (cost=0.00..26190.25 rows=1347872 width=8) (actual time=0.014..2257.271 rows=1347872 loops=1)
                     Filter: ((invoker_type)::text = 'Invite'::text)
         ->  Hash  (cost=84264.43..84264.43 rows=1683927 width=4) (actual time=5462.709..5462.709 rows=1683927 loops=1)
               Buckets: 262144  Batches: 1  Memory Usage: 59201kB
               ->  Index Scan using invites_pkey on invites  (cost=0.00..84264.43 rows=1683927 width=4) (actual time=0.159..2763.626 rows=1683927 loops=1)
 Total runtime: 21923.504 ms



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

Предыдущее
От: Jeff Davis
Дата:
Сообщение: Re: indexes across joins not used for count
Следующее
От: Jeff Davis
Дата:
Сообщение: Re: indexes across joins not used for count