Re: Why is PostgreSQL not using my index?

On Mon, Jan 26, 2015 at 10:32 AM, Christian Roche
<Christian(dot)Roche(at)workshare(dot)com> wrote:
> Bitmap Heap Scan on mixpanel_events_201409 (cost=7663.36..1102862.70
> rows=410022 width=949)
>
> Recheck Cond: (event_id = ANY
> ('{3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[]))
>
> -> Bitmap Index Scan on mixpanel_idx_event_201409 (cost=0.00..7560.85
> rows=410022 width=0)
>
> Index Cond: (event_id = ANY
> ('{3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[]))
>
>
> But when I try to join the lookup table and select from it, the index is
> dismissed for a full table scan with a catastrophic effect on performance:

Better to post 'explain analyze' times than 'explain', so we can get a
better understanding of what 'catastrophic' means. Other frequently
overlooked planner influencing settings are effective_cache_size,
which estimates amount memory available for caching and work_mem.
effective_cache_size in particular is often dreadfully underset making
the server thing it's going to have to do expensive random i/o to
facilitate nestloops and will therefore tend to avoid them.

merlin