Shaun Thomas-2 wrote
>> 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?
>
> I'm not sure. But according to that explain analyze, it matched 2.5
> million... somethings. Perhaps it has to do with how hstore is actually
> indexed. Maybe it starts at SpawnID, then narrows it down with
> SpawnID-428870395.258592, but can't do so until it fetches the SpawnID
> part. I'll stop commenting on hstore anything, because I never use it.
The presence of hstore in a red herring here. The index itself is storing
the result of "hstore => 'key'" which has a data type of "text". Note that
the use of a "btree" index reinforces this since (IIRC) a direct hstore
index would require gin/gist.
*Recommendation:* Create a multi-column index instead of (or in addition to)
the two single column ones.
Theorizing out loud here...take with a grain of salt - and experiment.
What is happening is that the entire (id) index is being bitmapped (hashed,
basically) so that it can be combined with the other (date) index (also
being bitmapped). Since this process operates at the page level the bitmap
of an ID that is frequently used throughout time is going to have a very low
hit ratio (matched tuples / pages) and also result in a high recheck loss
rate. The ID seems likely to have this problem - the Date index likely not
so much.
NOTE: I am confused by this line:
-> BitmapAnd (cost=291564.31..291564.31 rows=28273 width=0) (actual
time=23843.870..23843.870 rows=0 loops=1)
How did actual match zero rows? It should be something like 2.2M
Anyway, you should probably experiment with creating a multi-column index
instead of allowing PostgreSQL to BitmapAnd them together. Likely the
timestamp will have higher cardinality and so should be listed first in the
index. This is the only way it can combine two indexes and it thinks this
is better than using the "Date" index and then scanning every single page
for matching IDs.
Experimentation (or more knowledgeable insight) into this particular
decision is worthwhile...
I would probably still increase statistic targets but I do not think that
will help as much as a compound index.
Note that using a CTE (which is kinda like an explicit BitmapAnd) is not
likely to be any better than an implicit BitmapAnd - but it doesn't hurt to
check.
I'm not sure how a partial index would help here...though maybe
partitioning...especially since a partial index on date doesn't typically
make sense since time is constantly changing.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/understanding-why-two-nearly-identical-queries-take-two-different-planner-routes-one-5s-and-one-2hr-tp5813819p5813840.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
