Re: [PERFORM] timestamp with time zone
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
Re: [PERFORM] timestamp with time zone
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] timestamp with time zone
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
Re: [PERFORM] timestamp with time zone
Alessandro Gagliardi alessan...@path.com wrote: (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.) CREATE INDEX blocks_created_date_idx ON blocks USING btree (((created at time zone timezone)::date)); -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] timestamp with time zone
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? On Thu, Feb 9, 2012 at 12:15 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alessandro Gagliardi alessan...@path.com wrote: (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.) CREATE INDEX blocks_created_date_idx ON blocks USING btree (((created at time zone timezone)::date)); -Kevin
Re: [PERFORM] timestamp with time zone
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
