Re: timestamp with time zone

[Alessandro Gagliardi <alessandro@xxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

Alessandro Gagliardi <alessandro@xxxxxxxx> 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