Re: Speeding up index scans by truncating timestamp?

On Tue, Feb 15, 2011 at 10:16 AM, Michael Glaesemann
<grzm(at)seespotcode(dot)net>wrote:

> Providing the table definition, queries, and EXPLAIN and EXPLAIN ANALYZE
> output would help people provide more specific feedback.

Seemed a general enough question that it wasn't necessary. That, and I
wanted to figure out as much of it on my own as I could, rather than just
get the end-result of an expert's answer without all the knowledge of the
leg work.

Thank you, though.

On Tue, Feb 15, 2011 at 10:20 AM, Vick Khera <vivek(at)khera(dot)org> wrote:

> On Tue, Feb 15, 2011 at 10:00 AM, Derrick Rice <derrick(dot)rice(at)gmail(dot)com>
> wrote:
> > Is the query optimizer capable of using the relationship between an index
> on
> > date_trunc(foo) and a query with "where foo < bar and foo > baz" ? At
> this
> > point the question is to satisfy my own curiosity.
>
> No. The query has to use the same function as the index does.
>

Well that settles it, then. Thanks, Vick.

>
> I also don't think that the storage space will be any less. A
> timestamp is always stored in the same amount of space. All you're
> doing is zeroing out the higher resolution bits of time.
>
>
It's been a while since I was intimate with the implementation of a btree.
I was guessing that it might make the tree more "dense" if there were more
values that were equal. A "dense" tree would be easier to scan when
grabbing all of the children of a particular node (which is the case when
doing a wide range comparison).

The little bit of review that I was able to do reminded me that equal or
unequal values don't make a tree more or less "dense". It could arguably
make inserts easier (because there's more acceptable places to put an item)
but each node will have n to m items regardless of their relationship to
each other.

On that topic... are the details of PostgreSQL's b-tree implementation found
anywhere outside of the code? i.e. what n,m-tree values it uses? Searched
docs and wiki with no luck.

Having fun relearning this stuff

Derrick