Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 1E4A36CFD91 for ; Fri, 10 Feb 2012 02:20:11 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rvjq3-0006i8-8B for pgsql-performance@postgresql.org; Fri, 10 Feb 2012 06:20:10 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q1A6JqcY007689; Fri, 10 Feb 2012 01:19:52 -0500 (EST) To: Alessandro Gagliardi cc: Kevin Grittner , pgsql-performance@postgresql.org Subject: Re: timestamp with time zone In-reply-to: References: <27573.1328816766@sss.pgh.pa.us> <4F33D4FE0200002500045139@gw.wicourts.gov> Comments: In-reply-to Alessandro Gagliardi message dated "Thu, 09 Feb 2012 12:38:22 -0800" Date: Fri, 10 Feb 2012 01:19:52 -0500 Message-ID: <7688.1328854792@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/83 X-Sequence-Number: 46255 Alessandro Gagliardi 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