Re: timestamp with time zone

Hm. Tried running ANALYZE. Took almost 10 minutes to run. (Don't know if it
would have been run automatically since I last tried this yesterday, but
figured it couldn't hurt.) Still, no difference:
http://explain.depesz.com/s/xHq
Actually, it's 10x worse (maybe because this is my first time running this
query today, whereas last time I had run it, or a version of it, several
times before running that EXPLAIN). Anyway, good tip on dropping the index,
but I don't think that would be a good idea in this case because the index
it appears to be choosing is the primary key!

On Thu, Feb 9, 2012 at 10:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alessandro Gagliardi <alessandro(at)path(dot)com> writes:
> > Still slow as mud: http://explain.depesz.com/s/Zfn
> > Now I've got indices on created, timezone, created at time zone timezone,
> > and (created at time zone timezone)::date. Clearly the problem isn't a
> lack
> > of indices!...except, wait, it's not actually using
> blocks_created_date_idx
> > (or blocks_created_at_timezone_idx). How do I make that happen?
>
> Did you ANALYZE the table after creating those indexes? Generally you
> need an ANALYZE so that the planner will have some stats about an
> expression index.
>
> It might still think that the other index is a better option. In that
> case you can experiment to see if it's right or not; the general idea
> is
>
> begin;
> drop index index_that_planner_prefers;
> explain analyze your_query;
> rollback; -- revert the index drop
>
> If that EXPLAIN isn't actually any better than what you had, then the
> planner was right. If it is better, let's see 'em both.
>
> regards, tom lane
>