Обсуждение: Why is PostgreSQL not using my index?

This email is from Workshare Limited. The information contained in and accompanying this communication may be confidential, subject to legal privilege, or otherwise protected from disclosure, and is intended solely for the use of the intended recipient(s). If you are not the intended recipient of this communication, please delete and destroy all copies in your possession and note that any review or dissemination of, or the taking of any action in reliance on, this communication is expressly prohibited. Please contact the sender if you believe you have received this email in error. Workshare Limited is a limited liability company registered in England and Wales (registered number 3559880), its registered office is at 20 Fashion Street, London, E1 6PX for further information, please refer to http://www.workshare.com.

"Christian Roche" <Christian.Roche@workshare.com> writes:
> Now when I select a subset of the possible event IDs in the big table, PG uses the appropriate index:

> select *
>   from mixpanel_events_201409
>  where event_id in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318);

> 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
catastrophiceffect on performance: 

> 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[]))

Given the estimated costs and rowcounts here, I'm far from convinced that
the planner made the wrong decision.  You seem to be expecting that it
will go for a nestloop plan that would require 13 separate indexscans of
the large table.  Those are unlikely to be only 1/13th the cost of the
unified bitmap scan with =ANY; there's going to be overhead from repeated
work.  If there's say a factor of 2 penalty for the repeated scans, that'd
be plenty enough to push the cost of that plan to be more than the
hashjoin.

If, indeed, the hashjoin is slower, that may suggest that you need to dial
down random_page_cost to better represent your environment.  But you
should be wary of making such an adjustment on the basis of a single
example; you might find that it makes other plan choices worse.

            regards, tom lane

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

On Mon, Jan 26, 2015 at 10:32 AM, Christian Roche
<Christian.Roche@workshare.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