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 <t...@sss.pgh.pa.us> wrote: > Alessandro Gagliardi <alessan...@path.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 >
