indexes across joins not used for count

From: Jeremy Wells <jemmyw(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: indexes across joins not used for count
Date: 2012-10-16 03:08:20
Message-ID: 507CCFA4.6060000@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Adam Mackler 2012-10-16 04:46:04 Re: Equivalence of CROSS JOIN and comma
Previous Message George Roberge 2012-10-15 22:11:59 Re: Beginner's question about ODBC and/or foreign data sources