I tried: CREATE INDEX blocks_created_at_timezone_idx ON blocks USING btree ((created at time zone timezone));
(Actually, I originally did try one on "(created at time zone timezone)::date" but couldn't figure out how to phrase it in a way PostgreSQL would accept.) Anyway, no difference: http://explain.depesz.com/s/Zre I even tried changing the filter to (created at time zone timezone) > 'yesterday' AND (created at time zone timezone) < 'today' to see if that might make a difference. Sadly, no: http://explain.depesz.com/s/dfh Here's the definition for the offending table: CREATE TABLE blocks ( block_id character(24) NOT NULL, user_id character(24) NOT NULL, created timestamp with time zone, locale character varying, shared boolean, private boolean, moment_type character varying NOT NULL, user_agent character varying, inserted timestamp without time zone NOT NULL DEFAULT now(), networks character varying[], lnglat point, timezone character varying, CONSTRAINT blocks_pkey PRIMARY KEY (block_id ) ) WITH ( OIDS=FALSE ); CREATE INDEX blocks_created_at_timezone_idx ON blocks USING btree (timezone(timezone::text, created) ); CREATE INDEX blocks_created_idx ON blocks USING btree (created DESC NULLS LAST); CREATE INDEX blocks_lnglat_idx ON blocks USING gist (lnglat ); CREATE INDEX blocks_moment_type_idx ON blocks USING btree (moment_type ); CREATE INDEX blocks_networks_idx ON blocks USING btree (networks ); CREATE INDEX blocks_private_idx ON blocks USING btree (private ); CREATE INDEX blocks_shared_idx ON blocks USING btree (shared ); CREATE INDEX blocks_timezone_idx ON blocks USING btree (timezone ); On Thu, Feb 9, 2012 at 11:46 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Alessandro Gagliardi <alessan...@path.com> writes: > > WHERE ... (created at time zone timezone)::date = 'yesterday' > > > created has an index (btree if it matters). timezone does not. I'm > > wondering if the solution to my problem is to create a joint index > between > > created and timezone (and if so, if there is a particular way to do that > to > > make it work the way I want). > > The only way to make that indexable is to create an expression index on > the whole expression "(created at time zone timezone)::date". Seems > pretty special-purpose, though it might be worthwhile if you do that a > lot. > > regards, tom lane >