On Tue, Aug 5, 2014 at 2:08 PM, john gale <john@smadness.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.