indexes across joins not used for count

Поиск
Список
Период
Сортировка
От Jeremy Wells
Тема indexes across joins not used for count
Дата
Msg-id 507CCFA4.6060000@gmail.com
обсуждение исходный текст
Ответы Re: indexes across joins not used for count  (Jeff Davis <pgsql@j-davis.com>)
Список pgsql-novice
I'm running a query to do a count with two joins in it. I've added
indexes to the tables for the join columns, but the explain of the query
doesn't seem to be using the indexes:

Table 1:
   invites (id:int)

Table 2:
   sms_requests (id:int, invoker_id:int, invoker_type:string,
sms_message_id:int)
   Indexes:
     "sms_requests_pkey" PRIMARY KEY, btree (id)
     "index_sms_requests_on_invoker_id_and_invoker_type" btree
(invoker_id, invoker_type)
     "index_sms_requests_on_sms_message_id" btree (sms_message_id)

Table 3:
   sms_messages (id:int, sent_at:timestamp)
   Indexes:
     "sms_messages_pkey" PRIMARY KEY, btree (id)
     "index_sms_messages_on_sent_at_partial" btree (sent_at) WHERE
sent_at IS NULL
     "index_sms_messages_on_sent_at" btree (sent_at)

Query:

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

Explain:

  Aggregate  (cost=165914.42..165914.43 rows=1 width=0)
    ->  Hash Join  (cost=92326.82..163534.87 rows=951821 width=0)
          Hash Cond: (sms_requests.sms_message_id = sms_messages.id)
          ->  Hash Join  (cost=32692.53..83674.38 rows=951821 width=4)
                Hash Cond: (invites.id = sms_requests.invoker_id)
                ->  Seq Scan on invites  (cost=0.00..27525.48
rows=1238948 width=4)
                ->  Hash  (cost=20794.76..20794.76 rows=951821 width=8)
                      ->  Seq Scan on sms_requests  (cost=0.00..20794.76
rows=951821 width=8)
                            Filter: ((invoker_type)::text = 'Invite'::text)
          ->  Hash  (cost=48180.24..48180.24 rows=916324 width=4)
                ->  Seq Scan on sms_messages  (cost=0.00..48180.24
rows=916324 width=4)
                      Filter: (sent_at IS NOT NULL)

This is pretty slow, ~5000ms on my development machine. I would have
expected it to be able to make use of the indexes I've created. Any
ideas on what I can do to make this perform better?

Jeremy



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

Предыдущее
От: George Roberge
Дата:
Сообщение: Re: Beginner's question about ODBC and/or foreign data sources
Следующее
От: Adam Mackler
Дата:
Сообщение: Re: Equivalence of CROSS JOIN and comma