From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Why is PostgreSQL not using my index?
Date: Tue, 27 Jan 2015 05:25:02 +0100
Message-ID: <54C7131E.6060006@2ndquadrant.com> (raw)
In-Reply-To: <dd25f397d7424a50b4c548a435d6847d@LN1-EX2013-01.workshare.com>
References: <dd25f397d7424a50b4c548a435d6847d@LN1-EX2013-01.workshare.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com
Subject: Re: Why is PostgreSQL not using my index?
In-Reply-To: <54C7131E.6060006@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox