Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr

On Tue, Aug 5, 2014 at 2:08 PM, john gale <john(at)smadness(dot)com> wrote:

>
> >> -> Bitmap Index Scan on
> >> index_testruns_on_custom_spawnid (cost=0.00..41437.84 rows=500170
> >> width=0) (actual time=4872.404..4872.404 rows=2438520 loops=1)
> >
> > Ouch, ouch, and more ouch. Your index_testruns_on_custom_spawnid index
> on matched 2.5 million rows...
>
>
> Yes, although it's still better than the 40mil rows that we have in the
> table itself...
>
> Also, that doesn't make sense to me, since we don't have 2.5mil rows that
> match this one SpawnID. Could this suggest that my partial hstore index is
> somehow misconstructed? Or is that saying that 2.5mil rows have a SpawnID,
> not all of which will be the one I'm looking for?
>

Have you tripled checked that for 'SpawnID-428870395.258592' ?

That seems like something a human is much more likely to get wrong than a
computer is.

Anyway, it seems like an compound index on ((custom_data ->
'SpawnID'::text),started_at) could do wonders for this query.

Cheers,

Jeff