Re: Why is PostgreSQL not using my index?

Hi,

On 26.1.2015 17:32, Christian Roche wrote:
> select *
>
> from mixpanel_events_201409 mp
>
> inner join mixpanel_event_list ev on ( ev.id = mp.event_id )
>
> where ev.id in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318);
>
>
>
> Hash Join (cost=20.73..2892183.32 rows=487288 width=1000)
>
> Hash Cond: (mp.event_id = ev.id)
>
> -> Seq Scan on mixpanel_events_201409 mp (cost=0.00..2809276.70
> rows=20803470 width=949)
>
> -> Hash (cost=20.57..20.57 rows=13 width=51)
>
> -> Seq Scan on mixpanel_event_list ev (cost=0.00..20.57
> rows=13 width=51)
>
> Filter: (id = ANY
> ('{3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[]))
>
>
>
>
>
> Both tables have been vacuum analyzed.

Can we get EXPLAIN ANALYZE please, and maybe some timings for the two
plans? Otherwise we have no clue how accurate those estimates really
are, making it difficult to judge the plan choice.

You might also use enable_hashjoin=off to force a different join
algorithm (it may not switch to nested loop immediately, so maybe try
the other enable_* options).

The estimated row counts are quite near each other (410k vs. 487k), but
the costs are not. I'm pretty sure that's because while the fist query
has WHERE condition directly on the event_id column, the second one
moves the condition to the 'list' table, forcing this particular plan.

But as the condition is on the join column, you may try moving it back:

select *
from mixpanel_events_201409 mp
inner join mixpanel_event_list ev on ( ev.id = mp.event_id )
where mp.event_id in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318);

Of course, this only works on this particular column - it won't work for
other columns in the 'list' table.

regards

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services