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
|
Список | 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 по дате отправления: