Re: indexes across joins not used for count

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