From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alessandro Gagliardi <alessandro@path.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: timestamp with time zone
Date: Fri, 10 Feb 2012 01:19:52 -0500
Message-ID: <7688.1328854792@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAB3BBJyVqQQKqDZSz3ankPuT0PcsNxfC6U_GaPCCLDVWBRpzA@mail.gmail.com>
References: <CAAB3BBJnD+dcaOQ_T8g+y==zen9yuOj2NvXrE5vhVe=RP28hMw@mail.gmail.com>
<27573.1328816766@sss.pgh.pa.us>
<CAAB3BB+R7O7NCDZJJp7sABWSDtbYsSf4H7ugxGn8EDcJ9H_VqA@mail.gmail.com>
<4F33D4FE0200002500045139@gw.wicourts.gov>
<CAAB3BBJyVqQQKqDZSz3ankPuT0PcsNxfC6U_GaPCCLDVWBRpzA@mail.gmail.com>
Alessandro Gagliardi <alessandro@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, alessandro@path.com, Kevin.Grittner@wicourts.gov
Subject: Re: timestamp with time zone
In-Reply-To: <7688.1328854792@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox