Re: indexes across joins not used for count

On Sun, 2012-10-21 at 08:37 +1300, Jeremy Wells wrote:
> 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;
>
Interesting... it looks like the two plans take about the same amount of
time, so the planner is not making a mistake.

I tried making three tables about the same size as yours, and then doing
a three-way join, and on my machine it took closer to a second. So
there's clearly something else going on with your data. Maybe the data
distribution is skewed (some values having many matches in another
table, others having none)?

Regards,
    Jeff Davis